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

Re: Faster way to get record sets

Greg wrote:
> 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)
>

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
"

response.write "Opening first recordset took " & _
DateDiff("s", t, now()) & " seconds
"

response.write "Opening second recordset took " & _
DateDiff("s", t, now()) & " seconds. Starting to process records
"

response.write "Processing recordsets took " & _
DateDiff("s", t, now()) & " seconds.
"


Keep reading for more comments:

>
> set con=Server.CreateObject("ADODB.Connection")
>
> ConString = "DRIVER={Microsoft Access Driver
> (*.mdb)};DBQ="&server.mappath("/db/db127/store.mdb")

> con.open(ConString)

Stop using ODBC.

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

>
> Set subcats = Server.CreateObject( "ADODB.Recordset" )
> subcats.ActiveConnection = con

This should be:

set subcats.ActiveConnection = con

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


> sqlString = "SELECT * FROM subcats Where SubCatOf = " & ShowSubcats &
> " Order By Position"

Use

Response.Write sqlString

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

> subcats.Open sqlString

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.



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&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

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

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

http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&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"

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" wrote in message
news:usc5G1TwEHA.4040@TK2MSFTNGP11.phx.gbl...
> 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
>
>

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.mdb"
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.

Re: Faster way to get record sets

Greg wrote:
> 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.mdb"
> 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" )
This line is not needed, but it should not be causing a problem.

> sqlString = "SELECT * FROM categories Order By Position"
> set cat = con.Execute(sqlString,,1)
>

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.

Re: Faster way to get record sets

Greg wrote:
> That's great, thanks for that - no errors any more :)
>
> It's still retrieving recordsets REALLY slowly and it's doing my nut
> in!
>
> I've attached all the code to this post in the hope that someone
> might find a problem with it. Because I'm close to giving up! The
> queries run really quick in Access, and my only idea now is that it's
> got something to do with the server I'm running it on - which I don't
> have access to.
>
> The last time I loaded the page it took one second to connect to the
> database with this:
> conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\inetpub\clients\awt326\db\db127\store.mdb"
> set con = Server.CreateObject("ADODB.Connection")
> con.open(conString)
>
> It took another second to open the cat recordset using:
> sqlString = "SELECT * FROM [categories] Order By [Position]"
> set cat = con.Execute(sqlString,,1)
>

>

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"

Re: Faster way to get record sets

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

Greg wrote:
> That's great, thanks for that - no errors any more :)
>
> It's still retrieving recordsets REALLY slowly and it's doing my nut in!
>
> I've attached all the code to this post in the hope that someone might find
> a problem with it. Because I'm close to giving up! The queries run really
> quick in Access, and my only idea now is that it's got something to do with
> the server I'm running it on - which I don't have access to.

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 sexually-oriented site, I have to wonder if this is
this not a clever bit of SPAM?-))

When you say
> The queries run really
>> quick in Access,

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.asp x#MDACDownloads
for updates.

> The last time I loaded the page it took one second to connect to the
> database with this:
> conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\inetpub\clients\awt326\db\db127\store.mdb"
> set con = Server.CreateObject("ADODB.Connection")
> con.open(conString)
>
> It took another second to open the cat recordset using:
> sqlString = "SELECT * FROM [categories] Order By [Position]"
> set cat = con.Execute(sqlString,,1)
>
> It took a further second to open the welcome recordset using:
> sqlString = "SELECT * FROM [welcome] WHERE [Key] = 1"
> set welcome = con.Execute(sqlString,,1)
>
> Then another second for this one:
> sqlString = "SELECT * FROM [featured] Order By [Key]"
> set featured = con.Execute(sqlString,,1)
>
> And another second for this one:
> sqlString = "SELECT * FROM [topten] Order By [Position]"
> set topten = con.Execute(sqlString,,1)
>
> So I'm stuck! All these queries run really quickly when I run them in
> access! I don't get it! It's happening on all the pages of the site, not
> just this one, everytime it gets a recordset it either does it really quick,
> or really slowly. I'm confused to the max!
>
> Another killer was that it took 2 seconds to actually write the featured
> items to the screen! So I think this section might need optimising a
> little - though at the moment I can't think of a better way of doing it! I'm
> talking about lines 234 to 368.

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.

> All together the entire page took 7 seconds to load - that's just not
> natural is it?! I can't be doing anything else wrong can I?! After
> implementing everyones ideas I've got the loading time for the whole page
> from 12 seconds down to 7 :-D
>
> You can see the page "in action" at this address www.jmswebdesigns.com/127
> and the source is attached to this post.
>
> I don't expect you to help me any further because you've already been such a
> great help - but if you have any quick ideas I'd be VERY grateful to hear
> them, because I'm really stressed out now! :)
>
> Kind regards,
> Greg

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
"
prints out the difference between a start time t and now() at various
steps. So it represents the accumulated (total) time of script
execution, not the execution time of the various script segments.

Good Luck,
Michael D. Kersey

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.mdb"
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

Re: Faster way to get record sets

Greg wrote:
> 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.mdb"
> set con = Server.CreateObject("ADODB.Connection")
> con.open(conString)

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