Parameter Sniffing

Question

Post reply

Parameter Sniffing

Louis Hillebrand

SSCarpal Tunnel

Points: 4982
More actions
August 21, 2014 at 5:59 am

#294856

If I run this Query using the procedure-parameters (@From, @To) in the WHERE clause, the execution time is over 40 sec.
When I use the local parameters (@F_Loc and @T_Loc) execution time is less than 1 sec.
I have attached both execution plans.
I solved the issue by using a local variable, but is there another way to change this behavior?
The query is not the full code, but enough the represent the issue.
The table dbo.Quotation has about 100.000 rows, Quotation.Follow_up about 344..
CREATE PROCEDURE [Quotation].[Get_Quotes_LH] ( @From DateTime = null, @To DateTime = null )
AS
BEGIN
SELECT @From = COALESCE(@From, Min(QuotationDate)),
@To = COALESCE(@To , Max(QuotationDate))
FROM dbo.Quotation;
DECLARE @F_Loc DateTime = @From,
@T_Loc DateTime = @To;
DECLARE @CutOffDate DateTime = DATEADD(MONTH, -3, CURRENT_TIMESTAMP);
WITH QU_Memo(Quotation_Id, R, Next_Follow_Up) AS (
SELECT Quotation_Id,
ROW_NUMBER() OVER (PARTITION BY Quotation_Id ORDER BY Id DESC),
Next_Follow_Up
FROM Quotation.Follow_Up
)
SELECT QU.QuotationId,
COALESCE(QUIC.Next_Follow_Up, 0)
FROM dbo.Quotation QU LEFT OUTER JOIN
(SELECT * FROM QU_Memo WHERE R = 1) QUIC ON QU.QuotationId = QUIC.Quotation_Id
--WHERE (QU.QuotationDate > @From OR COALESCE(QUIC.Next_Follow_Up, 0) > 0 )
--AND QU.QuotationDate <= @To
WHERE (QU.QuotationDate > @F_Loc OR COALESCE(QUIC.Next_Follow_Up, 0) > 0 )
AND QU.QuotationDate <= @T_Loc
ORDER BY QU.QuotationId DESC;
END
GO
--#########################
EXEC [Quotation].[Get_Quotes_LH]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

Quick thought, have you tried using WITH RECOMPILE?

😎

In addition, are the statistics up to date?

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 2

I tried WITH RECOMPILE, and Statistics are up to date.

The table [Follow_Up] was created recently, I deleted and recreated the table an indexes, but this did not change this behavior.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Here is a quick suggestion, should result in more consistent plan (for better or worse)

😎

CREATE PROCEDURE [Quotation].[Get_Quotes_LH] ( @From DateTime = null, @To DateTime = null )

AS

BEGIN

SELECT @From = COALESCE(@From, Min(QuotationDate)),

@To = COALESCE(@To , Max(QuotationDate))

FROM dbo.Quotation;

DECLARE @F_Loc DateTime = @From,

@T_Loc DateTime = @To;

DECLARE @CutOffDate DateTime = DATEADD(MONTH, -3, CURRENT_TIMESTAMP);

WITH QU_Memo(Quotation_Id, R, Next_Follow_Up) AS (

SELECT Quotation_Id,

ROW_NUMBER() OVER (PARTITION BY Quotation_Id ORDER BY Id DESC),

Next_Follow_Up

FROM Quotation.Follow_Up

)

SELECT QU.QuotationId,

COALESCE(QUIC.Next_Follow_Up, 0)

FROM dbo.Quotation QU

OUTER APPLY QU_Memo QUIC

WHERE QUICK.R = 1

AND QU.QuotationId = QUIC.Quotation_Id

AND (QU.QuotationDate > @F_Loc OR COALESCE(QUIC.Next_Follow_Up, 0) > 0 )

AND QU.QuotationDate <= @T_Loc

ORDER BY QU.QuotationId DESC;

END

GO

--#########################

EXEC [Quotation].[Get_Quotes_LH]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Here are several suggestions -

--SELECT @From = COALESCE(@From, Min(QuotationDate)),

-- @To = COALESCE(@To , Max(QuotationDate))

--FROM dbo.Quotation;

--DECLARE @F_Loc DateTime = @From,

