July 19, 2016 at 9:35 am
I have a same query to find MIN(download_id) but different data ranges in where clause. The criteria which has more number of rows takes less time but query result which has relatively less number of rows takes more time with more logical and physical reads. I am scratching my head for last few days. please help...
Query 1:
----------
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT min(Download_ID) FROM dbo.tbl_Download WHERE DownloadDate > '2011-01-01 00:00:00'
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'tbl_Usage'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Query 2:
----------
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT min(Download_ID) FROM dbo.tbl_Download WHERE DownloadDate > '2016-07-01 00:00:00'
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'tbl_Usage'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Query 3:
---------
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT min(Download_ID) FROM dbo.tbl_Download WHERE DownloadDate > '2014-05-01 00:00:00'
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'tbl_Usage'. Scan count 1, logical reads 926996, physical reads 2026, read-ahead reads 872488, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 14680 ms, elapsed time = 112610 ms.
The third query which takes less number of rows compared to second query for aggregation. But it takes more time. I also updated the STATISTICS but no luck. The performance remains same. The column - Download_ID is a primary key with clustered index.
July 19, 2016 at 10:31 am
When query behavior gets that weird, the first thing I suggest is DBCC CHECKDB and make sure you don't have something going wrong with the storage.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 21, 2016 at 11:06 am
1. check the execution plan for all the queries.
2. Check if there is a index on date that you are using to filter the data. if not, then create a non-cluster index.
July 21, 2016 at 12:10 pm
You didn't drop a nonclustered index between query 2 and query 3, did you?
Are they run on the same server against the same table and database?
I'm asking because those are the only things I can think of. If not, then see Gus's post above and hope that isn't the answer.
July 21, 2016 at 6:07 pm
For God's sake please don't try to use common logic, just look at the plans! The following is just my guessing on what could have happened.
There are two ways to execute this query:
1. Get first value from Download_ID index and then check if condition on DownloadDate is fulfilled, if not take the next value and repeat the process until it finds a record
2. Get all the records that satisfy DownloadDate > 'somedate' condition (potentially table scan), sort them by Download_ID and then take the 1st one.
It could be that
for Query 1 it picked 1st approach. Because there are a lot of records where DownloadDate > '2011-01-01 00:00:00' it can quickly find a good record in an ordered Download_ID index.
For Query 2 it picked 2nd approach, and since there are not so many records past '2016-07-01 00:00:00' it can use the index seek (Download_ID) + lookup and sort, and it works fast.
For Query 3 it also is using 2nd approach, but there are many more records in 2 year period so it decided to scan the whole table (and read it from disk) and then the resultset also has to be sorted. That's why it could be slow.
July 25, 2016 at 12:56 pm
DBCC CHECKDB looks good.
July 25, 2016 at 12:58 pm
I have not dropped any nonclustered index.Also we run on the same server against the same table and database.
July 25, 2016 at 5:43 pm
msnathan (7/25/2016)
I have not dropped any nonclustered index.Also we run on the same server against the same table and database.
Have you looked at the execution plans?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply