SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parameters vs. No Parameters


Parameters vs. No Parameters

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216369 Visits: 46277
Yes, absolutely.

I suspect you have stale stats. The version with the getdate, the optimiser can sniff the values and use those values to get an estimate from the row distribution in the statistics, whereas with variables if can't see the values and hence can only get a generic row estimation. If your stats are off (very common on a table with an ascending datetime column), the version with the function could get a completely incorrect (way low) estimate and generate an exec plan that's optimal for very few rows. When the query runs and gets lots of rows the plan is very, very, very bad indeed.

I've seen a query go from 30 minutes to not finishing after 4 hours because of that problem. The exec plan, if you look only at the estimated plan, looks fine, seeks, key/rid lookups, nested loop joins and low row estimations (like the one row estimated in the plan you attached). It's only with the actual plan you can see the extreme mis-estimate on the row count.

http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


johnmcp
johnmcp
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 362
Very interesting indeed.
I checked my stats, they were almost 48 hours out of date when I was running the query yesterday (and have not been updated as of yet). There are about 40,000 rows added to this table on a daily basis and its has over 13 million rows in total.
The thing is, the filter on my query looks at the last 3 months but starting at the 1st of the month. So its not concerned with the last 11 days.
I'm still confused...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216369 Visits: 46277
Try updating stats with full scan and then the query with the getdates again.

To diagnose the problem, I really need to see the actual plan of that query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


johnmcp
johnmcp
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 362
I updated the statistics on each of the 4 tables in the query, on by one.
The first 3 updates had no impact, but when I updated the 4th table stats, it ran in 40 seconds!
But I still don't understand how this made such a difference. The actual query plan is attached...
Attachments
GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216369 Visits: 46277
I might be able to explain, if I knew which table the '4th table' was. ;-)

Is that the actual plan from before the stats update or after?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


johnmcp
johnmcp
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 362
Oops Pinch
That would be the transactions table.
That's the actual plan after the stats update.
Now that I think of it, I didn't specify fullscan. But I think this is the default right?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216369 Visits: 46277
johnmcp (4/12/2013)
Oops Pinch
That would be the transactions table.


:-) I'll take a look a bit later if I find the time.

Now that I think of it, I didn't specify fullscan. But I think this is the default right?


Depends on the size of the table. Large tables default is sampled.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search