May 16, 2016 at 6:16 am
Estimated Execution plan
I am looking at improving the efficiency of someone's SQL. Adding Indexes is not a possibility. So I look at one of the copre WHERE clauses and this compares a datetime to a gard coded string i.e.'2015-12-24' There is an index (non clustered in the dattime column I am looking at. I have tried to force using the non clustered index as well as the clustered in the query.
I then looked again and checked the performance of comaring my datecolumn to a.) '2014-12-24'and b.) tweaking Getdate to return 24th Dec 2014 as a datetime.
When I used the Estimated Execution Plan the Estimated Number of Rows was vastly different! 827,911 for '2014-12-24' and 21,733,300 for the getdate()
First thought was I had messed up the SQL so I tweeked it to return just one month both and again the estimated execution plan showed differenet estimated nuymber of rows. In actuual fact and I am just guessing here but however I alter the getdate() by adding or removing months the estimated rows dont change.
Is putting a getdate() in the wehre clause a really bad idea? Should I declare a variable and set it before the select statement? Or is there another explanation?
Thanks in advance
E
May 16, 2016 at 6:50 am
No, it's not automatically a bad idea, and the variable may or may not give any better estimates.
It has to do with parameter sniffing and the ability of the optimiser to see the value at compile time and to use it along with the stats to come up with row estimations.
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
May 18, 2016 at 9:55 am
Thanks,
that makes a lot of sense.
E
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply