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:359425
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
