none
with recompile in store procedure RRS feed

  • Question

  • I have following store procedure running very slow in both SSMS and called by application. If we recompile this store procedure, we can see the performance gain and be slow after couple days. We are planning to add with recompile option in procedure create statement. However, we don't know whether that will solve this problem or not. This store procedure can be call couple thousand times per day. 

    Table2 data is from replication scream and has about 400 - 600 million records. It will update/insert several million records per day. 

    Table1 data is not update too often and have about 15 - 20 million records. It will insert 200 - 500K records per day. 

    Could anyone explain when we should /[should not] add [With RECOMPILE] option in store procedure creation? 

     

     
    CREATE PROCEDURE [dbo].[SP1] 
     @parm1 uniqueidentifier, 
     @parm2 varchar(2), 
     @parm3 varchar(12), 
     @parm4 varchar(35), 
     @parm5 bit, 
     @StatusList xml, 
     @sortBy varchar(20), 
     @param11 int = NULL, 
     @parm7 datetime = NULL, 
     @parm8 datetime = NULL, 
     @Page int, 
     @PageSize int, 
     @parm9 char(6) = NULL, 
     @parm10 char(1) = NULL, 
     @parm11 decimal(19,10) = NULL, 
     @parm12 smalldatetime = NULL, 
     @TotalCount int OUTPUT 
     
    AS 
     
    SET ANSI_NULLS ON 
    SET NOCOUNT ON 
    BEGIN 
     
     
     DECLARE @rowStart integer 
     DECLARE @rowEnd integer 
     
     SET @rowStart = (@page * @pageSize) + 1; 
     SET @rowEnd = ((@page + 1) * @pageSize); 
     
     
     DECLARE @StatusTable as TABLE(StatusValue varchar(128)) 
     
     INSERT INTO @StatusTable (StatusValue) SELECT ParamValues.StatusValue.value('.','varchar(128)') 
     FROM @StatusList.nodes('/StatusList/StatusValue') as ParamValues(StatusValue) 
     
     
     DECLARE @result AS TABLE 
     ( 
     .... -- Columns
     ) 
     
     
     INSERT INTO @result 
     
     SELECT 
     .... -- Columns 
     FROM [dbo].[Table1] T with(nolock) 
      inner join @StatusTable ST on ST.StatusValue = T.[Status]   
     WHERE Column1 = @parm1 
      AND T.column2 = @parm2
      AND T.column3 = @parm3
      AND T.column7 >= @parm7 
      AND T.column8 <= @parm8
      AND ((@parm5 = 1 and [column5] = 1) or @parm5=0) 
     AND (@parm4 is null or T.column4=@parm4) 
     
     
    SELECT @TotalCount = Count(1) FROM @result 
     
     SELECT 
     ... -- Columns
     FROM 
     ( 
     SELECT .. -- Columns
      , 
     Row_Number() over 
     ( 
      ORDER BY 
      CASE WHEN @sortBy = 'SortBy1 ASC' THEN Column1 END ASC, 
      CASE WHEN @sortBy = 'SortBy2 ASC' THEN Column2 END ASC, 
      CASE WHEN @sortBy = 'SortBy3 ASC' THEN Column3 END ASC, 
      CASE WHEN @sortBy = 'SortBy4 ASC' THEN Column4 END ASC, 
      CASE WHEN @sortBy = 'SortBy5 ASC' THEN Column5 END ASC, 
      CASE WHEN @sortBy = 'SortBy6 ASC' THEN Column6 END ASC, 
      CASE WHEN @sortBy = 'SortBy7 ASC' THEN Column7 END ASC, 
      CASE WHEN @sortBy = 'SortBy8 ASC' THEN Column8 END ASC, 
      CASE WHEN @sortBy = 'SortBy9 ASC' THEN Column9 END ASC, 
      CASE WHEN @sortBy = 'SortBy10 ASC' THEN Column10 END ASC, 
      CASE WHEN @sortBy = 'SortBy1 DESC' THEN Column1 END DESC, 
      CASE WHEN @sortBy = 'SortBy2 DESC' THEN Column2 END DESC, 
      CASE WHEN @sortBy = 'SortBy3 DESC' THEN Column3 END DESC, 
      CASE WHEN @sortBy = 'SortBy4 DESC' THEN Column4 END DESC, 
      CASE WHEN @sortBy = 'SortBy5 DESC' THEN Column5 END DESC, 
      CASE WHEN @sortBy = 'SortBy6 DESC' THEN Column6 END DESC, 
      CASE WHEN @sortBy = 'SortBy7 DESC' THEN Column7 END DESC, 
      CASE WHEN @sortBy = 'SortBy8 DESC' THEN Column8 END DESC, 
      CASE WHEN @sortBy = 'SortBy9 DESC' THEN Column9 END DESC, 
      CASE WHEN @sortBy = 'SortBy10 DESC' THEN Column10 END DESC 
     ) AS RowNumber , 
     
     FROM @result T 
     left join Table2 TD WITH (NOLOCK) on T.Column1 = TD.ID 
     ) as TC 
     
     WHERE RowNumber BETWEEN @rowStart AND @rowEnd 
     
    

     


    • Edited by TravelMan Monday, March 21, 2011 4:47 PM add more detail
    Monday, March 21, 2011 4:38 PM

