ASP Timeout expired problem 1

[Elcin]

Hello,
I am using ASP with a SQL 7.0 database and ADODB connection string. The queries are simple but takes enormous amount of time to retrieve the dataset and most of the time gives a Timeout expired error message.
Using
commandtimeout = 0 'seconds
Server.ScriptTimeOut=240
is also not helping...
The database has around 123468 records.
An example of the query is as follows:
sql = "SELECT .,.,.,.,.,FROM a,b,c where a.SM_DoTM=1 and a.SM_AccNumber=b.SM_AccNumber and a.SM_TMDiag=c.D3_DiagCode and b.SM_Received between '"& Session("SDate&quot&"' and '"& Session("EDate&quot&"' and TblSampMain.SM_HospID ="& Session("Hospital&quot&" and a.SM_TMRndx IN ("&Session("Stat0&quot&","&Session("Stat1&quot&","&Session("Stat2&quot&","&Session("Stat3&quot&&quot and b.SM_PatType IN("&Session("Udefine&quot&","&Session("Screen&quot&","&Session("Sick&quot&","&Session("Special&quot&&quot" &" order by b.SM_AccNumber"
Set rs = Server.CreateObject("ADODB.Recordset&quot
rs.Open sql_h, conn, 3, 3
If (rs.eof) and (rs.bof) then
response.redirect "error_rpt2.asp"
Else
rs.movefirst

Any suggestions....

 

[Renzy]

Hi Elcin,

Your records is really large enough, and if you will display it at one html page clients can't wait that long to load 123,468 records. Why not limit your display to 100 records per page and have a link to proceed to the next page.

Actually m only suggesting 'coz based from experience m not willing to wait for 15 minutes just for the page to load...and even you will extremely increase your ScriptTimeOut still it wont solve that problem 'coz every user have different internet connection and others are still using a dial-up connection for 56kbps.

Regards,

 

[SrinuReddy]

Above Idea is good. also why not use stored proc to retrive the data from the database, instead of writing the query in ASP? Write the stored proc and pass arguments to it and get the recordset. Using stored proc will reduce the execution time as they are pre complied statements.

Hope this helps,

srinu...

 

[Elcin]

Thank you so much to those who replied, for the information and the tips. I already applied paging and will also write a stored procedure for this.

Thanks again.