CF is not a good choice to accomplish this. Caching a result set of 170k records is not good solution to whatever you are trying to accomplish.
Let sql server do the heavy lifting. I would write a stored procedure and use startrow and endrow paginations within the procedure. All you will need to do from CF side is to pass in the start row and end row to the stored procedure. From this logic, you can write a cf layer to cache a small subset of this data at a time. -----Original Message----- From: jjbgotty jjbgotty [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 05, 2006 1:23 PM To: CF-Talk Subject: CF crashing SQL 170K Hi all, I have a database with 170K records. What I'm doing is using CF to query this data with a blockfactor attribute. <cfquery name="qry_selectContacts" datasource="#getVars().dsn#" blockfactor="#arguments.maxRows#"> I have my loop doing. <cfloop query="qry_getRecords" startrow="#url.startRow#" endrow="#iif(not isDefined('form.goToPage') and url.startRow eq 1 or (isDefined('form.goToPage') and form.goToPage eq 1), de('#variables.maxRows#'), de('#(url.startRow+(variables.maxRows-1))#'))#"> Now my query is in an application cached component. SQL in this case is on the same machine. What seems to be happening when it doesn't crash is that the connection from CF to the database (Driver?) is getting clogged up. CF returns the list immediatelly as soon as the data is retrieved. Does anyone have any ideas on how I can speed up the process? I have a feeling I need to do something in SQL but I don't know what to do. How come with the blockfactor it still seems to be trying to retrieve all the data? --------------------------------- Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1ยข/min. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245463 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