Answers

  • Here are some suggestions:

    1 - First try to identify which statement is giving troubles.

    2 - Avoid inserting into a table variable if the query is reading lot of data and could benefit from parallelism. Use a permanent or temporary table.

    3 - If you are going to use a large intermediate result (like the table variable with more than 50 rows) to join to another table, like in:

    > FROM @result T left join Table2 TD WITH (NOLOCK) on T.Column1 = TD.ID

    it is better to use a permanent or temporary table, since SQL Server does not mantain statistics (histogram) for table variables. If you are going to use a table variable, then try recompiling the statement using "OPTION (RECOMPILE)" to let the optimizaer know the cardinality of the table variable.

    4 - As mentioned by Eric, if the distribution of your data is not the same for different sets of parameters, then it will be better to recompile the statement (query 1 - again "OPTION (RECOMPILE)" ) so the optimizer can use a better plan based on the values being passed. You mentioned that the sp is being executed a couple of thousand times per day, and this is not much so I would preffer dedicating some milliseconds for the optimizer in order to use a better plan.

    5 - For the last query, where you are using  dynamic sorting, it will be better using dynamic sql and send different statements and having different plans, than having one plan to fit all. You can also create different stored procedures and call the appropiate one based on the column you want to use for sorting (order by subclause in the over clause).

    Here are some good resources that can help you to tune your sp.

    - Erland Sommarskog's home page:

    http://www.sommarskog.se/dynamic_sql.html

    http://www.sommarskog.se/dyn-search.html

    http://www.sommarskog.se/query-plan-mysteries.html

    - The book Inside Microsoft SQL Server 2008: T-SQL Programming

     

     


    AMB

    Some guidelines for posting questions...


    • Proposed as answer by Naomi NModerator Monday, March 21, 2011 6:41 PM
    • Marked as answer by TravelMan Thursday, March 24, 2011 7:23 PM
    Monday, March 21, 2011 6:39 PM
    Moderator

