Problems filtering on a date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problems filtering on a date range

Author

Topic

Gyte
Starting Member

23 Posts

Posted - 2007-10-26 : 03:02:17

I have the following problem. On an ASP page many queries are being executed. These queries work very fast. On this page I have a filter utility by which you can filter on a date range. All this filter utility does is add to all the queries an AND statement with the date range. For example if you filter on 1 august 2007 to 30 august 2007, it adds the following SQL-code :

AND Rdate >= '20070801' AND Rdate <= '20070830'

This works fine, but if you filter on a date range close to todays date, then the queries are very slow and sometimes a time-out occurs.

My question is what causes this? Is it caused by something in the records of the database. Has anyone else encountered something similar like this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 03:03:54

Do you have an index at all over rdate column?
Perhaps a clustered one?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 03:04:28

Also, if you have functions in your select list, or more complicated WHERE's the query will be slow.

E 12°55'05.25"
N 56°04'39.16"

Gyte
Starting Member

23 Posts

Posted - 2007-10-26 : 03:34:46

No, I don't have an index on the Rdate column. How do you do that in SQL-Server 2005 ? What type of index should I use here ? Rdate has no unique values.

Here is an example of a query, when filtering from 1 January 2007 to 31 December 2007 :

SELECT COUNT(tblReport.RleadID) as numbLead
FROM tblReport
LEFT JOIN tblUsers ON tblUsers.ID_tblUsers = tblReport.RsalesID
LEFT JOIN tblContact ON tblContact.V91000 = tblReport.Rclientnumber
LEFT JOIN tblV ON tblV.ID_tblV = tblReport.RLeadID
WHERE tblUsers.IsShowOnSalesReports > 0
AND tblReport.Rdate >= '20070101' AND tblReport.Rdate <= '20071231'

This foregoing query works very fast.

But if you filter from 1 Oktober 2007 to 26 Oktober 2007 (todays date) it works very slow :

SELECT COUNT(tblReport.RleadID) as numbLead
FROM tblReport
LEFT JOIN tblUsers ON tblUsers.ID_tblUsers = tblReport.RsalesID
LEFT JOIN tblContact ON tblContact.V91000 = tblReport.Rclientnumber
LEFT JOIN tblV ON tblV.ID_tblV = tblReport.RLeadID
WHERE tblUsers.IsShowOnSalesReports > 0
AND tblReport.Rdate >= '20071001' AND tblReport.Rdate  <= '20071026'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 04:12:18

1) What number do the two queries return?
2) Is LEFT JOIN really necessary? Can't you use INNER JOIN?
3) tblContact table and tblV table are not used in query.
4) CREATE INDEX IX_Rdate ON tblReport (Rdate)

E 12°55'05.25"
N 56°04'39.16"

Gyte
Starting Member

23 Posts

Posted - 2007-10-26 : 04:47:06

1) The first (fast) query returns 1238. The second (slow) query returns 296.
2) Yes, LEFT JOIN is necessary here. Is an INNER JOIN faster than a LEFT JOIN ?
3) In the filter utility you can also search on other fields which are present in tblContact and tblV. This already works fast.
4) Thank you very much, this solved the problem. I made the index and the queries work very fast now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources