Why is a table Scan being performed?
SELECT X.ID, X.Field4
FROM
#TaxInvoiceData T
INNER JOIN
xxx X
ON T.Id = X.Id
AND Field2 = @VAR
AND Field3 = 'S'
When I run a query a Full table scan on table X. I do not understand why because the Primary Key of Table X is
ID INT ASC
Field3 CHAR(2) ASC
Field2 DATETIME ASC Unique Non-clustered
There is also an index on
Field2 DATETIME ASC Non-Unique Non-clustered
Doing just
SELECT ID
FROM xxx
WHERE
Field2 = @VAR
AND Field3 = 'S'
Does an Index Seek
Thanks in advance.
Short answer: because the optimizer thinks it would be faster.
However, let's try to read the optimizer's mind.
Since you haven't provided full table schema, I'm going to assume that there's a clustered index on xxx.ID
and that #TaxInvoiceData
is a heap. You're expecting a plan where the PK index is probed for every row in #TaxInvoiceData
, but you're selecting xxx.Field4
which is going to require a bookmark lookup for every match. This could result in 29,000 random I/O requests. Ouch.
Conversely, SQL Server could (and apparently is going to) just perform a larger amount of more efficient sequential I/O doing the table scan and is probably doing a speedy hash match against #TaxInvoiceData
.
So what can you do? You could create a covering index including Field4
. Or you could use index and join hints to force the plan you're looking for (but I suspect performance wouldn't be as good as you hope). Is this query used frequently enough that it is giving your application performance problems or are you just looking to eliminate table scans on principle? If the latter, you may find the overhead of getting rid of the scan isn't worth it in the end.
Edit:
Since you've mentioned that there's no clustered index on the table, this also may affect how efficient lookups from the index are. Unless this table is seeing extremely heavy insert activity, consider changing your PK to clustered. That alone may change the plan, and even if it doesn't it's likely to speed up other operations due to reduced overhead.
也许重写查询会有所帮助:
SELECT X.ID, X.Field4
FROM xxx X, #TaxInvoiceData T
WHERE X.Id = T.Id
AND X.Field2 = @VAR
AND X.Field3 = 'S'
链接地址: http://www.djcxy.com/p/10172.html
上一篇: 我如何监视并获取android应用程序中的http流量?
下一篇: 为什么要执行表格扫描?