Improve performance

[Mun]

Hello,

I need experts advise...I've an ASP page which is retriving 10,000 database records and it's taking too long to execute.
how do I make it faster.
I used response.buffer=true and response.flush methods but no change.

Thanks in advance.

 

[FesterSXS]

does your SQL command only retrieve the fields that you need...

strSQL = "SELECT field1, field4 FROM myTable;"

...or are you doing it like this...

strSQL = "SELECT * FROM myTable;"

Tony

http://www.SpiraxSarco.com

 

[guitardave78]

Can i ask why you need 10,000 records, is there no way of filtering some of the records out?

 

[Mun]

Yes, I've only selected fields in my query. My sql stmt has filters and 10K records are the final result after filters.

 

[Tarwn]

You could look into paging through those results if your looking to shorten each page and don't mind spreading em across multiple pages.

Also, response.Flush may actually slow your system down if you end up sending a bunch of unfilled packets. I think letting the buffer take care of everything by itself (without using Flush) may speed it up a little.

I hope your not looping through your recordset, but in the event that you are, consider using the GetRows method f the recordset to get the data back as an array, then loop through the array. you should see a 7x to 12x efficiency increase in your loop.

'pretend our RecordSet is MyRS
Dim arr_results
arr_results = MyRS.GetRows()
'loop throught records - second index is record index, first is field index
For i = 0 To UBound(arr_results,2)
   'do whatever with field
Next

Try using the timer method/object/thing at the top of your page and the bottom, the print the difference to the screen. I am willing to bet that more than half of the time is not processing the page, but transmitting it to the client and then waiting will the client laboriously displays it:

<%
Option Explicit

Dim start_time
start_time = timer
'lots of code
'lots more code
Response.Write "Execution Time: " & timer - start_time & "s"
%>

Also, when dealing with that many records, if they are coming from multiple tables try to use INNER JOINs rather than cross joins (or commas) in your select statement, this will speed up the database execution time:

"SELECT a, b, c, d FROM Table1, Table2, Table3...etc" <-- Bad
"SELECT a, b, c, d FROM ((Table1 INNER JOIN Table2 ON Table1.a = Table2.b) INNER JOIN Table3...etc" <-- good

The reason for making this switch is because the database akes a temporary table of everything in the FROM portion of the clause before applying the WHERE portion. When crossing table it will make a temporary table of literally every combination of the records from each table, then try to filter that monstrosity. Wen using an INNER JOIN/OUTER JOIN/etc the database uses the built-in ON conditions while building the temporary table, thus having a much smaller (and more relevant) set of records to filter with the WHERE arguments.


There are also FAQs and at least one huge thread on eficiency. Try the Search tab above and you'll find all kinds of good stuff.

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[shaddow]

You definately have Access Database there. You should go to SQL server or at least MSDE 2000 which is free and it's an SQL Server engine that has concurent clients limith access.
You'll notice the speed there.

Other wise my opinion is to make an external component which returs data to you. That component make shure is using DAO objects, or at least use ODBC drivers with ADO.

________
George, M
Searches(faq333-4906),Carts(faq333-4911)