Recordset Management 
Author Message
 Recordset Management

I have a relatively large SQL database (over 30,000 records) from which I
call a stored procedure, which returns a resultset containing from 50 to 500
records. I use connection.execute rather then the command object, as
detailed in the stored procedure article at aspfaq.com
( http://www.*-*-*.com/ ).

The next step is to be able to manipulate this data in several different
ways (sort by col1, order by col2, where col3 = 'x', etc.). Furthermore, the
various ways of manipulating the data is dynamic (chosen by the user).

It makes sense to me not to re-query the entire database to obtain the
recordset in a different form, but I am not aware of any way to retrieve a
resultset and then submit SQL statements to it.

Am I correct in trying to query the resultset rather than the database, and
if so, how do I go about doing this?

--
Regards,

Fred Chateau
http://www.*-*-*.com/



Mon, 13 Oct 2003 10:50:23 GMT  
 Recordset Management

Quote:
> I have a relatively large SQL database (over 30,000 records)

That's a tiny database!

Quote:
> The next step is to be able to manipulate this data in several
different
> ways (sort by col1, order by col2, where col3 = 'x', etc.).
Furthermore, the
> various ways of manipulating the data is dynamic (chosen by the
user).
> It makes sense to me not to re-query the entire database to obtain
the
> recordset in a different form, but I am not aware of any way to
retrieve a
> resultset and then submit SQL statements to it.

Read up on disconnected ADO recordsets, the .Filter and .Sort
properties. You do not need to requery your database (though with such
a miniscule database,  it probably doesn't matter which approach you
adopt).

--
Paul Thornett

Help fight Alzheimer's, Cystic Fibrosis and Mad Cow Disease
http://www.stanford.edu/group/pandegroup/Cosm/



Mon, 13 Oct 2003 12:42:02 GMT  
 Recordset Management
"Paul Thornett" wrote ...

Quote:
> That's a tiny database!

That's why I said relatively . . .    :)

Quote:
> Read up on disconnected ADO recordsets, the .Filter and .Sort
> properties. You do not need to requery your database (though with such
> a miniscule database,  it probably doesn't matter which approach you
> adopt).

Thanks for the referral. I'll check it out.

I think it may make some difference when hundreds of users are accessing
separate resultsets simultaneously, because that's thousands of hits on the
database that need not occur. It seems to me that no matter how you slice
it, a hit on the Web server is better than one hit on the Web server and one
hit on the database, but maybe not.

--
Regards,

Fred Chateau
http://members.home.net/fchateau/



Mon, 13 Oct 2003 14:33:44 GMT  
 Recordset Management


Quote:

>I have a relatively large SQL database (over 30,000 records) from which I
>call a stored procedure, which returns a resultset containing from 50 to 500
>records. I use connection.execute rather then the command object, as
>detailed in the stored procedure article at aspfaq.com
>(http://www.aspfaq.com/faq/sp.asp).

This method works fine but is a lot less flexable than the
recordset.Open method, or the Command.Execute method.

It returns a Forward-Only/ Read_Only cursor (Ie - no cursor at all).
Is that what you want? It will be fast, but so will the other methods
with the same cursor type.

Quote:

>The next step is to be able to manipulate this data in several different
>ways (sort by col1, order by col2, where col3 = 'x', etc.). Furthermore, the
>various ways of manipulating the data is dynamic (chosen by the user).

How are you displaying the data? If you use something like the
MSFlexGrid control, it has a built in sort function.

Otherwise, re-querying is usually the best way. It is the most
flexible approach, and in this case, it should be pretty fast.

Quote:

>It makes sense to me not to re-query the entire database to obtain the
>recordset in a different form, but I am not aware of any way to retrieve a
>resultset and then submit SQL statements to it.

I am not currently using SQL server so I cannot check this out myself
- Can you construct a SQL statement using the stored procedure name as
a table name?  ie

"SELECT * FROM myStoredProcedure WHERE Value > 10000" & _
                    " ORDER BY State, InvoiceDate DESC"

Create the WHERE and ORDER by clauses in your code. If this works,
then t is the way I would probably do it.

Quote:

>Am I correct in trying to query the resultset rather than the database, and
>if so, how do I go about doing this?

Databases love Queries. Although recordsets have a limited Sort and
Filter ability (if the provider, and cursor allows it), it often is
faster to requery.

Richard.

Quote:

>--
>Regards,

>Fred Chateau
>http://members.home.net/fchateau/



Mon, 13 Oct 2003 15:33:03 GMT  
 Recordset Management
"Richard" wrote ...

Quote:
> How are you displaying the data? If you use something like the
> MSFlexGrid control, it has a built in sort function.

> Otherwise, re-querying is usually the best way. It is the most
> flexible approach, and in this case, it should be pretty fast.

The data is being submitted to a COM module which plots coordinates on a
map. I must be able to access the re-queried data through several DO loops.
Basically, the user selects criteria which defines the sets of coordinates
he wants to see, and the coordinates are submitted to the COM module as
argument pairs. The original resultset returned by the stored procedure is
simply all the coordinates pertaining to that particular map, which is the
starting point. In other words, all coordinates are plotted at first, then
some are dropped, then added again, then others dropped, etc.

My concern is that while the original set of data the user selects remains
constant for a longer range of time, he may be creating a fairly large
number of subsets, and allowing for the possibility that several hundred
users are all doing the same thing simultaneously, I think it makes sense to
stay with data already in the Web server's memory, but I'm just guessing
about this.

It just occurred to me as I'm writing this that I could optimize the
resultset from the stored procedure. Right now, I'm just querying all the
possible rows I need, but I could pre-sort and order the data in advance,
which might make things faster when being handled by the recordset sort and
filter properties mentioned previously.

Quote:
> I am not currently using SQL server so I cannot check this out myself
> - Can you construct a SQL statement using the stored procedure name as
> a table name?  ie

> "SELECT * FROM myStoredProcedure WHERE Value > 10000" & _
>                     " ORDER BY State, InvoiceDate DESC"

> Create the WHERE and ORDER by clauses in your code. If this works,
> then t is the way I would probably do it.

What an interesting idea! I certainly will try it.

Quote:
> Databases love Queries. Although recordsets have a limited Sort and
> Filter ability (if the provider, and cursor allows it), it often is
> faster to requery.

I may copy this message over to a SQL Server newsgroup and see what they
have to say about it.

--
Regards,

Fred Chateau
http://members.home.net/fchateau/



Mon, 13 Oct 2003 16:27:25 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. ADO recordsets and memory management

2. management module(s)

3. Dates management in Access

4. Memory management using Access 97 under WinNT

5. DLL management in VBA for Access ???

6. contact management

7. what found for ur Bookmark management

8. Need help in management of dewey decimal numbers.

9. System Management, Almost There

10. System Management

11. Access 97 Memory Management Tool

12. Outlook Customer Management

 

 
Powered by phpBB® Forum Software