The same SQL runs slower or faster with the same parameters

Question

I have a query that runs extremely fast (1 sec) see below:

SELECT *
FROM    ( select ROW_NUMBER() OVER ( ORDER BY  [Rank] DESC ) AS RowNum, *
          FROM [product].[FnSearchKeyword]('basic', null, null, null, null, null, null,           null)
    ) AS RowConstrainedResult
WHERE   RowNum     = 1 AND RowNum < 30

Bad if I put this query inside a procedure it takes 15 seconds to run as follows:

 CREATE  anydata 
    @keywords nvarchar(4000),
    @minimunRate int, 
    @priceFrom decimal, 
    @priceTo decimal, 
    @relaeseStart datetime,
    @releaseEnd datetime,
    @categoryList nvarchar(4000), 
    @storeList nvarchar(4000),
    @rowBegin int,
    @rowEnd int,
    @orderBy int,
    @isAdult bit = null
 AS 
 SELECT *
 FROM    ( select ROW_NUMBER() OVER ( ORDER BY  [Rank] DESC ) AS RowNum, *
           FROM [product].[FnSearchKeyword]('basic', @minimunRate , @priceFrom , @priceTo      , null, null, null, null)
    ) AS RowConstrainedResult
 WHERE   RowNum     = 1 AND RowNum < 30

When I play the SQL, the return delay 15 sec:

 exec anydata  null, null, null, null, null, null, null, null, null, null, null

Note:

• Parameters of Function FnSearchKeyword have the same declared type in AnyData Procedure.

• I'm passing all values "NULL" in the exec procedure, just to have the same result as shown above query that runs fast. No changes in the scope of the parameters

Honestly, I noticed that the "Execution Plan" is changed when I run the query through the Procedure, but do not know how to solve this, since the parameters of the query are the same.

Unfortunately I need to be put inside a PROC query for other reasons not mentioned here not to further complicate the issue.

About Duplicated Message: Its question isn´t duplicated because it its slow running in anyone system (Query Analyzer, C# asp.net, others).

Answer 1

The parameters to your stored procedure now are the same, but what were they when the plan was first cached? You can thwart parameter sniffing by adding OPTION RECOMPILE.

If 2008+ (please specify version!), since the statement itself should use the same plan, you should also check SET settings for the session and the stored procedure. For example, having ARITHABORT on for one and not the other can lead to different plans, and if you have different plans, one can be bad and one can be good. Also note that even a single space difference within the query text can make SQL Server treat them as two different plans.

By forcing the settings and the query text to be the same, you can avoid these plan differences, but you still can't control the parameter sniffing issue this way. If it is a parameter sniffing issue you can also experiment with OPTIMIZE FOR...