Hi, I've just been through this.
I found that some of the threads in our forums were nearing 1000 messages, and the query containing all this was about 1.5Mb in size. The only snag with using a stored procedure for this is that it is very difficult to get a RecordCount for the entire query. The way I finally did it was to run the query twice: <cfquery name="getMessageIds"> SELECT messageId FROM messages WHERE threadId = #threadId# ...... any other criteria ORDER BY messageId </cfquery> <cfquery name="getMessages"> SELECT messageId, messageTitle, messageBody etc. etc. FROM messages WHERE messageId IN <cfloop query="getMessages" startRow="#startRow#" endrow="#endRow#"></cfloop> ORDER BY messageId </cfquery> This reduced my memory usage from 1.5Mb for one query to about 50k for both queries. Also the execution time for the two queries was about the same as for the one large query. There is a tipping point on this - if your queries are returning less than about two pages of results, you may use slightly more memory and take slightly more time. But I decided that using 12k instead of 10k occasionally outweighed regular 1.5Mb queries. You also need to make sure that the ORDER BY clause is identical for both queries. Caching the first query seems to make sense as it is very small in size and will be used multiple times as the user pages through the results. I created myself a simple generic helper component to hold everything, which had methods such as: setFullQuery() - contains results of first query setStartRow() setMaxRows() getPagedIdList() - returns Id list for the IN clause of second query setPagedQuery() getPagedQuery() getRecordCount() The memory useages and times above include the overhead of this component. Ian ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216956 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
