>>>I have an ASP page that does a database query. It typically retrieves between 0 and 15 records, with 4 fixed-length fields from each record including a unique identifier, and realistically the query is constrained to not ever retrieve more records than that. Regardless of how many are actually retrieved, there is _no_ guarantee that information from all of them will actually be used somewhere on the dynamically generated page. If used, the elements of data are distributed "here and there", not in a list.
>>>
>>>My question: What is the most efficient strategy for implementing the query? For example:
>>>- Create an ADO recordset and persist the recordset throughout the life of the page, and look into the recordset to find the elements of data I need to use at specific points in the page.
>>>- Create an ADO recordset, and from it populate some global script variables (such as arrays or uniquely delimited strings) that can be used throughout the page to provide the elements of data used at specific points in the page.
>>>- Use an MTDLL to create VFP cursor, and persist the cursor throughout the life of the page, and do COM callbacks to fetch (from the cursor) the elements of data I need to use at specific points in the page.
>>>- Use an MTDLL to create VFP cursor, and from it populate some object properties (or simply pass back a constructed text blob) as uniquely delimited string(s) that can be used throughout the page to provide the elements of data used at specific points in the page.
>>>- I assume that the least efficient approach is to do a more specific query that retrieves only one data element in an "on demand" basis, i.e., where the query is repeated with a more granular WHERE clause every time a specific data element is needed at a specific point on the page.
>>>- Other?
>>>
>>>Thanks very much.
>>
>>Mark,
>>As far as I understood I'd choose:
>>
>>"the least efficient approach is to do a more specific query that retrieves only one data element in an "on demand" basis..."
>>
>>Cetin
>
>I don't understand why that would be the best approach. If there are records like this:

>XXXX01  A_DATA  B_DATA  C_DATA
>XXXX02  A_DATA  B_DATA  C_DATA
>XXXX03  A_DATA  B_DATA  C_DATA
>XXXX04  A_DATA  B_DATA  C_DATA

>...where XXXXYY is the unique identifier, and A_DATA B_DATA and C_DATA are different for each record.
>
>then I can pull all 4 records into the recordset with one query using a WHERE clause (ansi OFF) like ID='XXXX', or I can pull each one using ID='XXXXYY'. I thought certainly it would be faster to create one record set with all hits and do something with it, rather than regenerate a recordset every time I needed one of the X_DATA items.

Well I basically based my choice on "...throughout the life of the page..." in your question. You cannot predict how long the page's life is. If you meant to persist it to session variable then viable. Otherwise I think a connection is a precious resource that you should return to pool immediately. With connection pooling 'on demand' queries might perform better.
If you said ASP.Net and not asp then I'd agree with initial 'pull'. ADO.Net is disconnected vs ADO is not.
Cetin