Re: Dealing with Large queries and csv files
thank you... however, my bottleneck isnt the file i/o, its the taking of the query object and inserting the comma's in between each value and the cr/lf at the end of a row. thats the problem. getting the data into a file, is easy and quick. i see, through my test harness, that my bottleneck is that darned query object into csv object... any ideas? tw On 5/16/07, Jon Clausen [EMAIL PROTECTED] wrote: Tony, Here's a quick tutorial on using BCP with SQL Server 2000 (with links to BCP resources on MSDN) to output text files: http://www.sqlteam.com/item.asp?ItemID=4722 If you want to maintain portability between DBMS' by using Coldfusion to handle the export workload, Ben Nadel had a good article the other day on optimizing that process by using the Java string buffer : http://www.bennadel.com/blog/679-ColdFusion-Optimizing-Case-Study- Writing-Orders-To-A-CSV-File.htm HTH, Jon On May 16, 2007, at 3:36 PM, Tony wrote: ok. next question. how in the heeeck do i do it in SQL? im using sql server 2000. i could use sql server 2005. any and ALL HELP is appreciated. thanks tony On 5/16/07, Robertson-Ravo, Neil (RX) [EMAIL PROTECTED] wrote: Best bet is don't do it in ColdFusion at all, let the SQL engine do the work and ref the resultant file. ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:278452 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Dealing with Large queries and csv files
Best bet is don't do it in ColdFusion at all, let the SQL engine do the work and ref the resultant file. This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Tony To: CF-Talk Sent: Wed May 16 20:27:50 2007 Subject: Dealing with Large queries and csv files hi there. when dealing with record sets in the 10,000 records plus arena how do you, in the fastest way possible, get that record set into comma delimited format, and subsequently write that out to a file? i have a nice little test bed setup, that i can see very the bottlenecks in doing this. getting the data takes less than 2 seconds taking the data from a query object to a csv formatted object takes too long writing it to a file takes no time at all maybe a second or two and the rest is willy nilly... its just the process of query object to csv formatted object, that is the problem... how/what do you all do? btw, im currently using the UDF (QueryToCsvSpecificRows()) thanks! tony -- 'Never have anything in your life that you can't walk out on in thirty seconds flat, if you spot the heat coming around the corner' robert deniro - heat (1995) ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:278329 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dealing with Large queries and csv files
ok. next question. how in the heeeck do i do it in SQL? im using sql server 2000. i could use sql server 2005. any and ALL HELP is appreciated. thanks tony On 5/16/07, Robertson-Ravo, Neil (RX) [EMAIL PROTECTED] wrote: Best bet is don't do it in ColdFusion at all, let the SQL engine do the work and ref the resultant file. This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Tony To: CF-Talk Sent: Wed May 16 20:27:50 2007 Subject: Dealing with Large queries and csv files hi there. when dealing with record sets in the 10,000 records plus arena how do you, in the fastest way possible, get that record set into comma delimited format, and subsequently write that out to a file? i have a nice little test bed setup, that i can see very the bottlenecks in doing this. getting the data takes less than 2 seconds taking the data from a query object to a csv formatted object takes too long writing it to a file takes no time at all maybe a second or two and the rest is willy nilly... its just the process of query object to csv formatted object, that is the problem... how/what do you all do? btw, im currently using the UDF (QueryToCsvSpecificRows()) thanks! tony -- 'Never have anything in your life that you can't walk out on in thirty seconds flat, if you spot the heat coming around the corner' robert deniro - heat (1995) ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:278331 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dealing with Large queries and csv files
If you database server has access to a file system that your CF server also has access to, you could try using the export functions built into your DB server. I usually try to keep data processing in the DB server since it is way more efficient at it. Wes On 5/16/07, Tony [EMAIL PROTECTED] wrote: hi there. when dealing with record sets in the 10,000 records plus arena how do you, in the fastest way possible, get that record set into comma delimited format, and subsequently write that out to a file? i have a nice little test bed setup, that i can see very the bottlenecks in doing this. getting the data takes less than 2 seconds taking the data from a query object to a csv formatted object takes too long writing it to a file takes no time at all maybe a second or two and the rest is willy nilly... its just the process of query object to csv formatted object, that is the problem... how/what do you all do? btw, im currently using the UDF (QueryToCsvSpecificRows()) thanks! tony -- 'Never have anything in your life that you can't walk out on in thirty seconds flat, if you spot the heat coming around the corner' robert deniro - heat (1995) ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:278336 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dealing with Large queries and csv files
whoops, didn't see the other posts. You could create a SQL server integration services package. This could then be executed from a stored procedure that your CF code could call. (supposing that CF needs to do something with the file afterwards). On 5/16/07, Wesley Middendorff [EMAIL PROTECTED] wrote: If you database server has access to a file system that your CF server also has access to, you could try using the export functions built into your DB server. I usually try to keep data processing in the DB server since it is way more efficient at it. Wes On 5/16/07, Tony [EMAIL PROTECTED] wrote: hi there. when dealing with record sets in the 10,000 records plus arena how do you, in the fastest way possible, get that record set into comma delimited format, and subsequently write that out to a file? i have a nice little test bed setup, that i can see very the bottlenecks in doing this. getting the data takes less than 2 seconds taking the data from a query object to a csv formatted object takes too long writing it to a file takes no time at all maybe a second or two and the rest is willy nilly... its just the process of query object to csv formatted object, that is the problem... how/what do you all do? btw, im currently using the UDF (QueryToCsvSpecificRows()) thanks! tony -- 'Never have anything in your life that you can't walk out on in thirty seconds flat, if you spot the heat coming around the corner' robert deniro - heat (1995) ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:278337 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Dealing with Large queries and csv files
Tony, Here's a quick tutorial on using BCP with SQL Server 2000 (with links to BCP resources on MSDN) to output text files: http://www.sqlteam.com/item.asp?ItemID=4722 If you want to maintain portability between DBMS' by using Coldfusion to handle the export workload, Ben Nadel had a good article the other day on optimizing that process by using the Java string buffer : http://www.bennadel.com/blog/679-ColdFusion-Optimizing-Case-Study- Writing-Orders-To-A-CSV-File.htm HTH, Jon On May 16, 2007, at 3:36 PM, Tony wrote: ok. next question. how in the heeeck do i do it in SQL? im using sql server 2000. i could use sql server 2005. any and ALL HELP is appreciated. thanks tony On 5/16/07, Robertson-Ravo, Neil (RX) [EMAIL PROTECTED] wrote: Best bet is don't do it in ColdFusion at all, let the SQL engine do the work and ref the resultant file. ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:278348 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
