Is the Stored procedure is pre-compiled ?

Posted by Kanakaraju_Msc under Sql Server on 7/16/2014 | Points: 10 | Views : 6011 | Status : [Member] | Replies : 4
Hi Friends

I faced an interview questions is the stored procedure is pre-compiled ?

If yes how can we say that is pre compiled.

Thanks,
Raju




Responses

Posted by: Vuyiswamb on: 7/16/2014 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Yes a StoredProcedure is Pre-Compiled. When you run a normal Query in your SQL management Studio e.g

select * from My table


SQL needs to find a best possible way of retrieving your data and it needs to also check if you have indexes that can help bring back the data. This is called Execution plan. Now in a StoredProcedure these steps are not performed because they are determined , the stored procedure stores the execution plan and it just use the plan to run the stored procedure.


Thank you for posting at Dotnetfunda
[Administrator]

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

Posted by: Bandi on: 7/17/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Yes, Stored Procedures are Pre-Complied after its first run. The practical example to prove this is:

Stored procedure are not pre-compiled, but compiled only during the first run. For every subsequent runs, it is for sure pre-compiled.

When we create a Stored procedure, first time and we will find that there are not casing entry for the executions of the Stored procedure.

After running the stored procedure for the first time, the entry for the cache is made in the system.

That's why Stored Procedure takes long time to run for first time.

Here are some SQL statements to see the casing options of stored procedure.

Run below code once......

-- First Clean Cache

DBCC FREEPROCCACHE
GO
IF EXISTS (SELECT * FROM sys.sysobjects WHERE TYPE = 'P' AND NAME = 'up_SampleSP')
BEGIN
DROP PROCEDURE up_SampleSP
END
GO
-- Create the Stored Procedure
CREATE PROCEDURE up_SampleSP
AS
SELECT *
FROM TableName -- Replace the table name which you have in your database
GO
-- Check the Query Plan for SQL Batch
-- [ Result -- You will find that there is no ObjectName with the name of up_SampleSP ]
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;
GO
-- Execute Stored Procedure
EXEC up_SampleSP
GO
-- Check the Query Plan for SQL Batch
-- [ Result -- You will find that there is one entry with name ObjectName with name up_SampleSP ]
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st ;
GO



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Posted by: Ajay.Kalol on: 7/17/2014 [Member] Starter | Points: 25

Up
0
Down
SP is pre-compiled
if you want to recompile that SP then You can Use

WITH RECOMPILE

Keyword while creating SP

Ajay
ajaypatelfromsanthal.blogspot.in

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

Posted by: nayeemdotnetfunda-27597 on: 7/24/2014 [Member] Starter | Points: 25

Up
0
Down
No Stored Procedures are pre compiled it is compiled only during its first run Assume we are creating Stored procedure, first time and we will find that there is no entry for the executions of the Stored procedure.

After running the stored procedure for the first time, the entry for the cache is made in the system.



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

Login to post response