October 1, 2012 at 2:42 pm
I have 2 queries that run against the same table. Table is a heap with a GUID PK and has 392780 rows.
Query 1 has no join and 2 WHERE conditions. The actual execution plan shows a table scan with Actual Number of Rows = 5073.
Query 2 has a join against a second table on the GUID PK. It's actual execution plan shows a table scan with Actual Number of Rows = 392780.
Execution times are faster on query 1.
Question - can a table scan be a "partial" table scan? If it can, I assume it is using one of the other indexes to limit the number of rows it has to read? I also assume that query 2 has to read every row to see if it can match against the other table?
TIA
John
John Deupree
October 1, 2012 at 2:57 pm
It can, but only in the case where there's a TOP or something similar.
Worth noting that the actual row count is not the number of rows read, it's the number of rows returned. So if that's a table scan with a predicate (read all rows and filter out the ones that don't match) the actual row count will be less than the total rows in the table, but every single row was still read and examined.
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply