Another method, assuming you're using MS SQL Server (not sure how far
this goes back compatibility-wise) is to toss in the criteria for the
full search, but then to only pull back X rows:
WITH Results_Full AS (
SELECT Field1, Field2, Field3, ROW_NUMBER()
OVER (ORDER BY Field1) AS RowNum
FROM tableName
)
SELECT Field1, Field2, Field3
FROM Results_Full
WHERE RowNum >= #StartRowNum# AND RowNum <= #EndRowNum#
Where StartRowNum/EndRowNum are whatever you set.
I've found this method to be superfast when querying large recordsets.
Works great especially if you're doing a "show X rows on a page" thing
with forward/back/etc.
--Scott
On 1/5/13 3:39 PM, =?ISO-8859-1?Q?Claude_Schn=E9egans wrote:
> >>2. Only query the primary keys, and then loop over that list grabbing x
> records at a time and doing a new query to get all rows for those keys.
>
> This is a pretty good method.
> I tested it on a database containing about 450000 records with a seach
> template.
> I give a very loose criterion on purpose, the query takes 53 sec and returns
> 44500 records.
> This is ridiculous since the user will only see ten records at a time, using
> startRow and maxRows in a CFOUTPUT.
> And for the next page, it takes another 50sec :-(
> A certain amount of time is also taken by CFX_highlight which highlights
> every occurence of the search string,
> again in the 44500 records!
>
> With this method, the query returns only the 10 records needed, and it takes
> about 5 sec the first time and 3 sec any subsequent times.
> And the CFX_highlight is applied on only 10 records at a time. A big
> difference.
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353793
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
