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?
>
>At 09:52 PM 11/17/00 -0500, Jon Hall wrote:
>>Seems to me that if this small subset of records has a unique identifier
>>that you could just limit the query using a standard WHERE statement. Just
>>adding a field to the db would be the easiest solution to only pulling the
>>set of records you need.
>>
>>I think Shawn answered your question though and you didn't see it.
>>Use the Startrow statement in your cfquery, then pass a variable called
>>'page' that is incremented by 1 each time you view the next page or
>>subtracted by 1 when you go back a page.
>>This formula might get you on the right track.
>>
>>page x maxrecordsperpage + 1 will always give you the Startrow variable for
>>your cfquery statement.
>>
>>If your recordset is a static one that does not change much and you expect
>>high enough traffic to justify a little memory usage.You might want to think
>>about caching the entire query until something is changed.
>>
>>jon
>>
>>----- Original Message -----
>>From: "[BOXoFUSES] Michael Slatoff" <[EMAIL PROTECTED]>
>>Newsgroups: cf-talk
>>To: "CF-Talk" <[EMAIL PROTECTED]>
>>Sent: Friday, November 17, 2000 6:10 PM
>>Subject: Re: scaleability of <cfoutput maxrows=x>
>>
>>
>>> This is what I'm trying to get away from. This is just outputting a subset
>>> of my entire recordset.
>>>
>>> If I've got a query that returns 125,000 records and only display 25 of
>>> them, it's a waste of the servers resources.
>>>
>>> Michael
>>>
>>> "Shawn McGehee" <[EMAIL PROTECTED]> wrote in message
>>> 015b01c050e0$044326e0$[EMAIL PROTECTED]">news:015b01c050e0$044326e0$[EMAIL PROTECTED]...
>>> > <cfoutput query="myquery" startrow="26" maxrows="25">
>>> > blah blah blah
>>> > ...
>>> > </cfoutput>
>>> >
>>> > --Shawn McGehee
>>> >
>>> > ----- Original Message -----
>>> > From: "[BOXoFUSES] Michael Slatoff" <[EMAIL PROTECTED]>
>>> > To: "CF-Talk" <[EMAIL PROTECTED]>
>>> > Sent: Friday, November 17, 2000 2:16 PM
>>> > Subject: scaleability of <cfoutput maxrows=x>
>>> >
>>> >
>>> > > I'm working on an inventory management system for a small subset of
>>out
>>> > > product database (only about 3k records compared to about 125k
>>records).
>>> > > When returning the recordset, I want to break it up into bite sized
>>> > chunks.
>>> > > Only problem is, using <cfoutput query="myQuery" maxrows=25>, the
>>query
>>> is
>>> > > still returning about 3k records and only displaying 25. If this thing
>>> > ever
>>> > > gets to the point where I need to keep track of the entire product
>>list
>>> > > (125k records), this is going to bog down things.
>>> > >
>>> > > Is there a way in SQL to set a start row for a query? I know I can use
>>> > 'set
>>> > > rowcount 25' to only get 25 records, but I then need to start again at
>>> row
>>> > > 26.
>>> > >
>>> > > Am I looking in the wrong place? Is there a different way of getting x
>>> > > records and then next x records on the SQL side?
>>> > >
>>> > > Oh, and I can't persist the data in a array of structures or wddx
>>packet
>>> > > because it is inventory that changes quickly and needs to be accurate
>>on
>>> > the
>>> > > execution of the query.
>>> > >
>>> > > Michael
>>> > >
>>> > >
>>> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>> > > Structure your ColdFusion code with Fusebox. Get the official book at
>>> > http://www.fusionauthority.com/bkinfo.cfm
>>> > >
>>> > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>>> > > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
>>> >
>>> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>> > Structure your ColdFusion code with Fusebox. Get the official book at
>>> http://www.fusionauthority.com/bkinfo.cfm
>>> >
>>> > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>>> > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
>>>
>>>
>>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>> Structure your ColdFusion code with Fusebox. Get the official book at
>>http://www.fusionauthority.com/bkinfo.cfm
>>>
>>> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>>> Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
>>
>>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>Structure your ColdFusion code with Fusebox. Get the official book at
>http://www.fusionauthority.com/bkinfo.cfm
>>
>>Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>>Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
>
>
>---------------------------------------------------------------------------
>Peter Theobald, Chief Technology Officer
>LiquidStreaming http://www.liquidstreaming.com
>[EMAIL PROTECTED]
>Phone 1.212.545.1232 x204 Fax 1.212.545.0938
>
>To put this contact information into your Palm device, click here:
>http://www.coola.com/cgi-bin/addinfo.cgi?pid=15803&rid=972879910&type=A
>
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>Structure your ColdFusion code with Fusebox. Get the official book at
>http://www.fusionauthority.com/bkinfo.cfm
>
>Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
---------------------------------------------------------------------------
Peter Theobald, Chief Technology Officer
LiquidStreaming http://www.liquidstreaming.com
[EMAIL PROTECTED]
Phone 1.212.545.1232 x204 Fax 1.212.545.0938
To put this contact information into your Palm device, click here:
http://www.coola.com/cgi-bin/addinfo.cgi?pid=15803&rid=972879910&type=A
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
