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»»

Parameters vs. No Parameters Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 3:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:30 AM
Points: 58, Visits: 297
I was asked to look at a query today that was taking a very long time to return. I waited 10 minutes and nothing was happening. It wasn't blocked by any other process (in fact the query was running against a backup of the live database and therefore nothing else was happening in the database). The query plan also looked fine - no table scans or index scans and no suggested missing indexes.

The query was filtered on a date field like so:

 a.actDateAdded between DATEADD(month, DATEDIFF(month, 0, GETDATE())-3, 0) and 
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

In an effort to improve the performance, I changed this to use parameters instead and set the parameters to the exact same values as above:

a.actDateAdded between @start and @end

This time the query ran in less than a minute.
So my question is, why the dramatic improvement?

Post #1441148
Posted Thursday, April 11, 2013 4:08 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: Tuesday, August 12, 2014 12:24 AM
Points: 718, Visits: 545
Can you provide table structure, sample data and query
Post #1441166
Posted Thursday, April 11, 2013 4:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 5,045, Visits: 11,794
Maybe this article explains it.

Your GETDATE() function is being re-evaluated many times - your faster solution avoids this.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1441174
Posted Thursday, April 11, 2013 4:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:30 AM
Points: 58, Visits: 297
That article makes sense, thank you
Post #1441180
Posted Thursday, April 11, 2013 5:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 42,805, Visits: 35,925
It won't be due to multiple executions of getdate. The function is fast, but besides that it will only be evaluated once in the query execution (otherwise you'd get multiple different dates in a long-running query, which you don;'t).

Probably has to do with row estimations, difference between how the optimiser handles the constant values vs the parameters (or were they variables?).
Any chance of seeing the exec plan of both?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1441186
Posted Thursday, April 11, 2013 5:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 5,045, Visits: 11,794
GilaMonster (4/11/2013)
It won't be due to multiple executions of getdate. The function is fast, but besides that it will only be evaluated once in the query execution (otherwise you'd get multiple different dates in a long-running query, which you don;'t).

Probably has to do with row estimations, difference between how the optimiser handles the constant values vs the parameters (or were they variables?).
Any chance of seeing the exec plan of both?


Hmm, I just knew that I should have kept my mouth shut on this one!



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1441189
Posted Thursday, April 11, 2013 5:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:30 AM
Points: 58, Visits: 297

It won't be due to multiple executions of getdate. The function is fast, but besides that it will only be evaluated once in the query execution (otherwise you'd get multiple different dates in a long-running query, which you don;'t).


Now that you mention it, the query plans do look similar and there are no index scans in either, so I suspect you are correct.
And yes it is variables, not parameters.
I've attached the query plans for both.



  Post Attachments 
sqlplan_with_variable.sqlplan (2 views, 224.50 KB)
sqlplan_without_variable.sqlplan (5 views, 200.73 KB)
Post #1441191
Posted Thursday, April 11, 2013 5:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 42,805, Visits: 35,925
Actual plans please, not estimated.

Index and table scans are not necessarily bad, so the absence of them is not necessarily good.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1441194
Posted Thursday, April 11, 2013 6:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:30 AM
Points: 58, Visits: 297
Here is the actual plan when using variables.
I'm still waiting for the query without variables to complete. 20 minutes and counting....


  Post Attachments 
sqlplan_actual_with_variable.sqlplan (4 views, 283.09 KB)
Post #1441210
Posted Thursday, April 11, 2013 9:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:30 AM
Points: 58, Visits: 297
The query did not return after 60 minutes. My process was killed.
Can there really be that much difference in such a small alteration to a query?
Post #1441306
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse