ASP database search with large result set

it takes time: is your query not optimized or you are trying to display 10M records on the page?

HainKurt - Display 200K records. Query is ok, but there are some nested loops and such.

padas - Wouldn't this also take to generate making the request hang?

with a lot of records and writing to the browser, try using getrows() http://www.w3schools.com/ado/met_rs_getrows.asp   it takes a little longer to code if you are not used to using arrays, but the end result is much faster.

If you do have 10,000 records and excel is ok to open the file, use a csv file instead of a table that gets converted to excel.  That's a lot of extra <tr><td></td><td></td><td></td><td></td></tr>' that could be just 4 commas then a vbcrlf.  That will save you about 36 characters or 360,000 over 10,000 records.

I do what you are trying to do a lot.  Writing directly to the server goes very quickly.   There is no way you are going to write 200,000 records to the browser through excel stream.

When you say "use a csv file instead of a table that gets converted to excel" do you just mean create a comma delimited text file (with a .csv extension)? How do you get around commas in the resultset?

You can use anything for a delimiter.  Typically, you will have quotes around the field too.

response.write( "&quot;" &rsName& "&quot;," "&quot;" &rsAddress&  "&quot;," .....  &vbcrlf)

to end up with below results. Notice the comma after LastName will be part of the field "LastName, FirstName"

"LastName, FirstName","address", "city","st","zip"
"LastName, FirstName","address", "city","st","zip"
"LastName, FirstName","address", "city","st","zip"

You can use a tild or anything for the field separator.  But then the person using excel has to know to change the default comma to a tild or whatever you use.    With 200,000 records you will need to change your time out a little longer.

Another trick I use is to add the date and time to the download filename to make sure multiple people do not over write the file.   filename_20120215_1314.28.csv  or I will name the file with the logged in usenrmae filename_username.csv.

The key is to have a way to easily remove those saved files.

Padas, what do you display the user after they click the button to generate the report during the server processing time?

You can submit it via ajax and show a little animation.  Then the link to the download and a form button to remove the file.  jquery post works nice.  

On that page, I also show the current files that should be deleted http://www.plus2net.com/asp-tutorial/file-list.php if they don't want them deleted automatically.  Each file has a link to download and a delete button.

Thanks. ajax is beyond my ability, but thanks for the idea.

After you get that done, try playing with some sample ajax code.

http://www.w3schools.com/ajax/ajax_xmlhttprequest_send.asp

or

http://api.jquery.com/jQuery.post/

The learning curve is short.  Just take it in steps.

Dumb question...

When I do this:
testfile.WriteLine "Shipment Date &quot;, &quot; Carrier - Class&quot;, &quot;Tracking Number&quot;" &vbcrlf

The result is:
Shipment Date &quot;, &quot; Carrier - Class&quot;, &quot;Tracking Number&quot;

Why doesn't &quot; get replaced with "?

Oh, this instead  chr(34)

If you are just testing with text

testfile.WriteLine "Shipment Date," & chr(34) & "Carrier - Class," & chr(34) &"Tracking Number" &vbcrlf

If you using real variables Shipment_Date, Carrier_Class, Tracking_Number

testfile.WriteLine chr(34) &  Shipment_Date & chr(34) &"," & chr(34)  & Carrier_Class & chr(34) &"," & chr(34) & Tracking_Number  &vbcrlf

or

testfile.WriteLine """ &  Shipment_Date & chr(34) &"," & chr(34)  & Carrier_Class & chr(34) &"," & chr(34) & Tracking_Number  &vbcrlf

THX