«

Dear all

I have written a very complex web app for intranet use. It allows
users to search a large database and then returns formatted results. I
am currently employing a method as below:

1. Create a global database connection
2. Open a recordset that searches the database
3. Loop through recordset calling the relevant function from an
include file to display results
4. Loop until EOF or PageSize reached.
5. Close and destroy recordset
6. Close and destroy connection


Formatting function on include does the following:
1. Create a recordset and open the record passed into the function as
a primary key ID.
2. Display each field in a formatted fashion.
3. Close and destroy recordset.

Is this efficent? There is a lot of creation and destruction of
recordset objects (a typical search result may yield up to 400
records) - each time a result is returned in fact. Additionally the
database is queried twice for the same result. Once for the search
recordset and then again for the formatting function. This has got to
be slow and inefficient unless Microsoft have some clever thing that I
don't understand similar to connection pooling (although I appreciate
that this is only for ODBC connections to a database and nothing to do
with recordsets!)

I am aware that I could do this all inline in the main page - but the
formatting is employed at several different locations in the database.
What's worse is that the formatting function may call other formatting
functions to retrieve formatted data in other tables that work in a
similar fashion.

Whilst this makes my code very easy to read and re-useable I am
concerned that I am paying a very big penalty. Is there any way around
this?

Options I considered:
- Recordsets declared globally - always available for use. (Although
this won't work in many cases as lots of the functions are recursive)
- plus this is messy!
- Passing open recordsets by reference in the "right place" (on the
correct record) to the function for it to manipulate. Is this
efficient?
- As above but passing by value? Is this just as slow?
- Putting it all inline - will improve performance - but at the cost
of reusable code

Please advise - I'm pulling my hair out! Any changes will take me
several days to do (there's a lot of this code!) so I want to make
sure I get it right!!

Thanks in advance
Jason.

Hi Jason,
I read in a book and websites that is the best:
create objects and just after your use destroy them!
I believe that related to that I mentioned above, your application is fine.
About the rest, let the others say something.
bye,
--

««««««««»»»»»»»»»»»»»»
Vlmar Brazão de Oliveira
Desenvolvimento Web
HI-TEC


"Jason Gates" <> escreveu na mensagem
news: om...
> Dear all
>
> I have written a very complex web app for intranet use. It allows
> users to search a large database and then returns formatted results. I
> am currently employing a method as below:
>
> 1. Create a global database connection
> 2. Open a recordset that searches the database
> 3. Loop through recordset calling the relevant function from an
> include file to display results
> 4. Loop until EOF or PageSize reached.
> 5. Close and destroy recordset
> 6. Close and destroy connection
>
>
> Formatting function on include does the following:
> 1. Create a recordset and open the record passed into the function as
> a primary key ID.
> 2. Display each field in a formatted fashion.
> 3. Close and destroy recordset.
>
> Is this efficent? There is a lot of creation and destruction of
> recordset objects (a typical search result may yield up to 400
> records) - each time a result is returned in fact. Additionally the
> database is queried twice for the same result. Once for the search
> recordset and then again for the formatting function. This has got to
> be slow and inefficient unless Microsoft have some clever thing that I
> don't understand similar to connection pooling (although I appreciate
> that this is only for ODBC connections to a database and nothing to do
> with recordsets!)
>
> I am aware that I could do this all inline in the main page - but the
> formatting is employed at several different locations in the database.
> What's worse is that the formatting function may call other formatting
> functions to retrieve formatted data in other tables that work in a
> similar fashion.
>
> Whilst this makes my code very easy to read and re-useable I am
> concerned that I am paying a very big penalty. Is there any way around
> this?
>
> Options I considered:
> - Recordsets declared globally - always available for use. (Although
> this won't work in many cases as lots of the functions are recursive)
> - plus this is messy!
> - Passing open recordsets by reference in the "right place" (on the
> correct record) to the function for it to manipulate. Is this
> efficient?
> - As above but passing by value? Is this just as slow?
> - Putting it all inline - will improve performance - but at the cost
> of reusable code
>
> Please advise - I'm pulling my hair out! Any changes will take me
> several days to do (there's a lot of this code!) so I want to make
> sure I get it right!!
>
> Thanks in advance
> Jason.

Jason Gates wrote:
> Dear all
>
> I have written a very complex web app for intranet use. It allows
> users to search a large database and then returns formatted results. I
> am currently employing a method as below:
>
> 1. Create a global database connection

Ignore the following if you mean that you are opening a database connection
on each page.

************************************************** ********
Assuming you are storing this connection in Application, this is very bad.
ADO objects are not free-threaded, which means only one thread will be able
to use this connection object at a time. Connections should be opened late
and closed early in each page in which they are used.
http://www.aspfaq.com/2053

It is possible to use a batch file called makfre15.bat (located in
C:\Program Files\Common Files\System\ADO on my machine) to cause a registry
change to make the ADO objects free-threaded, allowing you to use them in
Application. However, if any applications on that machine are connecting to
Jet databases (Access), YOU MUST NOT DO THIS.
************************************************** ********


> 2. Open a recordset that searches the database
> 3. Loop through recordset calling the relevant function from an
> include file to display results
> 4. Loop until EOF or PageSize reached.

Here are some alternatives that may improve your performance:
http://www.aspfaq.com/show.asp?id=2120

> 5. Close and destroy recordset
> 6. Close and destroy connection

Excellent.

You may wish to consider using GetRows arrays to avoid all the recordset
looping - looping through recordsets is incredibly slow compared to looping
through arrays. Better yet, if possible, make use of GetString. Take a look
at this:
http://www.aspfaq.com/show.asp?id=2467

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"