SP Recompile Issue.
-
Hi All,
Iam trying to insert some values into the Temp table by calling a stored procedure (sp_A) which inturns calls, another Stored procedure (sp_B) inside it.
syntax is as below :
create procedure sp_Parent
INSERT #Temp (column1, Coulmn2) Exec (sp_A (col1,col2)
End
note: sp_A interns call sp_B.
While executing this procedure sp_Parent is getting recompiled when executing the INSERT #Temp.. Query.
Usage of table variable is not possible here, since we are calling an sp_A which returns a result set.
I also tried with OPTION(KEEPFIXED PLAN) in the SELECT stament of this Temp table, but still it is geting recompiled.
Kindly provide me some solution to stop the sp_Parent from getting Recompiled.
Thanks in Advance.,
Regards,
Prabu.P
-
Where are you creating the #temp table?
if it's inside sp_a, you can't go around a recompile.
Check out this article :
-
Hi , Thanks for your Reply, but Iam creating the #temp table in the sp_Parent procedure, which is calling the sp_A stored Procedure.
The exact procedure is like below :
CREATE PROCEDURE sp_Parent
AS
BEGIN
CREATE TABLE #Temp (Column1,Column2)
INSERT #Temp (column1, Coulmn2) Exec (sp_A (col1,col2)
END
When I execute the sp_Parent, the INSERT #Temp statement is making the Strored procedure sp_Parent to get recompiled.
Kindly provide me the solution how to avoid in this scenario.
Once again Thanks for your response.
Thanks and Regards,
Prabu.P
-
Did you read this???
Optimizing Stored Procedures To Avoid Recompiles
if you use a temp table, there's gonna be a recompile, there's just no way around that.
-
Hi, Thanks for your Prompt reply. I have already gone thru this article at microsoft site, But I have some SP's having #temp tables INSERT queries like below :
INSERT #Temp (column1, Column2)
SELECT coulmn1, Column2 FROM <Table> Where <Condn>
This query is not causing recompilation.
But in the Previous sceanrio, Iam getting the values by EXEC an stored procedure. Only that query causes recompiling.
It would be Great, If you could provide me some more information, why the SP will be recompiled when using such kind of queries, as you said.
Thanks a lot for your time.
Regards,
Prabu.P
-
You can't seriously tell me you read the article :
Recompiles due to interleaving DDL and DML operations
Stored procedures will recompile is that the developer has place interleaving Data Definition Language operations with Data
Manipulation Language operations. This is usually caused when temporary objects are created and referenced throughout the
code. The reason this happens is that the temporary objects due not exist when the initial compilation of the code takes
place so SQL Server will have to recompile the stored procedure during execution. This recompilation will take place after
the temporary object is referenced for the first time. By placing all of your temporary table creation statements together,
SQL Server can create plans for those temporary tables when one of them is referenced for the first time. This recompile
will still take place during the execution of the stored procedure, but you have cut down of the recompiles from n to two
(one for the stored procedure and one when the first reference to a temporary table is made). SQL Server will also be able
to reuse the execution plan for the stored procedure the next time the procedure is called and your recompiles will go to
zero. Remember that like permanent objects, if you change the schema of a temporary table, that change will cause the
stored procedure to recompile as well. Make all schema changes (such as index creation) right after your create table
statements and before you reference any of the temporary tables. If you take the stored procedure created during the
section on using Profiler and modify it as written below you will stop the unnecessary recompiles.
Modifications to stop the recompile (7.0 and 2000)
USE pubs
GO
IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO
CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON
DECLARE @lngCounter INTEGER
SET @lngCounter = 1
--create temp table
CREATE TABLE #tTemp
(a INTEGER, b INTEGER)
--Create index on temp table
CREATE CLUSTERED INDEX ind_temp ON #tTemp(a)
SELECT count(*) FROM #tTemp
--add large amount of rows to table
WHILE @lngCounter < 2000
BEGIN
INSERT INTO #tTemp(a) VALUES(@lngCounter)
SET @lngCounter = @lngCounter + 1
END
SELECT count(*) FROM #tTemp
GO
EXEC dbo.spShowRecompile
Recompiles due to operations against temporary objects
SQL Server will recompile a stored procedure every time it is ran if any of the following conditions apply in that stored
procedure: If statements that contain the name of a temporary table refer to a table created by a calling or called stored
procedure or in a string execute by using sp_executesql or the EXECUTE statement. If any statement that contains the
name of the temporary table appear syntactically before the temporary table is created in the stored procedure or trigger.
If there are any DECLARE CURSOR statements whose SELECT statement references a temporary table. If any statements
that contain the name of a temporary table appear syntactically after a DROP TABLE against the temporary table (you
might read that DROP TABLES for temporary tables are not needed since they are dropped at the conclusion of the stored
procedure execute, but it is a good idea to drop temporary tables as you are done with them to free up system resources).
Or if any statement that creates a temporary table appear in a control-of-flow statement. By avoiding these conditions
when you create your code you can avoid needless stored procedure recompiles.
-
Hi, Thanks for making me , Exploring this article crystal clear..
Now I got the Reason for my SP getting recompiled, But one more interesting thing is, one of my collegue, utilised UDF instead of calling an SP, on doing so he could able to avoid recompilation. But in my case I coudn't do so, since the SP which Iam calling , intern calls another SP.
Anyway if no other go, we can settle down.
Iam really excited with your help and support. Hope I can get your help in the future also.
Thanks again.
Prabu.P
-
Sure thing.. just make sure you actually do what I ask and you'll be fine
.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply