Large recordsets turned into text file
Have an app. That needs to turn a query of about 150,000 records into a text file, zip it, send back to user. Using cf to loop and build the text file just wont work, it takes forever and often crashes. Someone mentioned a way todo it with php, by invoking a function that converts the record set in ram to a text file and then zips and send back. But we are stuck doing it on box with mx 6.0. Looked at having sql generate the file, but trying to avoid mapping drives to move it around. Anyone got an clever ideas? gabe ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216346 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
Large recordsets turned into text file
Have an app. That needs to turn a query of about 150,000 records into a text file, zip it, send back to user. Using cf to loop and build the text file just wont work, it takes forever and often crashes. Someone mentioned a way todo it with php, by invoking a function that converts the record set in ram to a text file and then zips and send back. But we are stuck doing it on box with mx 6.0. Looked at having sql generate the file, but trying to avoid mapping drives to move it around. Anyone got an clever ideas? gabe ~| 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:216362 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
RE: Large recordsets turned into text file
I would definitely use SQL Server DTS (if that is the SQL you mean) to generate the file. You don't need to map any drives either. Just give SQL and CF the appropriate permissions and you can use \\servername\share\ to access the files. M!ke -Original Message- From: gabriel l smallman [mailto:[EMAIL PROTECTED] Sent: Thursday, August 25, 2005 10:10 AM To: CF-Talk Subject: Large recordsets turned into text file Have an app. That needs to turn a query of about 150,000 records into a text file, zip it, send back to user. Using cf to loop and build the text file just wont work, it takes forever and often crashes. Someone mentioned a way todo it with php, by invoking a function that converts the record set in ram to a text file and then zips and send back. But we are stuck doing it on box with mx 6.0. Looked at having sql generate the file, but trying to avoid mapping drives to move it around. Anyone got an clever ideas? gabe ~| 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:216371 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
RE: Large recordsets turned into text file
What format do you want it in? CSV? Have you tried CF_Query2CSV http://www.cfcustomtags.com/goto.cfm?LinkID=334 Dave -Original Message- From: gabriel l smallman [mailto:[EMAIL PROTECTED] Sent: Thursday, August 25, 2005 11:10 AM To: CF-Talk Subject: Large recordsets turned into text file Have an app. That needs to turn a query of about 150,000 records into a text file, zip it, send back to user. Using cf to loop and build the text file just wont work, it takes forever and often crashes. Someone mentioned a way todo it with php, by invoking a function that converts the record set in ram to a text file and then zips and send back. But we are stuck doing it on box with mx 6.0. Looked at having sql generate the file, but trying to avoid mapping drives to move it around. Anyone got an clever ideas? gabe ~| 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:216374 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Large recordsets turned into text file
We had the same problem. We found that anything over 5 was crashing our server using the old school cfloop over a variable. Check out cflib.org http://cflib.org and search for this QueryToCSV2 It uses JAVA and the toString method. It reduced our export time by about 2000% and now I have succesfully exported recordsets as large as 50 records. Qasim Rasheed is the guy who wrote it, it rules and he is the man. =] On 8/25/05, gabriel l smallman [EMAIL PROTECTED] wrote: Have an app. That needs to turn a query of about 150,000 records into a text file, zip it, send back to user. Using cf to loop and build the text file just wont work, it takes forever and often crashes. Someone mentioned a way todo it with php, by invoking a function that converts the record set in ram to a text file and then zips and send back. But we are stuck doing it on box with mx 6.0. Looked at having sql generate the file, but trying to avoid mapping drives to move it around. Anyone got an clever ideas? gabe ~| 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:216382 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
Re: Large recordsets turned into text file
You should use something like a buffered writer to create the file. you can probably createObject(java, ...) to get it working. Look at: http://java.sun.com/j2se/1.5.0/docs/api/java/io/BufferedWriter.html for info on how it works. I don't really have time to play with it today, so, sorry i can't help any more. -nathan strutz http://www.dopefly.com/ On 8/25/05, gabriel l smallman [EMAIL PROTECTED] wrote: Have an app. That needs to turn a query of about 150,000 records into a text file, zip it, send back to user. Using cf to loop and build the text file just wont work, it takes forever and often crashes. Someone mentioned a way todo it with php, by invoking a function that converts the record set in ram to a text file and then zips and send back. But we are stuck doing it on box with mx 6.0. Looked at having sql generate the file, but trying to avoid mapping drives to move it around. Anyone got an clever ideas? gabe ~| 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:216385 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Large recordsets turned into text file
Alan, Thanks for nice comments about the UDF and I am glad that it worked for you. I have been using it at my work since some of our dataset are huge and it was taking forever for clients to download a CSV file. Qasim Rasheed On 8/25/05, Alan Rother [EMAIL PROTECTED] wrote: We had the same problem. We found that anything over 5 was crashing our server using the old school cfloop over a variable. Check out cflib.org http://cflib.org and search for this QueryToCSV2 It uses JAVA and the toString method. It reduced our export time by about 2000% and now I have succesfully exported recordsets as large as 50 records. Qasim Rasheed is the guy who wrote it, it rules and he is the man. =] On 8/25/05, gabriel l smallman [EMAIL PROTECTED] wrote: Have an app. That needs to turn a query of about 150,000 records into a text file, zip it, send back to user. Using cf to loop and build the text file just wont work, it takes forever and often crashes. Someone mentioned a way todo it with php, by invoking a function that converts the record set in ram to a text file and then zips and send back. But we are stuck doing it on box with mx 6.0. Looked at having sql generate the file, but trying to avoid mapping drives to move it around. Anyone got an clever ideas? gabe ~| 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:216418 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
