Returning many records Timeout

[TMRO]

Hi guys,

I trying to return a lot of records (the table holds 30,000,000 records) from the MS SQL. The lines to call the stored procedure are as follow:

set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 0
conn.CommandTimeout = 0
set objrs = Server.CreateObject("ADODB.Recordset")
conn.CursorLocation = 3
conn.Open "ConnectionString"

Set oCom = Server.CreateObject("ADODB.Command")
with oCom
.activeconnection = conn
.commandtext = "sp_BringSearchResultsDate"
.commandtype = 4
.Parameters.Append .CreateParameter("@SD", 7, 1, 1, trim(Request("start")))
.Parameters.Append .CreateParameter("@ED", 7, 1, 1, trim(Request("end")))
end with
set objrs = oCom.Execute
Set conn = nothing
Set oCom = nothing

When I query the table I receive Connection.Timeout

Is there a way not to get this error amd still return all the records?

Thx,
TMRO

 

[DotNetGnat]

i would suggest paging your recordset...

-DNG

 

[TMRO]

I put this 2 last lines after
set objrs = oCom.Execute

objrs.pagesize = 100 'nr.of records on page
objrs.absolutepage = 1

The error that I'm getting is on line "set objrs = oCom.execute"

with the message Timeout Expired

Any ideas?
Thank you,
TMRO

 

[Sheco]

I'll go out on a limb and say that nobody wants to look at thirty million records in a browser.

Why not instead change the stored procedure to limit the number or rows returned?

 

[DotNetGnat]

Yes i agree with Sheco...put some kind of search criteria and filter your recordset...thirty million records is waaaaaaaaaay toooooooooo muccccccccccccccch

-DNG

 

[TMRO]

There are at least 20000 records per day inserted.
And they want to use wildcards.

I limited to return 50000 by choosing select top 50000, but the error I get is before i can get something from the recordset. As I said it looks like dying when I execute the command object.
set objrs = ocom.execute

 

[DotNetGnat]

do you mind posting your SP??

-DNG

 

[Sheco]

Is this a page for humans to examine? ... or a web-enabled method for generating a text file that is parsed by some other software?

 

[TMRO]

Here is the SP

SELECT top 50000 PCBID, REEL.RID, LOT, PN, TRACE_LOG_BAK.TIMESTAMP,
MPROG, LOC, TRACE_STATION
FROM TRACE_LOG_BAK RIGHT JOIN (PN INNER JOIN REEL ON (PN.SPN = REEL.SPN) AND (PN.COMPANY = REEL.COMPANY))
ON TRACE_LOG_BAK.RID = REEL.RID
WHERE TRACE_LOG_BAK.TIMESTAMP > @start and TRACE_LOG_BAK.TIMESTAMP < @end

Is for humans to examine like Sheco is saying.
The problem is that the same query in Access is running witout problems. Takes a little bit of time, but at least is not crashing.

 

[Tarwn]

There are at least 20000 records per day inserted.
And they want to use wildcards.

Either:
1) Build your SQL statement from scratch so you can use their search terms in the WHERE portion
or
2) Build you stored procecure to accept arguments for filtering


Pulling all of the records back and then filtering them is a recipe for disaster. Even if you get past this connectiopn timeout you will then start running into page load timeouts. Once you get past that your sysadmin will start wondering why the computer crunches to a halt every few minutes when someone is hitting this page.

Let the database do the filtering, thats what it is built for.

-T

 

[TMRO]

If I put top 5000 instead of 50000 is returning records.
Strange. Should it such a big difference?

 

[damber]

TMRO,

I can't stress enough that you need to LISTEN to what people are saying here.

50,000 rows of data on a page is FAR TOO MUCH for any user. Even 5,000 is too much - you would never sit and read through 5,000 lines of trace information in one sitting would you ?

First, listen to what has been said and add the appropriate filters in your SQL Statement so the db server can do its job - you should aim to have nearer the 1,000 mark to be an effective filter.

Second, learn about paging recordsets - get the database server to serve you 100 at a time, with the option to look at 1-100 on page 1, 101-200 on page 2, etc etc. This means 10 pages would give you 1000 rows of information (but I bet the user wont go passed page 4/5 very often).

e.g.:

http://4guysfromrolla.com/webtech/062899-1.shtml

http://www.aspfaq.com/show.asp?id=2120

A smile is worth a thousand kind words. So smile, it's easy!

 

[onpnt]

If I put top 5000 instead of 50000 is returning records.

*sigh* if you make $5,000 annually verses $50,000 is that a big difference?

Sorry, but those two numbers are not even comparable in size. One is VERY large and one is NOT very large. And to through them in a script is astronomically different in terms of efficiency



General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[Sheco]

One order of magnitude.