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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280362 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
