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
