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# </cfquery> <cfquery name="getMessages"> SELECT messageId, messageTitle, messageBody etc. etc. FROM messages WHERE messageId IN <cfloop query="getMessages" startRow="#startRow#" endrow="#endRow#"></cfloop> </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. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216955 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
