Professional Web Applications Themes

Faster way to get record sets - ASP Database

Hi, what is the fastest way to get record sets?? Using ASP to access an MS Access database it takes AGES to open the following two recordsets using this code - a couple of seconds each sometimes!!! The subcats table has 96 entries and the products table around 1,500. Is there a faster way I can access them?! (Preferably while still using MS Access) set con=Server.CreateObject("ADODB.Connection") ConString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="&server.mappath("/db/db127/store.mdb") con.open(ConString) Set subcats = Server.CreateObject( "ADODB.Recordset" ) subcats.ActiveConnection = con sqlString = "SELECT * FROM subcats Where SubCatOf = " & ShowSubcats & " Order By Position" subcats.Open ...

  1. #1

    Default Faster way to get record sets

    Hi, what is the fastest way to get record sets?? Using ASP to access an MS
    Access database it takes AGES to open the following two recordsets using
    this code - a couple of seconds each sometimes!!! The subcats table has 96
    entries and the products table around 1,500. Is there a faster way I can
    access them?! (Preferably while still using MS Access)


    set con=Server.CreateObject("ADODB.Connection")

    ConString = "DRIVER={Microsoft Access Driver
    (*.mdb)};DBQ="&server.mappath("/db/db127/store.mdb")
    con.open(ConString)

    Set subcats = Server.CreateObject( "ADODB.Recordset" )
    subcats.ActiveConnection = con
    sqlString = "SELECT * FROM subcats Where SubCatOf = " & ShowSubcats & "
    Order By Position"
    subcats.Open sqlString

    Set products = Server.CreateObject( "ADODB.Recordset" )
    products.ActiveConnection = con
    sqlString = "SELECT * FROM products WHERE Cat=" & cat & " Order By Name"
    products.Open sqlString




    Many thanks,
    Greg


    Greg Guest

  2. #2

    Default Re: Faster way to get record sets

    Greg wrote: 

    I don't see any timing statements in your code snippet, so I'm wondering how
    you came to the conclusion that your method of opening the recordsets was
    causing the slowdown. In the future, instead of guessing, do something like
    this:

    dim t
    t=now()
    response.write "Process starting<BR>"
    <snip of code used to open recordset>
    response.write "Opening first recordset took " & _
    DateDiff("s", t, now()) & " seconds<BR>"
    <snip of code used to open recordset>
    response.write "Opening second recordset took " & _
    DateDiff("s", t, now()) & " seconds. Starting to process records<BR>"
    <snip of recordset-processing code>
    response.write "Processing recordsets took " & _
    DateDiff("s", t, now()) & " seconds.<BR>"


    Keep reading for more comments:
     
     

    Stop using ODBC.

    ConString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source="&server.mappath("/db/db127/store.mdb")
     

    This should be:

    set subcats.ActiveConnection = con

    But this line isn't needed. You can specify the connection in the recordset
    Open statement

     

    Use

    Response.Write sqlString

    during this debugging phase. It will come in handy. You can comment it out
    when all is working correctly.
     

    Like this:
    subcats.Open sqlString,con,,,1

    The "1" argument tells ADO that your data source is a sql statement
    contained in a string (adCmdText). Don't make ADO guess about the command
    type.

    <snip>

    Some people will tell you to use

    set subcats=con.Execute(sqlString,,1)

    instead of

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

    subcats.Open sqlString,con,,,1

    but both these techniques really do the same thing. One just has fewer lines
    of code.

    The method of opening the recordset only has a little to do with the speed
    of retrieving the data. You should open your database in Access and use the
    query builder to run these queries (create a query in design view and switch
    to SQL View without choosing a table. Copy and paste the sql statement
    resulting from the Response.Write statement above and run it).

    Are they as slow when you run them in Access? If they are, one reason could
    be the lack of an index on the fields being used in your WHERE and ORDER BY
    clauses. This is why I always advocate designing and testing your queries in
    your database's native query execution environment before attempting to run
    them from external programs. it allows you to verify that any problems seen
    when running them in the external program are due to the external program
    rather than the query itself. In this case, when you see that the queries
    run slowly, you can try adding indexes to the fields (Access also has a
    query optimizer tool that you can use)

    If your queries run quickly in Access, then I suspect that what's taking the
    time on your asp page is what you're doing with the recordsets AFTER they
    are opened. Looping through recordsets can be very slow. See
    http://www.aspfaq.com/show.asp?id=2467 for ideas about quickly handling the
    data in your recordsets.

    I also question why you are using two recordsets. Can you use an inner join
    to retrieve the data yu require from both tables in a single query? Since I
    don't know how the tables are related (if at all), I can't answer that
    question for you. But given the names of the tables and fields, I suspect
    these tablse are related and that a single query can be used to retrieve the
    records you need. The Access Query Builder can really help with creating
    queries involving multiple tables.

    And for a more advanced, but simpler, way to run your queries, look into
    using saved parameter queries instead of dynamic sql:

    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.ph x.gbl

    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=ukS%246S%247CHA.2464%40TK2MSFTNGP1 1.phx.gbl

    http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx.gb l&oe=UTF-8&output=gplain

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  3. #3

    Default Re: Faster way to get record sets

    For starters, stop using SELECT *. And if you're nesting and looping
    recordsets, consider an inner join instead, and performing loops using
    GetRows() or returning a string using GetString(). You didn't show the rest
    of your code, I am going to guess that the delay is in your choice of
    looping/display techniques, and not how "to get record sets" ... in other
    words, the code that you chose to show us is not the code we need to see if
    you really want help improving the speed.

    In the meantime, ome general tips here:

    http://www.aspfaq.com/2241
    http://www.aspfaq.com/2424#db
    http://www.aspfaq.com/2467
    http://www.aspfaq.com/2096

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Greg" <com> wrote in message
    news:phx.gbl... 


    Aaron Guest

  4. #4

    Default Re: Faster way to get record sets

    Wow, thanks for all that advice!

    I know it's getting the record sets that is making it slow because i put

    Response.Write(Now())

    all over the place!

    I've tried using the jet connection string you mentioned:

    conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=C:\inetpub\clients\awt326\db\db127\store.md b"
    set con = Server.CreateObject("ADODB.Connection")
    con.open(conString)


    However, I keep getting an unspecified error on the line "set cat =
    con.Execute(sqlString,,1)" in the following:

    Set cat = Server.CreateObject( "ADODB.Recordset" )
    sqlString = "SELECT * FROM categories Order By Position"
    set cat = con.Execute(sqlString,,1)



    Whereas this works without a problem?!?!

    Set welcome = Server.CreateObject( "ADODB.Recordset" )
    sqlString = "SELECT * FROM welcome WHERE Key=1"
    set welcome = con.Execute(sqlString,,1)



    Is it me or are they the same thing, why is one not working?! I've tried
    changing the order etc. nothing!

    Greg.


    Greg Guest

  5. #5

    Default Re: Faster way to get record sets

    Greg wrote: 
    This line is not needed, but it should not be causing a problem.
     

    Does the query run in Access using the Query Builder? If so, there may be a
    reserved keyword in your sql statement. ... yes, "Position" is a reserved
    keyword (http://www.aspfaq.com/show.asp?id=2080). If you cannot change the
    name of that field for some reason, then you are going to have to remember
    to surround it with brackets [] whenever you use it in a query run via ADO.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  6. #6

    Default Re: Faster way to get record sets

    Greg wrote: 
    <snip> 

    It could be many things, including network traffic, too much activity in
    your database, etc.

    I still have to question why you are opening all these recordsets. Aren't
    these tables related? You should be able to combine at least some of the
    recordsets into a single query using appropriate joins.

    The other problem is that you are still using selstar (Select *) instead of
    the more efficient practice of explicitly specifying the clolumns you wish
    to retrieve. When you use selstar ADO has to make an extra trip to the
    database to resolve the * into an actual list of field names. Don't make it
    to that. Also, take this opportunity to evaluate whether you REALLY need to
    retrieve every single field in every one of those tables. The more you cut
    down network traffic, the better off you will be, especially if that is the
    bottleneck.


    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  7. #7

    Default Re: Faster way to get record sets

    Notes inline. See especially the notes about CheckFileExists() ...

    Greg wrote: 

    First of all. When I run the page I get 0 for all time values: IOW it
    runs fairly quickly. Secondly, the output page itself does not match the
    ASP page you sent: in particular the actual page has "No Picture
    Available" in it but your ASP page does not.

    Also since it is a ually-oriented site, I have to wonder if this is
    this not a clever bit of SPAM?-))

    When you say [/ref]

    are you running it on the same server from which the ASP page gets it?
    Or do you mean that the query runs fast when running Access on your own
    workstation?

    Has the database been compacted/repaired recently?

    Do the database server and web server (if separate) have the latest MDAC
    installed? See
    http://msdn.microsoft.com/data/downloads/updates/default.aspx#MDACDownloads
    for updates.
     

    The "Select * " is slow because it must make additional inquiries to the
    database server to get a description of the fields in the relevant
    tables first. Only then can it turn around and request the various
    fields' data. But I am not convinced that this is your main problem.

    The recordset GetString() method can save some time here, but save this
    for later. I think your problems lie elsewhere mostly.
     

    Get rid of the CheckFileExists() tests - calls to the FileSystemObject
    can be costly and in this case may eat up time unnecessarily. As a test,
    just stub the CheckFileExists() method so it returns TRUE and see how
    much quicker it runs. If a picture is not available, then you have
    several alternatives including:
    - provide a default picture that says "image not available" or such,
    - put a flag in the database that tells you no picture is available or
    necessary.

    Put a
    Response.Buffer = TRUE
    at the top of your code to ensure buffering is enabled.

    The code creates a number of recordset objects but only one is needed.
    Each Server.CreateObject( "ADODB.Recordset" ) call after the first
    wastes time. Instead re-use the same recordset object by replacing
    "cat", "subcats", "welcome", "featured", "topten" with a single
    recordset object "rs".

    "catagories" is misspelled - it should be "categories" on the page.

    What's your server configuration? You may have a network problem or a
    separate and slow database server.

    An observation about your debug code, e.g.:
    response.write "Opened featured RecordSet at " & _
    DateDiff("s", t, now()) & " seconds<BR>"
    prints out the difference between a start time t and now() at various
    steps. So it represents the aculated (total) time of script
    execution, not the execution time of the various script segments.

    Good Luck,
    Michael D. Kersey
    Michael Guest

  8. #8

    Default Re: Faster way to get record sets

    Wow, thanks for all that. I'll have a bodge around.

    I have now decided that although most of the advice you have given me does
    improve the loading speed, the timing statements definately show me that the
    bulk of the delay lies solely in certain segements that open recordsets. The
    rest of the page always loads (relatively) quickly, although it will
    probably be a lot quicker once I implement the latest suggestions.

    Do you have any ideas why this section alone sometimes takes up to 12
    seconds to complete?

    conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=C:\inetpub\clients\awt326\db\db127\store.md b"
    set con = Server.CreateObject("ADODB.Connection")
    con.open(conString)

    Thanks SO MUCH for all your help, it really is appreciated, and by the way,
    this isn't spam - hehe! :-D
    I'm putting the site together for a friend, it is all legal and I would like
    to apologise to anyone who found it offensive.

    Kind regards,
    Greg


    Greg Guest

  9. #9

    Default Re: Faster way to get record sets

    Greg wrote: 

    That shouldn't happen. The IIS server, the database server or the
    network must be very busy or SNAFU'd for that to happen.

    BTW is the database on the same server as your IIS web server?

    Make sure all systems have the latest MDAC installed.

    Just for fun try an ODBC connection. http://www.connectionstrings.com/
    has sample ODBC (and other) connection strings for SQL Server and
    Microsoft Access. Make sure ODBC pooling is enabled for the test.

    Good Luck,
    Michael D. Kersey
    Michael Guest

Similar Threads

  1. connections work but record sets do not
    By pkouvelis in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: August 9th, 06:23 PM
  2. Why did Photoshop CS quit suddenly in the middle of making sets? Action sets
    By blackxacto@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 1
    Last Post: May 4th, 04:01 AM
  3. CS quit suddenly in the middle of making Action sets Action sets,
    By blackxacto@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 3
    Last Post: May 2nd, 06:06 AM
  4. DuplicateMovieClip goes faster and faster
    By sylvie in forum Macromedia Flash
    Replies: 0
    Last Post: September 30th, 06:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139