Hello,



I'm experimenting with an ASP application that lists athletes and their

statistics that are stored in an Access DB. I'm getting to a point, however,

where my pages are bogging down because I need to retrieve multiple

recordsets for each row of my player recordset to display all of the stats

that I want. My question is what can I do to speed it up? Is there a way to

store functions or expressions in Access so that I can crunch the numbers on

the database side and simply access my results as part of my original

recordset? Is Access 2000 just too slow to handle lots of queries (around

600 or more) in a loop? Am I screwing up by programming in such a way that I

open and close DB connections too often (I do that to make my subroutines

more cohesive)?



A couple of elements of Access that look promising to me are stored

procedures and calculated fields, but I don't know if there is a way to

leverage those features through ADO. Is there? Am I barking up the right

tree? If someone could point me to any resources that explain how those

concepts can be put to use, I would be thankful.



Regards,

Josh.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Josh Turgeon" <josh_turgeon@y...>

Subject: [asp_web_howto] Effecient use of my Access DB





: Hello,

:

: I'm experimenting with an ASP application that lists athletes and their

: statistics that are stored in an Access DB. I'm getting to a point,

however,

: where my pages are bogging down because I need to retrieve multiple

: recordsets for each row of my player recordset to display all of the stats

: that I want.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



This sounds like bad query design. Surely you should be able to do a JOIN

between the relevant tables, and return just the one recordset. Remember,

databases work best when you think in terms of Sets, not Cursors.



More details on the actual tables/queries you are performing might help us

help you on a more practical level.



Cheers

Ken

Hi Josh,



I did just this  few months back...keeping track of a local football league,

and it was nuts in its first iteration!  I found the easiest solution was to

really plan out the design of your stored procedures ("queries" in Access),

so that you don't handicap the page when it's trying to load multiple

recordsets.  Try and keep this at a minimal for optimal performance.  The

data can come from multiple tables, but really should be pre-compiled on

your server through the use of a query beforehand, and then executed in your

script.



Would you be able to post your table structure so that we could make some

recommendations?



Jason





----- Original Message -----

From: "Ken Schaefer" <ken@a...>

To: "ASP Web HowTo" <asp_web_howto@p...>

Sent: Thursday, November 22, 2001 9:44 PM

Subject: [asp_web_howto] Re: Effecient use of my Access DB





> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "Josh Turgeon" <josh_turgeon@y...>

> Subject: [asp_web_howto] Effecient use of my Access DB

>

>

> : Hello,

> :

> : I'm experimenting with an ASP application that lists athletes and their

> : statistics that are stored in an Access DB. I'm getting to a point,

> however,

> : where my pages are bogging down because I need to retrieve multiple

> : recordsets for each row of my player recordset to display all of the

stats

> : that I want.

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>

> This sounds like bad query design. Surely you should be able to do a JOIN

> between the relevant tables, and return just the one recordset. Remember,

> databases work best when you think in terms of Sets, not Cursors.

>

> More details on the actual tables/queries you are performing might help us

> help you on a more practical level.

>

> Cheers

> Ken

>

>

>




$subst('Email.Unsub')

>

>

Just food for thought...Would this be a good application for datashaping?



=================================

Kyle M. Burns, MCSD, MCT

ECommerce Technology Manager

Centra Credit Union

kburns@c...



 



-----Original Message-----

From: Ken Schaefer [mailto:ken@a...]

Sent: Thursday, November 22, 2001 6:44 AM

To: ASP Web HowTo

Subject: [asp_web_howto] Re: Effecient use of my Access DB





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Josh Turgeon" <josh_turgeon@y...>

Subject: [asp_web_howto] Effecient use of my Access DB





: Hello,

:

: I'm experimenting with an ASP application that lists athletes and their

: statistics that are stored in an Access DB. I'm getting to a point,

however,

: where my pages are bogging down because I need to retrieve multiple

: recordsets for each row of my player recordset to display all of the stats

: that I want.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



This sounds like bad query design. Surely you should be able to do a JOIN

between the relevant tables, and return just the one recordset. Remember,

databases work best when you think in terms of Sets, not Cursors.



More details on the actual tables/queries you are performing might help us

help you on a more practical level.



Cheers

Ken










$subst('Email.Unsub')