April 11, 2013 at 3:31 am
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?
April 11, 2013 at 4:08 am
Can you provide table structure, sample data and query
April 11, 2013 at 4:20 am
April 11, 2013 at 4:49 am
That article makes sense, thank you 🙂
April 11, 2013 at 5:10 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2013 at 5:21 am
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! 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 11, 2013 at 5:28 am
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.
April 11, 2013 at 5:47 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2013 at 6:28 am
Here is the actual plan when using variables.
I'm still waiting for the query without variables to complete. 20 minutes and counting....
April 11, 2013 at 9:02 am
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?
April 11, 2013 at 9:53 am
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.
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
April 12, 2013 at 2:38 am
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...
April 12, 2013 at 2:41 am
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
April 12, 2013 at 8:30 am
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...
April 12, 2013 at 8:37 am
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply