Understanding Why SQL Query Takes So Long
I have a fairly large SQL query. Below is a simplification of the problem I am seeing.
SELECT *
FROM dbo.MyTransactionDetails TDTL
JOIN dbo.MyTransactions TRANS
on TDTL.ID = TRANS.ID
JOIN dbo.Customer CUST
on TRANS.CustID = CUST.CustID
WHERE TDTL.DetailPostTime > CONVERT(datetime, '2015-05-04 10:25:53', 120)
AND TDTL.DetailPostTime < CONVERT(datetime, '2015-05-04 19:25:53', 120)
MyTransactionDetails has about 7 million lines, while MyTransactions has about 300 thousand lines.
The above query takes about 10 minutes, which is insane. All indexes have been reindexed and all ID columns have an index.
Now if I add the following lines to the WHERE clause the query takes about 1 second.
AND TRANS.TransBeginTime > CONVERT(datetime, '2015-05-05 10:25:53', 120)
AND TRANS.TransBeginTime < CONVERT(datetime, '2015-05-04 19:25:53', 120)
I know that the database content and TransBeginTime are almost identical to the DetailPostTime parameter, so these are additional where clauses shouldn't filter much more than the JOIN.
Why is adding them so much faster?
The problem is that I cannot use the filter on the TransBeginTime as it is not guaranteed that the transaction detail will be published on the same date.
EDIT: I should also add that the execution plan says 50% of the time is taken up by MyTransactionDetails
source to share
The percentages shown in the plan (both estimated and actual) are estimates that are based on the assumption that the row count is correct. In bad cases, the percentages can be completely wrong, even though 1% may indeed be 95%.
To find out what's really going on, turn on "io statistics". This will tell you the logical amount of I / O for each table - and getting this usually means that the time is decreasing as well.
You can also look at the actual plan and there are many things that can cause the slowness, like scanning, sorting, finding keys, coils, etc. If you include both the I / O statistics and the execution plan (preferably the actual xml, not just the picture) it is much easier to understand what is going wrong.
source to share