Classic ASP Discussion - Make faster database access

Print Page | Close Window

Make faster database access

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=14652
Printed Date: 30 March 2026 at 10:06am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Make faster database access

Posted By: riya_singtel
Subject: Make faster database access
Date Posted: 12 April 2005 at 5:10am

As the database size increases so does the time to get records from it.

To test this, I added lots of data to my access db so that its size was around 900 kb. I have to generate some reports and when i tested some of them took greater than 3 mins to be loaded.

My connection string is:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString= "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
            & "Password=sam;" _
            & "User ID=sam;" _
            & "DATA SOURCE=" & dbPath _
            & "; Jet OLEDB:System Database=secw_Path;" _
            & "Persist Security Info=True;"

and my recordset is as:

Set obj = Server.CreateObject("ADODB.Recordset")

obj.Open sql1,Conn

I agree that i am reading lot of data about 200 records from a table and about 30 more frm another table for each of these 200; in a nested loop. The sql statements are simple SELECT statements using only simple WHERE and ORDER BY statements

Is there a way that I can improve the efficiency of the script so that it is generated faster.

I am not sure of the dynaset object??

Can somebody please comment!!!

Replies:

Posted By: bootcom
Date Posted: 12 April 2005 at 10:32am

Can you paste the whole code in so that we may get a better look at what your doing mate

It seems that you are using a lot of recordsets if Im reading that correctly.

Better take a look at the code, then I can give an informed suggestion. No way should a script like this take 3 minutes to load.

Posted By: Lofty
Date Posted: 12 April 2005 at 6:16pm

900 kb is a tiny database.

access should be able to sort through 5000 records in just a few seconds. once you get beyond 5000 is where access starts to slow down. becuase access is just a flat file. and has to pull all the records from a table before it sorts and filters.

but certainly a 900kb db is tiny small and theres no way on earth it should be taking ay 3 mins to pull the records.

even a very badly scripted piece of code would give much better performance than that.

unless ya meant 900mb

Posted By: bootcom
Date Posted: 12 April 2005 at 6:35pm

Agreed Lofty. My current WWF db is 4.3mb and thats because it's got tonnes of modifications.

What I think he could be doing is possibly moving through each record in the recordset then creating a new recordset from that initial record, like this ...



do while not rsCommon.eof
 
response.write(rsCommon("field_header"))
 
' Then do another SQL string and execute and loop again
 
rsCommon.movenext
loop

Thats the only way I could think that there would be that kind of lag as the database is being accessed loads

. As I say though if we see how it is currently coded, we can suggest any changes and see if we can help you make the script run faster

Posted By: riya_singtel
Date Posted: 12 April 2005 at 9:56pm

ok,

the structure of my script is:

sql1="Select [Name] FROM Table1 WHERE [Group]='" & Request.form("txt") & "' "

Conn.Open

obj1.Open sql1,Conn

Do While NOT obj1.EOF

sql2="Select * FROM Table2 WHERE [Name]="' & obj1("Name") & "' "

obj2.Open sql2,Conn

Do While NOT obj2.EOF

sql3="Select * FROM Table3 WHERE [Date]="'& obj2("Date") &"' "

obj3.Open sql2,Conn

obj2.MoveNext

Loop

obj1.MoveNext

Loop

Obj1.Close

obj2.Close

obj3.Close

Conn.Close

In the worst case: 200 records are selected from Table1 and for each of these 200 records 25 records are selected from Table2. For each of these 25 records of Table2 about 4 records are selected from Table3.

Can I somehow change the structure of the code so that it executes faster? Is there some other way in which I should be extracting the records? I hope u got what I am trying to do in my script.

Thanks

Posted By: bootcom
Date Posted: 12 April 2005 at 10:19pm

Yeah to open a recordset within a recordset is never a good idea when your looping, but my theory was correct, thats bad code. Looking at it I have no idea why you're using the third recordset because your doing nothing with it, you're just opening and closing unless you're only expecting 1 record for it, thats a bit of a mellon scratcher to quote ned flanders

Here is the correct SQL syntax in doing it like this:


SQL1 = "SELECT table1.name, table2.name FROM (table1 LEFT JOIN " _
 
& "table2 ON table1.name = table2.name) LEFT JOIN table3 ON " _
 
& "table3.date = table2.date WHERE table1.group = '" & Request.form("txt") & "'"

There ! We pulled it all out with just 1 SQL statement, now for the rest ....


<%
Dim tbl1Name
Dim emptyVar
 
emptyVar = "" 


Do Until RS.EOF
    tbl1Name= "" ' Variable to store your table1.name value
  
  If tbl1Name <> emptyVar Then

        If priorTeam <> "" Then

            Response.Write "</UL>" 

        End If

        Response.Write "Table 1 Name: " & tbl1Name & "<UL>"

        emptyVar = tbl1Name
 

    End If

 
    'Loop through all of the table2 names whilst under our the table 1 name of the um ... same name :-D
 
    Response.Write "<LI>" & RS("table2.name")
 
    ' As you only showed that you have table 3 date not looped Im not gonna loop either. 
' Im just going to put it in there before the loop ends
 
    response.write "<LI>" & rs("date")

    RS.MoveNext

Loop

Response.Write "</UL>" ' clean up the tags!
%>

I've not even tested this but thats roughly how I would show all the records, I've never pulled records out of 3 tables at the same time so never had much call for a script like this, it's an extention of the one I use from 2 tables but it looks sound.

As you can see 1 SQL string, 1 recordset, no mess, quick loading. Have fun

Posted By: riya_singtel
Date Posted: 12 April 2005 at 10:45pm

Thanks a lot,

I will try and let u know the results..

Posted By: riya_singtel
Date Posted: 13 April 2005 at 10:51pm

The problem is that it is difficult for me to join the queries as u suggested because when i listed out my code I had written simple SQL statements only to indicate the structure of my code. However each of the SQL statements are instead queries selecting records from a number of tables. So if i try to join them it gets very complicated and does not display the results as i need.i am trying to do away with as many queries as possible but is there something else also that i should take care of.

Please suggest smth..

Posted By: bootcom
Date Posted: 13 April 2005 at 10:55pm

OK. Well if you post the actual SQL statements your using and not the simple ones you showed initially, I'm sure we can get our heads together and come up with some suggestions.

Posted By: riya_singtel
Date Posted: 25 April 2005 at 1:46am

hey thanks a lot...I did a li'l bit change in my sql queries and got it done. I used one sql query extracting all records at once and then using the ones needed. The time is now reduced to 3 secs at the most.

Could not post the exact date due to security reasons.Thanks "bootcom" for ur help.