Hello,
we have some user forms that include a 'period' menu, where the user can request the server to return data for a specific date range, such as "Purchase Orders issued between the 1st and the 10th of October 2008".
The logic is then to add "on the fly" the date range to the original sql query and requery the data. The syntax of the filter added to the query is:
WHERE myDate >=dateMin and myDate <= dateMax
or, if the original query already has a filter clause:
WHERE <original filter> AND (myDate >=dateMin and myDate <= dateMax)
With both dateMin and dateMax in the 'YYYYMMDD' format.
We began yesterday to get some query timeouts for our 'errors' form, which specifically requests our 'errors' table. After some tests, it appeared this timeout issue was happening only when data was requested from the Error table and for the month of October. The same query, with the very same syntax (only dateMin and dateMax values are changed) when sent with another range (september, august, or whatever) on the same table, did not time out! This happened either when sent from the app or directly from the Sql Server Management Studio.
We avoided the timeout problem by adding an index on the errorDate column of the Errors Table (we should have done this before, I know, but we forgot!). We still have a query delay which is 4 to 5 times longer than the standard one when requesting on October's dates! We have been trying to query on smaller intervalls, like "first 15 days", "last 15 days". The "first 15 days" query takes longer than the last 15 days one, which is still significantly slower than queries on other periods.
I feel like the problem is only avoided, not really solved, and I still feel quite disturbed by this behaviour. Has anyone ever noticed such weird things, or does anyone have any idea about what is happening?