Re: cfquery results--memory resident?
Very slick. Thanks guys! --Ben On 1/5/2013 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 45 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:353792 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery results--memory resident?
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 45 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
Re: cfquery results--memory resident?
On Sat, Jan 5, 2013 at 8:25 AM, Russ Michaels r...@michaels.me.uk wrote: 2 simple solutions are. 1. Dont query all the data at all, instead provide a search form to get at specific records, which is usually much simpler thsn paging through hundreds or thousands of records. 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. If you know you are going to have huge datasets shouldn't you also use blockfactor? my assumption is that while it might not speed things up, it's at least clearing things out of buffer while it works on more records. ~| 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:353794 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
