Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - Make faster database access
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Make faster database access
 Post Reply Post Reply Page  12>
Author
  Topic Search Topic Search  Topic Options Topic Options
riya_singtel View Drop Down
Newbie
Newbie
Avatar

Joined: 14 March 2005
Location: India
Status: Offline
Points: 30 		Post Options Post Options   Thanks (0) Thanks(0)   Quote riya_singtel Quote  Post ReplyReply Direct Link To This Post Topic: Make faster database access
    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!!!



Edited by riya_singtel - 12 April 2005 at 5:13am
Back to Top
bootcom View Drop Down
Senior Member
Senior Member
Avatar

Joined: 26 February 2005
Location: United Kingdom
Status: Offline
Points: 259 		Post Options Post Options   Thanks (0) Thanks(0)   Quote bootcom Quote  Post ReplyReply Direct Link To This Post 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 Smile.
 
It seems that you are using a lot of recordsets if Im reading that correctly.
 
Quote
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
 
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.
Back to Top
Lofty View Drop Down
Newbie
Newbie
Avatar

Joined: 03 April 2005
Status: Offline
Points: 19 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Lofty Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
bootcom View Drop Down
Senior Member
Senior Member
Avatar

Joined: 26 February 2005
Location: United Kingdom
Status: Offline
Points: 259 		Post Options Post Options   Thanks (0) Thanks(0)   Quote bootcom Quote  Post ReplyReply Direct Link To This Post 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 Confused. 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 Smile
Back to Top
riya_singtel View Drop Down
Newbie
Newbie
Avatar

Joined: 14 March 2005
Location: India
Status: Offline
Points: 30 		Post Options Post Options   Thanks (0) Thanks(0)   Quote riya_singtel Quote  Post ReplyReply Direct Link To This Post 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


Edited by riya_singtel - 12 April 2005 at 9:58pm
Back to Top
bootcom View Drop Down
Senior Member
Senior Member
Avatar

Joined: 26 February 2005
Location: United Kingdom
Status: Offline
Points: 259 		Post Options Post Options   Thanks (0) Thanks(0)   Quote bootcom Quote  Post ReplyReply Direct Link To This Post 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 Tongue.
 
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 Smile


Edited by bootcom - 12 April 2005 at 10:25pm
Back to Top
riya_singtel View Drop Down
Newbie
Newbie
Avatar

Joined: 14 March 2005
Location: India
Status: Offline
Points: 30 		Post Options Post Options   Thanks (0) Thanks(0)   Quote riya_singtel Quote  Post ReplyReply Direct Link To This Post Posted: 12 April 2005 at 10:45pm

Thanks a lot,

I will try and let u know the results..
 
 
Back to Top
riya_singtel View Drop Down
Newbie
Newbie
Avatar

Joined: 14 March 2005
Location: India
Status: Offline
Points: 30 		Post Options Post Options   Thanks (0) Thanks(0)   Quote riya_singtel Quote  Post ReplyReply Direct Link To This Post 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..
Back to Top
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.