Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Parameter Sniffing Expand / Collapse
Author
Message
Posted Thursday, August 21, 2014 5:59 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 785, Visits: 1,426
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]



  Post Attachments 
Query_Using_From.sqlplan (15 views, 76.41 KB)
Query_Using_FLocal.sqlplan (8 views, 75.09 KB)
Post #1605767
Posted Thursday, August 21, 2014 6:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:39 AM
Points: 2,217, Visits: 5,999
Quick thought, have you tried using WITH RECOMPILE?


In addition, are the statistics up to date?
Post #1605775
Posted Thursday, August 21, 2014 7:51 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 785, Visits: 1,426
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.
Post #1605823
Posted Thursday, August 21, 2014 10:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:39 AM
Points: 2,217, Visits: 5,999
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]

Post #1605923
Posted Friday, August 22, 2014 2:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:08 AM
Points: 6,809, Visits: 14,024
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1606143
Posted Friday, August 22, 2014 7:03 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 785, Visits: 1,426
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..
Post #1606260
Posted Friday, August 22, 2014 7:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:08 AM
Points: 6,809, Visits: 14,024
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1606263
Posted Sunday, August 24, 2014 1:44 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 785, Visits: 1,426
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.


Post #1606836
Posted Monday, August 25, 2014 6:36 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 785, Visits: 1,426
Chris,

Here is the execution plan..
The OUTER APPLY does 100k seeks on the Follow-Up table.

Louis.


  Post Attachments 
ChrisM_Solution.sqlplan (4 views, 109.79 KB)
Post #1607035
Posted Monday, August 25, 2014 10:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:04 AM
Points: 44, Visits: 98
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
Post #1607124
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse