Les, I think that it would probably be more efficient if you imported all the records into a temp table and then did an insert into the main email table based upon a join query that only includes records from the temp table that are not in the main email table. You could then do a similar insert into the category relationship table, but update the query to use the new id value that was created by the insert into the main email table. Both of these queries could be included in a single stored procedure and executed with a single request from ColdFusion.
On Thu, Oct 9, 2014 at 12:30 PM, Les Mizzell <lesm...@bellsouth.net> wrote: > > 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:359430 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
