March 29, 2016 at 6:56 am
Hi,
In the AdventureWorksDB, I queries Sales.SalesOrderDetail table on the column CarrierTrackingNumber which doesn't have any index defined on it and I expected a clustered index scan. This query returns only 9 rows.
The query plan shows clustered index scan with estimated and actual number of rows as 12137 (total rows) and then it adds two compute scalar and filter operator to get 9 rows.
However I expected that in the Clustered index scan it should have given estimated rows as some value and actual as 9
So I made a copy of Sales.SalesOrderDetail using select into and the table name is Sales.SalesOrderDetailCopy.
I create all the indexes which were present for Sales.SalesOrderDetail.
When I queried Sales.SalesOrderDetailCopy table with CarrierTrackingNumber, I got as I expected.
So my question is why querying Sales.SalesOrderDetail table gave such a query plan with filter operator.
select * from Sales.SalesOrderDetail where CarrierTrackingNumber = 'A870-450A-8A'
select * from Sales.SalesOrderDetailCopy where CarrierTrackingNumber = 'A870-450A-8A'
March 29, 2016 at 7:27 am
er.mayankshukla (3/29/2016)
Hi,In the AdventureWorksDB, I queries Sales.SalesOrderDetail table on the column CarrierTrackingNumber which doesn't have any index defined on it and I expected a clustered index scan. This query returns only 9 rows.
The query plan shows clustered index scan with estimated and actual number of rows as 12137 (total rows) and then it adds two compute scalar and filter operator to get 9 rows.
However I expected that in the Clustered index scan it should have given estimated rows as some value and actual as 9
So I made a copy of Sales.SalesOrderDetail using select into and the table name is Sales.SalesOrderDetailCopy.
I create all the indexes which were present for Sales.SalesOrderDetail.
When I queried Sales.SalesOrderDetailCopy table with CarrierTrackingNumber, I got as I expected.
So my question is why querying Sales.SalesOrderDetail table gave such a query plan with filter operator.
select * from Sales.SalesOrderDetail where CarrierTrackingNumber = 'A870-450A-8A'
select * from Sales.SalesOrderDetailCopy where CarrierTrackingNumber = 'A870-450A-8A'
Try updating statistics on the original table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 29, 2016 at 7:43 am
The original table has a computed column (LineTotal) in it.
When you created the copy of the table with SELECT...INTO, only the data for that column was moved to the new table; it lost its definition as a computed column.
That's probably the reason for the difference, as at least one of the compute scalar operators seems to be for calculating the line total.
I'm just scanning the XML of the plans (don't have SSMS on my current machine), so that might not be the whole story. I'll confirm in a bit when I have access to a server and can test.
Cheers!
March 29, 2016 at 10:22 am
Yeah, Jacob has it nailed. Because it's a calculated column, it's performing the calculation before doing the filtering in the first plan. The second plan, with stored data and no calculated column, filters right at the index scan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2016 at 10:56 am
Great Jacob, you got it correct.
Thanks!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply