OPTION (KEEPFIXED PLAN) issue
-
Hello All,
Whenever I try to execute my stored procedure, for the First Time, it is getting recompiled in two place..as mentioned below :
1) INSERT INTO #UsrFavs (TransId)
EXEC @SPRETVAL=ED.ED.dbo.prcCtlSelUsFav @userid,1
2)
SELECT
CF.FileId
,CF.Transid
From #UsFavs UF WITH (NOLOCK)
, Cus_file CF WITH (NOLOCK)
Where
UF.Transid = CF.Transid
When I tried utilising the OPTION(KEEPFIXED PLAN) in the second query, I could able to avoid recompilation , but the stored execution time is getting increased considerbly.
Why is this happening, ??
It would be great, if I get any help regarding this issue.
Thanks in advance- Prabu
-
Can we see the code of the proc (full code).
-
Please find the Below Entire SP:
CREATE PROCEDURE dbo.prcCtlLnkSelFavCust( @userid int, @UserPartId int , @UserType int) AS
DECLARE @ErrMsg VARCHAR(500)
,@SPRETVAL int
,@RetVal int
,@ErrSpName varchar(100)
SELECT @ErrSpName = 'Stored procedure:' + OBJECT_NAME(@@PROCID)
Create table #UsFavs
(Transid int NOT NULL)
CREATE CLUSTERED INDEX [IDX_2] ON [dbo].[#UsFavs] ([TransId])
insert into #UsFavs (TransId)
EXEC @SPRETVAL=ED.ED.dbo.prcCtlSelUsFav @userid,1
IF(@@ERROR)<>0
BEGIN
SELECT @ErrMsg='ERROR OCCURED IN SELECT STATEMENT of ' + @ErrSpName
SELECT @RetVal =100
GOTO ERROR
END
if(@SPRETVAL<>0)
BEGIN
SELECT @ErrMsg='ERROR OCCURED IN SELECT STATEMENT of Stored Procedure:prcCtlSelUserFavorites'
SELECT @RetVal =105
GOTO ERROR
END
SELECT
CF.FileId
,CF.Transid
From #UsFavs UF WITH (NOLOCK INDEX=IDX_2)
, Cus_file CF WITH (NOLOCK)
Where
UF.Transid = CF.Transid
ORDER BY P.LastNm,P.FirstNm
IF(@@ERROR)<>0
BEGIN
SELECT @ErrMsg='ERROR OCCURED IN SELECT STATEMENT of ' + @ErrSpName
SELECT @RetVal =110
GOTO ERROR
END
drop table #UsFavs
IF(@@ERROR)<>0
BEGIN
SELECT @ErrMsg='ERROR OCCURED IN dropping temporary table of ' + @ErrSpName
SELECT @RetVal =115
GOTO ERROR
END
Return (0)
ERROR:
Raiserror 77777 @ErrMsg
Return (@retval)
GO
-
I see, check out this article for all the info you need :
-
Yes, as mentioned in that article, I could able to avoid recompile, by utilising the OPTION (keepfixed plan), but my execution time is getting increased, when compared to before implementing this OPTION.
Kindly could you please tell, why is this happening so ?? or is there any specific scenario I should verify, before implementing this OPTION.
Thanks a lot for prompt reply regarding this issue.
-
I don't know why the time is increased. I also know that you can't avoid using temp table in this case because you are populating a table from a proc. Any way you can change the sp to a table function so that you can drop the temp table completely?
-
SELECT
CF.FileId
,CF.Transid
From #UsFavs UF WITH (NOLOCK INDEX=IDX_2)
, Cus_file CF WITH (NOLOCK)
Where
UF.Transid = CF.Transid
ORDER BY P.LastNm,P.FirstNm
I'm not familiar with NOLOCK clauses, but
Why not using an inner join instead of where x=y
Where does P.LastNm come from?
SET NOCOUNT ON?
-
I'd agree with jo on the inner join; looking at your clustered index creation i take it that the temp table is going to contain quite a few rows?
If it's not the you could possibly get away with using a table variable instead but that means you wouldn't be able to index it (you could give it a primary key though if the data was unique)
As for why it's slower, how much data do you have in the Cus_file table (rows), how fragmented is the index and when was the last time you updated the statistics on it?
When you execute the SP with the execution plan does it show any missing stats, bookmark lookups or hash matches? as that would indicate you have something missing.
What the hell, why not include parameter sniffing as a potential problem too, might as well throw it in for consideration
-
I guess it's time to ask for the execution plan
: SET SHOWPLAN_TEXT ON
GO
Select 'query here'
GO
SET SHOWPLAN_TEXT OFF
-
You mean I forgot that bit
.Nice script in the newbies section by the way Remi
-
Well when I ask some questions about the execution plan, it's often faster to just ask for the plan than to educate the guy on how to read it... and it gives me a chance to see the whole thing for myself
.What script are you talking about Mike??
-
help with the select query, http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=219329
i can see that being useful in the future
-
Oh that, apparently it's too hard to understand for newbies. Looks like I'll have to comment everything in details before reusing that again
.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply