Rick, Just out of interest, could one use that <cfsetting> option within a CFC that publishes a web service?
I'm glad we're on the same page about dropping that loop though, ikky stuff. Rob -----Original Message----- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: 07 June 2007 13:54 To: CF-Talk Subject: Re: Reading/Importing Large Text Files into a Database On 6/7/07, Roxie R <[EMAIL PROTECTED]> wrote: > > Basically each file is a comma delimited text file that on every line has information that needs to be checked against the database and then either updated or inserted. > > Right now I loop through every line in the file and using CFQUERY to do the database check and insert/update. I assume you're using cfloop over a the file as a list with CR/LF as the delimiter? If so - don't do that! List processing is terribly slow. I'd actually try using cfhttp to read the file and convert it to a query. Then insert *ALL* the data into a temporary table using cfquery.... And then use a stored procedure of some kind if you can to do the comparison and update/insert the rows from the temp table into the perm table and then remove the data from the temporary table. It's still going to be slow probably. But add the cfsetting statement at the top to increase the timeout. Ultimately, you really need to do this with bulk insert rather than cfhttp/cfquery to get the data INTO the database, but if the provider is unwilling to get you bulk insert permissions, there's not much you can do. Rick ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280365 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
