asked
Unmatched estimated no of rows and sub tree cost
Table has around 5 million rows with unclustered index on picktime (datetime) column. I selected rows from table using two different ways. Execution plan's physical operation and actual no. of rows are same for both queries but differ in terms of estimated no. of rows and estimated sub tree cost. I am unable to find reason behind the scene. 1. Passing direct value in where clause SELECT ordernumber , binloc , QOR , QSH , ITM , Picker , z_assoc_row , brand , pickarea , deptcode , picktime FROM cpick_lines WITH ( NOLOCK ) WHERE picktime = '2015/01/02' Execution plan : ![alt text][1] 1. Using Date Variable in where clause DECLARE @picktime DATETIME SELECT @picktime = '2015/01/02' SELECT ordernumber , binloc , QOR , QSH , ITM , Picker , z_assoc_row , brand , pickarea , deptcode , picktime FROM cpick_lines WITH ( NOLOCK ) WHERE picktime = @picktime ![alt text][2] Could any one please let me know about this case? Thanks :) [1]: /storage/temp/2746-doubt1.png [2]: /storage/temp/2747-doubt2.png
A difference in the estimated number of rows and the actual number rows goes back to your statistics. You have an index on a heap table (a table without a clustered index). It looks like the statistics for the value you passed to the query, one looks like @picktime, the other looks like @1, so simple parameterization, would be used to look at the statistics. Depeding on if these are parameters or local variables, you'll get different behavior. The @1 is a parameter. It's going to use the actual value against the statistics. That's why the actual and estimate match. The @picktime, I'm guessing, is a local variable. Except in the instance of a recompile, a local variable will use an average of the statistics, not a specific value. That's why you're seeing 10059.7 instead of 3828. This is straight forward and expected behavior. A strong recommendation, the vast majority of tables within SQL Server should have a clustered index. The exceptions to this are very exceptional.
Thank you Grant. Your valuable comment forced me to learn more about relation between optimization and different types of search arguments.
18 People are following this question.
