timeout when searching 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)

timeout when searching on a date range

Author

Topic

Gyte
Starting Member

23 Posts

Posted - 2007-08-23 : 04:45:35

I have an asp-page in which a lot of SQL-queries are executed and calculations are performed. On this page a filter tool is built in, from which you can select a date range to search on. If you do this than programmatically an AND-condition will be added to all the queries.

For example : AND Rreportdate >= '20070101' AND Rreportdate >= '20070331'

I found out that a script timeout occurs when searching in a date range in the month august. When searching in other date ranges no script timeout occurs.

Could somebody explain why this only happens in the month august?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 04:49:59

Do you have any index for theRreportdate column?
Also, the example above gets ALL records dated "March 31, 2007" and later.

What command-timeout value do you have on the ASP page for command object?

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

Gyte
Starting Member

23 Posts

Posted - 2007-08-23 : 06:42:11

Dear Peso,

Sorry, I didn't write the example correctly. The right example should be :
AND Rreportdate >= '20070101' AND Rreportdate <= '20070331'

No, I do not have an index for Rreportdate.
I get the following error message.

Active Server Pages error 'ASP 0113'

Script timed out

The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTimeout or by changing the value in the IIS administration tools.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 06:51:25

So you have MORE where's than

Rreportdate >= '20070101' AND Rreportdate <= '20070331'

which should be rewritten as

Rreportdate >= '20070101' AND Rreportdate < '20070401'

after you put an [clustered] index on Rreportdate column.

Which are the other WHERE's?

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

Gyte
Starting Member

23 Posts

Posted - 2007-08-27 : 04:13:24

Below is an example of one of the many queries that are used.

SELECT COUNT(DISTINCT ID_tblV) AS numblostUser
FROM tblV
LEFT JOIN tblContact ON V91000 = Vclientnumber
LEFT JOIN tblReport ON RLeadID = ID_tblV
LEFT JOIN tblUsers ON RsalesID = ID_tblUsers
WHERE ID_tblV IS NOT NULL
AND Rorderstate = 'Lost Order'
AND DATEPART(yy,Rreportdate) = 2007
AND IsShowOnSalesReports > 0
AND Rreportdate >= '20070101' AND Rreportdate <= '20070331'

Field "Rreportdate" contains identical values. Is it then possible to use a clustered index on this field ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 04:44:47

Please repost query AFTER YOU HAVE PREFIXED the column names with TABLE NAME.
It is impossible for us to decide which column belongs to which table. We don't have your database at hand.

SELECT		COUNT(DISTINCT <tablenamehere>.ID_tblV) AS numblostUser
FROM		tblV
LEFT JOIN	tblContact ON <tablenamehere>.V91000 = <tablenamehere>.Vclientnumber
LEFT JOIN	tblReport ON <tablenamehere>.RLeadID = <tablenamehere>.ID_tblV
LEFT JOIN	tblUsers ON <tablenamehere>.RsalesID = <tablenamehere>.ID_tblUsers
WHERE		<tablenamehere>.ID_tblV IS NOT NULL
		AND <tablenamehere>.Rorderstate = 'Lost Order'
		AND DATEPART(yy, Rreportdate) = 2007		-- Redundant WHERE. Remove immediately
		AND <tablenamehere>.IsShowOnSalesReports > 0
		AND <tablenamehere>.Rreportdate >= '20070101' AND <tablenamehere>.Rreportdate < '20070401'

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

Gyte
Starting Member

23 Posts

Posted - 2007-08-27 : 10:43:34

Here is the rewritten query :

SELECT COUNT(DISTINCT tblV.ID_tblV) AS numblostUser
FROM tblV
LEFT JOIN tblContact ON tblContact.V91000 = tblV.Vclientnumber
LEFT JOIN tblReport ON tblReport.RLeadID = tblV.ID_tblV
LEFT JOIN tblUsers ON tblReport.RsalesID = tblUsers.ID_tblUsers
WHERE tblV.ID_tblV IS NOT NULL
AND tblReport.Rorderstate = 'Lost Order'
AND tblUsers.IsShowOnSalesReports > 0
AND tblReport.Rreportdate >= '20070101' AND tblReport.Rreportdate <= '20070331'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 10:47:42

Is tblV.ID_tblV the primary key of tblV table?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 10:50:46

[code]SELECT COUNT(*) AS NumbLostUser
FROM tblV
LEFT JOIN tblContact ON tblContact.V91000 = tblV.Vclientnumber
LEFT JOIN tblReport ON tblReport.RLeadID = tblV.ID_tblV
tblReport.Rorderstate = 'Lost Order'
AND tblReport.Rreportdate >= '20070101'
AND tblReport.Rreportdate < '20070401'
LEFT JOIN tblUsers ON tblUsers.ID_tblUsers = tblReport.RsalesID
AND tblUsers.IsShowOnSalesReports > 0
WHERE tblContact.V91000 IS NULL
AND tblReport.RLeadID IS NULL
AND tblUsers.ID_tblUsers IS NULL[/code]

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

Gyte
Starting Member

23 Posts

Posted - 2007-08-28 : 03:10:40

I haven't defined any primary keys in any table. I only make use of identity fields. Field "ID_tblV" is an identity field of table "tblV". Does making use of primary keys improve the preformance ?

I think you have made a few mistakes in your last query. I have rewritten it as follows and this query gives me the correct value.

SELECT		COUNT(*) AS NumbLostUser
FROM		tblV
LEFT JOIN	tblContact ON tblContact.V91000 = tblV.Vclientnumber
LEFT JOIN	tblReport ON tblReport.RLeadID = tblV.ID_tblV
			AND tblReport.Rorderstate = 'Lost Order'
			AND tblReport.Rreportdate >= '20070101'
			AND tblReport.Rreportdate < '20070401'
LEFT JOIN	tblUsers ON tblUsers.ID_tblUsers = tblReport.RsalesID
			AND tblUsers.IsShowOnSalesReports > 0
WHERE		tblContact.V91000 IS NOT NULL
		AND tblReport.RLeadID IS NOT NULL
		AND tblUsers.ID_tblUsers IS NOT NULL

Gyte
Starting Member

23 Posts

Posted - 2007-08-30 : 07:08:05

Peso,
I just found out that it was the GETDATE function that mostly was slowing all the queries. That is the following WHERE part :

AND DATEPART(yy,Rreportdate) = 2007

On all queries I replaced it with :

AND Rreportdate >= '20070101' AND Rreportdate <= '20071231'

Now all the queries work very fast.

Anyway thank you, Peso. You really helped me out.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 08:08:44

Yes. I wrote that in my third response.

You can make the query quicker now when you changed the "IS NULL" to "IS NOT NULL".

SELECT		COUNT(*) AS NumbLostUser
FROM		tblV
INNER JOIN	tblContact ON tblContact.V91000 = tblV.Vclientnumber
INNER JOIN	tblReport ON tblReport.RLeadID = tblV.ID_tblV
INNER JOIN	tblUsers ON tblUsers.ID_tblUsers = tblReport.RsalesID
WHERE		tblReport.Rorderstate = 'Lost Order'
		AND tblUsers.IsShowOnSalesReports > 0
		AND tblReport.Rreportdate >= '20070101'
		AND tblReport.Rreportdate < '20080101'

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 08:09:21

Did you also add an index on the Rreportdate column for table tblReport?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources