J
Jason Gates
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.
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.