Kindly explain, Somebody says user defined function is not "Pre-Compiled". Is that true ?

Posted by Jasminej under Sql Server on 4/18/2012 | Points: 10 | Views : 2852 | Status : [Member] | Replies : 7
Hi all,

I heard that, user defined function is not "Pre-Compiled" like stored procedure ? But, somebody says, User defined function and procedures both are pre-compiled and both are reuse the Plan.

csan anyone explain with an example ?

thank you




Responses

Posted by: Sakthi.Singaravel on: 4/18/2012 [Member] Silver | Points: 25

Up
0
Down
Refer this link..

http://www.sql-server-performance.com/2006/stored-procedure-function/

Regards,
Singaravel M

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/18/2012 [Member] Silver | Points: 25

Up
0
Down
See this MSDN Forum..

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/e472a918-7828-4817-88a4-d4b585042a72

Regards,
Singaravel M

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jasminej on: 4/18/2012 [Member] Starter | Points: 25

Up
0
Down
Thanks Sakthi.Singaravel

I went through the link you provided!. But, still I didn't get clear conclution yet. Because, Somebody says UDF is Pre-Compiled and Its stored the Plan for reuse.. somebody says It'll not!

But, where is the Proof?.. How can we conclude?

I also have posted the same question in someother Forums.. But, No luck!

Could you please provide someone with an example ?

Thank you


Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gsm_Gsv on: 4/19/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Jasminej

Stored procedures are pre compiled, and UDFs are executed at runtime.

Refer this link:http://www.techipost.com/2011/03/16/a-tutorial-on-stored-procedures-and-user-defined-functions/

-----------
Regards
MADHU


---------------------------------------
Live the life you've dreamed

Regards
MADHU

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 4/23/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

1. Normally, when we run/refer the Modular Object(Procedure, Function,....Etc.,), Plan will be generated for that Modular Object and cached in sql server.
2. That Plan will be re-used whenever we call the objects again and again instead of Parsing and Compiling.
3. In this case, Procedure and User Defined Function also behaves same...
4. We can see/identity the Plan which been generated for an Object using "sys.dm_exec_cached_plans" DMV

A. Creating a Function:
Use SQLServer

Go
alter Function Function1() Returns Varchar(25)
as
Begin
Return('SQL Server ''Denaly''')
End
Go
B. Checking whether Plan is created for the function:
Use SQLServer

Go
Select P.objtype [Object Type], Object_Name(x.objectid) [Object Name],x.[text] [Script], P.usecounts [Reuses], P.plan_handle [Plan Handle] from sys.dm_exec_cached_plans P Cross Apply Sys.dm_exec_sql_text(P.Plan_Handle) X
Where P.objtype = 'Proc'
And x.objectid = OBJECT_ID('Function1')
Go
- No Plan cached yet.. Because, Plan will be generated and cached in SQL Server @ Every first time of the execution/call (or) some Recompile factors (or) when the Plan cleared from the cach..

C. Execution/Calling the Function:
Use SQLServer

Go
Select dbo.Function1()
D. Checking whether Plan is created for the function:
Use SQLServer

Go
Select P.objtype [Object Type], Object_Name(x.objectid) [Object Name],x.[text] [Script], P.usecounts [Reuses], P.plan_handle [Plan Handle] from sys.dm_exec_cached_plans P Cross Apply Sys.dm_exec_sql_text(P.Plan_Handle) X
Where P.objtype = 'Proc'
And x.objectid = OBJECT_ID('Function1')
Go
- Now, we have cached plan for the Function. and "Reuse" column has the value as "1"


E. Execution/Calling the Function again:
Use SQLServer

Go
Select dbo.Function1()
F. Checking whether Plan is created for the function:
Use SQLServer

Go
Select P.objtype [Object Type], Object_Name(x.objectid) [Object Name],x.[text] [Script], P.usecounts [Reuses], P.plan_handle [Plan Handle] from sys.dm_exec_cached_plans P Cross Apply Sys.dm_exec_sql_text(P.Plan_Handle) X
Where P.objtype = 'Proc'
And x.objectid = OBJECT_ID('Function1')
Go
- Now, we have cached plan "Reuse" column has the value as "2"

G. Execution/Calling the Function again:
Use SQLServer

Go
Select dbo.Function1()
Go
Select dbo.Function1()
H. Checking whether Plan is created for the function:
Use SQLServer

Go
Select P.objtype [Object Type], Object_Name(x.objectid) [Object Name],x.[text] [Script], P.usecounts [Reuses], P.plan_handle [Plan Handle] from sys.dm_exec_cached_plans P Cross Apply Sys.dm_exec_sql_text(P.Plan_Handle) X
Where P.objtype = 'Proc'
And x.objectid = OBJECT_ID('Function1')
Go
- Now, we have cached plan "Reuse" column has the value as "4"
- The "Reuse" column says the Function is not been Re-Compiled and the Plan created again and again whenever we call the function... Instead, It Re-uses the cached plan...

I. Forcely, Removing/Clearing the Plan cache of this Function:
DBCC FreeProcCache(0x050009004A0CC672B820B30B000000000000000000000000)

Go
- The Plan handle value for this Function taken from the script (H.)
- Now, Tha Cached plan was removed from the SQL Server cache. So, the Plan will be newly created for this function again when we call/run the function.

J. Execution/Calling the Function:
Use SQLServer

Go
Select dbo.Function1()
Go
K. Checking whether Plan is created for the function:
Use SQLServer

Go
Select P.objtype [Object Type], Object_Name(x.objectid) [Object Name],x.[text] [Script], P.usecounts [Reuses], P.plan_handle [Plan Handle] from sys.dm_exec_cached_plans P Cross Apply Sys.dm_exec_sql_text(P.Plan_Handle) X
Where P.objtype = 'Proc'
And x.objectid = OBJECT_ID('Function1')
Go
- Now, the "Reuses" value is "1". So, the Plan has been re-generated and used only once...

So, the Procedure and the Function are behaving as same for Caching the Plan and Re-using it!

Note: "User Defined Function" also treated as "Proc" in SQL Server Plan. Please refer the column "Object Type" in our tracking script!

I hope it would give you better understanding!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jasminej on: 4/23/2012 [Member] Starter | Points: 25

Up
0
Down
hi Pandians

Thanks for given me a detailed explanation :)

Thank You

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/23/2012 [Member] Silver | Points: 25

Up
0
Down
@Pandian

Thank u for detailed answer..

Regards,
Singaravel M

Jasminej, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response