Maybe you could do this?
SELECT TOP 25 *
FROM TABLE
WHERE IDField NOT IN (#List_Of_IDs_Viewed_Already#)
ORDER BY SomeField
You could keep track of all the ids you've viewed already and store them in
a hidden form field?
Hmmm. At the end you'd have a list with 124,975 IDs in it. I guess that
sucks.
-----Original Message-----
From: Peter Theobald [mailto:[EMAIL PROTECTED]]
Sent: Saturday, November 18, 2000 3:35 PM
To: CF-Talk
Subject: Re: scaleability of <cfoutput maxrows=x>
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
