Peter, you understood my malformed question!
You are correct about the paging thru the entire 125k records. The initial
question was based on a query that would return everything. I have another
query that is more of a search; based on part number and other fields, the
user can narrow his/her search. However, the problem still exists such as,
the user can request part numbers starting with 0.1. That query would return
a few thousand records rather than the entire lot. I will have users from
all over the world requesting information out at all times of the day and I
don't want to have too many of these sort of queries being run. If I can
limit the query on the SQL side this won't be a problem.
I will look into "SQL cursor processing" and will try to share the knowledge
as best I can. Too bad my DBA doesn't know this stuff... might be time for a
new one.
Michael
"Peter Theobald" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> In re-reading what I wrote, I am thinking that the requirements here may
be unrealistic.
> How dynamic can the inventory be for a user reading through 125,000 items
by 25 at a time?
> I think in practical use nobody is going to read through all 125,000 items
by browsing through 5,000 pages of 25 items each page by page. They will
read a few pages and go back to make a more selective search.
> You can easily solve the problem, then, by using one cached query for an
unfiltered search that caches for 5 - 20 minutes.
> When a user issues a search with criteria don't cache the results for the
most up to date inventory.
>
> Some systems would even report back a message "This query returned too
many results (125,000) please make your selection more specific and try
again."
>
> At 06:34 PM 11/18/00 -0500, Peter Theobald wrote:
> >Unfortunately both of you completely missed his point.
> >
> >He has a very large recordset of 125,000 records. They are all valid
items in his inventory. He wants to provide a page for people to browse page
by page through this inventory 25 items at a time.
> >
> >He knows how to use:
> ><CFOUTPUT maxrows=25 startrow=#x#>
> >
> >but this standard way of paging results will cause the server to execute
a large query and send the entire 125,000 records across the network from
the database server to the CF server, use 25 records and throw away the
rest.
> >
> >The inventory is in constant flux, so he doesn't want to cache the query
to make it more efficient.
> >
> >So his question is: Is there a way in SQL or CF to request ONLY a certain
section of rows from the result set just by referring to their position in
the result set (ie: there are no distinguishing characteristics like
"category" that will split his results into 25 item groups)?
> >
> >Unfortunately I don't have the answer, but you may find it someone in
"SQL cursor processing". Depending on your database type you should be able
to write an SQL statement or a stored procedure that uses a "cursor"
pointing into a recordset to have a fine level of control over which records
to return.
> >
> >Anyone out there have some experience with this?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