All replies

  • You better do that. See why http://msdn.microsoft.com/en-us/library/ms181647.aspx

    Or http://msdn.microsoft.com/en-us/library/ms190439.aspx.

    It is mostly because your indexes are changeing and so your execution plan.


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Monday, March 21, 2011 4:43 PM
  • Thanks your input.  However, my question is when we should/shouldnot use with recompile. 

     

    Also, the Table2 what has 100 million rows is part of replication subscribtion. Do you think that might be an issue? You know, we can move history data to archive/history table and left current able much smaller. 

    Monday, March 21, 2011 5:34 PM
  • Travel,

     

    Yes, 100million is a lot of data, but, its not impossible to work with, here we have a table with about 1/2 billion and it work very well.

    In the procedure, i wouldn´t use recompile if this proc goiing to be used all time, actually, i would use this case a fixed plan, but, if you goiing to use lets say, one time a week, maybe recompile is a good option...


    ------------------------------------------------------------- Oracle OCA11g
    Monday, March 21, 2011 5:42 PM
  • I will use RECOMPILE in stored procedure that data is changing frequently. Meaning that everyday or everyweek there are a lot of transaction happening in them. So their index is chaning also.

    Regarding your second question for archive/history:

    I will archive them in a second database or partition depends on how you want to access them. If you are NOT going to have any access or it could be very rare, I will archive them in a separate database and maybe mark them as readonly. It depends how often you will access them. If you do not need anything prior a year, every month you can run a procedure and send data from current database to archive database.


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Monday, March 21, 2011 5:43 PM
  • It sounds like a possible case of parameter sniffing.  I would suggest you load your stored procedure input parameters into local variables in the procedure to prevent this from occurring in the future.  (Here's a reference, see option #2: http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/) 

    Also, how many rows are into @StatusTable?  Are they distinct?  If so,  you may consider setting a primary key as the index, to help speed up that join...

    DECLARE @StatusTable as TABLE(StatusValue varchar(128) PRIMARY KEY)

    I would consider not returning the @TotalCount either.  If you're using .NET, you can get the count easily from the collection.

    I would try to get rid of the temp table as well, either by using a CTE or by just returning the rowcount with the original select that you're returning and adding a WHERE clause

    to limit what's returned. 

    I hope that helps.


    Eric Isaacs
    Monday, March 21, 2011 6:15 PM
  • Here are some suggestions:

    1 - First try to identify which statement is giving troubles.

    2 - Avoid inserting into a table variable if the query is reading lot of data and could benefit from parallelism. Use a permanent or temporary table.

    3 - If you are going to use a large intermediate result (like the table variable with more than 50 rows) to join to another table, like in:

    > FROM @result T left join Table2 TD WITH (NOLOCK) on T.Column1 = TD.ID

    it is better to use a permanent or temporary table, since SQL Server does not mantain statistics (histogram) for table variables. If you are going to use a table variable, then try recompiling the statement using "OPTION (RECOMPILE)" to let the optimizaer know the cardinality of the table variable.

    4 - As mentioned by Eric, if the distribution of your data is not the same for different sets of parameters, then it will be better to recompile the statement (query 1 - again "OPTION (RECOMPILE)" ) so the optimizer can use a better plan based on the values being passed. You mentioned that the sp is being executed a couple of thousand times per day, and this is not much so I would preffer dedicating some milliseconds for the optimizer in order to use a better plan.

    5 - For the last query, where you are using  dynamic sorting, it will be better using dynamic sql and send different statements and having different plans, than having one plan to fit all. You can also create different stored procedures and call the appropiate one based on the column you want to use for sorting (order by subclause in the over clause).

    Here are some good resources that can help you to tune your sp.

    - Erland Sommarskog's home page:

    http://www.sommarskog.se/dynamic_sql.html

    http://www.sommarskog.se/dyn-search.html

    http://www.sommarskog.se/query-plan-mysteries.html

    - The book Inside Microsoft SQL Server 2008: T-SQL Programming

     

     


    AMB

    Some guidelines for posting questions...


    • Proposed as answer by Naomi NModerator Monday, March 21, 2011 6:41 PM
    • Marked as answer by TravelMan Thursday, March 24, 2011 7:23 PM
    Monday, March 21, 2011 6:39 PM
    Moderator
  • you can refer http://www.varindersandhu.in/2011/11/15/sql-server-stored-procedure-with-recompile/
    If you think my suggestion is useful, please rate it as helpful.

    If it has helped you to resolve the problem, please Mark it as Answer.

    Varinder Sandhu www.varindersandhu.in
    Wednesday, November 23, 2011 6:20 AM