-- @T_Loc DateTime = @To;

-- DECLARE @CutOffDate DateTime = DATEADD(MONTH, -3, CURRENT_TIMESTAMP);

SELECT QU.QuotationId,

ISNULL(QUIC.Next_Follow_Up, 0)

FROM dbo.Quotation QU

OUTER APPLY (

SELECT TOP 1 Next_Follow_Up

FROM Quotation.Follow_Up f

WHERE f.QuotationId = QU.QuotationId

ORDER BY Id DESC

) QUIC

WHERE ((@From IS NULL OR QU.QuotationDate > @From) OR QUIC.Next_Follow_Up > 0)

AND (@To IS NULL OR QU.QuotationDate <= @To)

ORDER BY QU.QuotationId DESC;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 5

Thanks for the solutions,

I tried both versions, but they are slower than my version using a local variable.

It is strange that the execution plans are so very different between using local variables or parameters in the query..

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

It would be interesting to see the Actual execution plans for them...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 7

I included the actual execution plans from my versions in my initial post and generate the EP from your versions when I'm back at work on monday.

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 8

Chris,

Here is the execution plan..

The OUTER APPLY does 100k seeks on the Follow-Up table.

Louis.

WorkasDBA SSCommitted Points: 1684 More actions · Answer 9

Below are some of the options for parameter sniffing:

1. Local variables

2. WITH RECOMPILE

3. Rebuild index - it creates new plan

4. Trace Flag 4136 with QUERYTRACEON HINT

more options can be found at http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 10

Today the procedure again ran with the slow execution plan, taking 2 minutes to run...

I rebuild the procedure using a temporary table with the selected QuotationId's and no select in the final part.

Execution time is now down to about 400 ms from the 2000 ms (Fast EP) or 2+ minutes (slow EP).

Is seems that in this case creating a temporary table is more efficient.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Louis Hillebrand (8/25/2014)
Chris,
Here is the execution plan..
The OUTER APPLY does 100k seeks on the Follow-Up table.
Louis.

The cost is as likely to be the residual predicate on the quotation table. Try this small mod:

SELECT@F = COALESCE(@From, Min(QuotationDate)),

@T = COALESCE(@To , Max(QuotationDate))

FROMdbo.Quotation;

SELECT QU.QuotationId,

ISNULL(QUIC.Next_Follow_Up, 0)

FROM dbo.Quotation QU

OUTER APPLY (

SELECT TOP 1 Next_Follow_Up

FROM Quotation.Follow_Up f

WHERE f.Quotation_Id = QU.QuotationId

ORDER BY Id DESC

) QUIC

WHERE (QU.QuotationDate > @F OR QUIC.Next_Follow_Up > 0)

AND QU.QuotationDate <= @T

ORDER BY QU.QuotationId DESC;

WITH QU_Memo(Quotation_Id, R, Next_Follow_Up) AS (

SELECT Quotation_Id,

ROW_NUMBER() OVER (PARTITION BY Quotation_Id ORDER BY Id DESC),

Next_Follow_Up

FROM Quotation.Follow_Up

)

SELECT QU.QuotationId,

COALESCE(QUIC.Next_Follow_Up, 0)

FROM dbo.Quotation QU

LEFT OUTER JOIN (SELECT * FROM QU_Memo WHERE R = 1) QUIC ON QU.QuotationId = QUIC.Quotation_Id

--WHERE (QU.QuotationDate > @From OR COALESCE(QUIC.Next_Follow_Up, 0) > 0 )

--AND QU.QuotationDate <= @To

WHERE (QU.QuotationDate > @F OR COALESCE(QUIC.Next_Follow_Up, 0) > 0 )

AND QU.QuotationDate <= @T

ORDER BY QU.QuotationId DESC;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

milos.radivojevic Default port Points: 1499 More actions · Answer 12

Hi Louis,

Try to replace the orginial WHERE clause (without local variables) with the following code:

WHERE (QU.QuotationDate > @From OR COALESCE(QUIC.Next_Follow_Up, 0) > 0 )

AND QU.QuotationDate <= @To

ORDER BY QU.QuotationId DESC

OPTION (RECOMPILE);

You should see performance impromevent, at least for some parameter combinations.

___________________________
Do Not Optimize for Exceptions!