SQLServer 2005, ~7 million records, queries are using a clustered index keyed on the field "date".
Both queries below have the same execution plan, IO Cost, etc but Query 1 takes ~38 seconds whereas Query2 takes ~.3 seconds.
The only difference is in one of the where clauses (point=). It seems to have something to do with the fact that the first query is only returning 66 rows, but I'm at a loss as to why it's so slow. Query 1 is sub 1 second if I do a select top 66, but ~38 seconds with a top 67.
Obviously there's something I'm missing, but I'm completely clueless as to what it is.
Thanks - James
Query 1:
SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName
FROM history
WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')
ORDER BY date DESC
37759 ms
66 rows
IO Cost: 188.225
Returns rows 1-61 < 1 second, 62-66 @. ~38 seconds
Query 2:
SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName
FROM history
WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 1')
ORDER BY date DESC
334 ms
100 rows
IO Cost: 188.225
It really depends on how many records there are of Attenna 1 and Antenna 2.
Clearly out of all your records there are only 66 that match Atenna 2 so it probably had to look through every record taking 38 seconds. however, there seem to be a whole lot more Attenna 1 or they were toward the begging of your records. Because it filled the Top 100 you specified. Once that is filled there is no point for the query to keep executing and it popped back after only 3 seconds.
On top of that your index is no on any of the columns in your where clause. An index is not a magic item. You indexes on your WHERE criteria in order for it to take advantage of it.
|||Query plans are your friend.
Look at the query plan and you will see the difference between both queries.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Yep query plans are the same for both. I've got indices for the other fields also.
The odd thing is with a if I give it a point name that doesn't exist like
SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName
FROM history
WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'xx')
ORDER BY date DESC
It uses the index for point then hits the clustered index, but the same query with a valid point name
SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName
FROM history
WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')
ORDER BY date DESC
it uses only the clustered index. I'm starting to wonder if it might be a problem with Stastics being out of synch for some reason.
Thanks again - James
|||Query 1 required a scan of 100% of the table. Even after looking at the whole table, only 66 records were returned.
In the second query, it found 100 records very quickly so there was no need to continue.
If you were to remove the "Top 100" from these queries, the execution times would be very similar as both queries would be required to scan the whole table (with this caviat: If Query 2 returne 200,000,000 records, it's going to take longer...the table scan won't take longer to locate the records, but actually reading the disk and moveing the bits will take longer).
No comments:
Post a Comment