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 :

    Optimizing Stored Procedures To Avoid Recompiles

  • 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://www.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