I have a simple select statement that selects data from a SQL Server 2000 table (so old) with about 10-20 million rows like this -
@startDate = '2014-01-25'
@endDate = '2014-02-20'
SELECT
Id, 6-7 other columns
FROM
Table1 as t1
LEFT OUTER JOIN
Table2 as t2 ON t1.Code = t2.Code
WHERE
t1.Id = 'G59'
AND (t1.Entry_Date >= @startDate AND t1.Entry_Date < @endDate)
This gives me about 40K rows in 10 seconds. But, if I set @startDate = '2014-01-30', keeping @endDate the same ALWAYS, then the request takes about 2 minutes 30 seconds
To create the same number of lines, I tried it again from 01-30, and it took 2 minutes 48 seconds.
I am surprised to see the difference. I did not expect that the difference would be so big. Rather, I expected it to take the same time or less for a smaller date range.
What could be the reason for this and how to fix it?
source
share