Have you investigated using a Stored Procedure that does both inserts?
---------------------------------- William Seiter -----Original Message----- From: Les Mizzell [mailto:lesm...@bellsouth.net] Sent: Thursday, October 09, 2014 9:31 AM To: cf-talk Subject: Avoiding a boat load of queries inserting multiple records - Better Way? I've got an application that imports email list from Excel sheets. Mostly working fine, but I've got one spot where I'd like to optimize things if I could. Once the data is imported I run two queries against each email address: 1. see if the email address is already in the group in question 2. see if this person has already unsubscribed from this list ---> If both of those return no records, and I've already run some other validation making sure entered info is valid, I add everything to an array: <cfset mailREC[CurrentRow][1] = email /> <cfset mailREC[CurrentRow][2] = fname /> <cfset mailREC[CurrentRow][3] = lname /> <cfset mailREC[CurrentRow][4] = other /> Now the problem ... I need to do two inserts - first to enter the info into the email database - second to capture the ID from the first insert, and put that into a relational table that assigns that address to the specified group. I removed cfqueryparam from the below to make it more compact... So, I loop through my array and enter all the records.. <loop......> <cfquery name="insertEMAIL"> SET NOCOUNT ON ..... remainder of insert code SET NOCOUNT OFF SELECT @@IDENTITY AS fromLISTID </cfquery> <cfquery name="insertGROUP" > INSERT INTO nl_catREL ( groups_id, email_id ) VALUES ( #req.thisGROUPID#, #insertLIST.fromLISTID# ) </cfquery> </loop......> The problem is, some of these spreadsheets and have up to 5,000 or more email addresses. That means, just for the insert, two queries for each address = 10,000 queries. If it was just one insert query, no big deal, as I could loop inside the query tag itself and reduce it to one call. It's the two combined that are giving me a headache. The application IS working the way it is, but shove a 15,000 email list into it, and it obviously chokes. Is there a better way to set up my two insert queries above so it's not making two calls for every single address? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359427 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
