Evening,
I'm still fighting with my Excel Import application a bit. Basically, it
works pretty well, as long as there's a limited number of records being
imported.
Problem is, the client tried to import a list with almost 15,000
addresses today, and it eventually timed out or the server reset the
connection before it finished the import. Then my phone rings...
The app does several things:
1. Import 4 columns from an excel sheet [email][first name][last
name][company] into an array
2. Check the email address to see if it's valid. If not, delete that row
from the array
3. Check the email address to see if it's been previously unsubscribed
from the list and delete that row from the array
4. Check the list against other email addresses already in the database
for that list, and delete that row so as to not import it if it's
already there.
5. After all that, write the record
Running some test with the 15,000 address sheet
Reading the Excel sheet in and running steps 1 through 4 above is taking
well over 10 minutes by itself.
Writing the record after that, enough extra time to be sure it borks up.
Smaller list (several hundred addresses) seem to import fine - MUCH
faster than the former POI based app.
So my question is - I need to optimize something somewhere to cut down
CPU cycles and increase efficiency.
The client uses this thing multiple times every day.
Some of the code is from an example by Raymond Camden. Thanks Raymond.
Full app below.
Where are my major bottlenecks and what can I do about them?
----------------------------------------------------------------------------
<cftry>
<cfset theISSUE = "true" />
<cfif structKeyExists(form, "email_list") and len(form.email_list)>
<!--- Each mail list has to belong to a group:
1. If a New Group - add to groups table and get ID--->
<cfif IsDefined("form.newGROUP") AND #form.newGROUP# NEQ "">
<cfquery name="addGROUP" datasource = "#req.datasource#"
username="#username#" password="#password#">
SET NOCOUNT ON
INSERT into nl_groupLIST ( mgroup,status ) values ( <cfqueryparam
value="#trim(form.newGROUP)#" cfsqltype="CF_SQL_VARCHAR"> ,'act' )
SELECT @@IDENTITY AS newGROUPID
SET NOCOUNT OFF
</cfquery>
<cfset req.thisGROUPID = "#addGROUP.newGROUPID#" >
</cfif>
<!---2. If an existing group - just get the id--->
<cfif IsDefined("form.mgroup") AND #form.mgroup# NEQ "xx"><cfset
req.thisGROUPID = "#form.mgroup#" ></cfif>
<!--- Get the Excel Sheet and send it to a destination outside of web
root --->
<cfset dest = getTempDirectory()>
<cffile action="upload"
destination="#dest#"
filefield="email_list"
result="upload" nameconflict="makeunique">
<cfif upload.fileWasSaved>
<cfset theFile = upload.serverDirectory & "/" & upload.serverFile>
<cfif isSpreadsheetFile(theFile)>
<cfspreadsheet action="read" src="#theFile#" query="data">
<cffile action="delete" file="#theFile#">
<cfelse>
<cfset theISSUE = "invalid" />
<cfset errors = "The file was not an Excel file.">
<cffile action="delete" file="#theFile#">
</cfif>
<cfelse>
<cfset theISSUE = "loading" />
<cfset errors = "The file was not properly uploaded.">
</cfif>
</cfif>
<cfset metadata = getMetadata(data)>
<cfset colList = "">
<cfloop index="col" array="#metadata#">
<cfset colList = listAppend(colList, col.name)>
</cfloop>
<cfif data.recordCount is 0>
<cfset theISSUE = "empty" />
<cfelse>
<!--- Create an array and read the data into it --->
<cfset mailREC=arraynew(2)>
<cfoutput query="data">
<cfif IsDefined("data.col_1") AND
#isVALID("email",data.col_1)#><cfset email = trim(#data.col_1#)
/><cfelse><cfset email = "-" /></cfif>
<cfif IsDefined("data.col_2") AND #len(data.col_2)#><cfset fname =
trim(#data.col_2#) /><cfelse><cfset fname = "-" /></cfif>
<cfif IsDefined("data.col_3") AND #len(data.col_3)#><cfset lname =
trim(#data.col_3#) /><cfelse><cfset lname = "-" /></cfif>
<cfif IsDefined("data.col_4") AND #len(data.col_4)#><cfset other =
trim(#data.col_4#) /><cfelse><cfset other = "-" /></cfif>
<cfset mailREC[CurrentRow][1] = email />
<cfset mailREC[CurrentRow][2] = fname />
<cfset mailREC[CurrentRow][3] = lname />
<cfset mailREC[CurrentRow][4] = other />
</cfoutput>
<!--- Before we can save the list, we need to run two checks --->
<!--- First, is there an email address in this list that has previously
unsubscribed? --->
<cfoutput>
<cfloop from="#arrayLen(mailREC)#" to="1" step="-1" index="i">
<!--- delete the row if it was an invalid address. Got to be a way
to fix this but --->
<!--- the above "data" output block wants to add an invalide row
if you try to skip the entire row when the address is invalid --->
<cfif mailREC[i][1] eq "-">
<cfset ArrayDeleteAt(mailREC, #i#)>
<cfelse>
<!--- OK, it's a valid address. Have they unsubscribed before? --->
<cfquery name="unsubbed" datasource="#req.datasource#"
username="#username#" password="#password#">
SELECT groups_id FROM maillist_unsubscribe
WHERE dateRESUB is NULL
AND ml_email = <cfqueryparam value="#mailREC[i][1]#"
cfsqltype="CF_SQL_VARCHAR">
AND groups_id = <cfqueryparam value="#req.thisGROUPID#"
cfsqltype="CF_SQL_INTEGER" />
</cfquery>
<cfif unsubbed.recordcount EQ 1><cfset ArrayDeleteAt(mailREC,
#i#)></cfif>
</cfif>
</cfloop>
<!--- if Admin has checked the duplicate filter box on the form, but
sure nothing incoming is a duplicate already in the database for this
group --->
<cfif form.dupFILTER EQ "on">
<cfloop from="#arrayLen(mailREC)#" to="1" step="-1" index="i">
<cfquery name="ckDUP" datasource = "#req.datasource#"
username="#username#" password="#password#">
Select groups_id from vw_mailLIST
WHERE groups_id = <cfqueryparam
value="#trim(req.thisGROUPID)#" cfsqltype="CF_SQL_INTEGER" />
AND ml_email = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#mailREC[i][1]#" />
</cfquery>
<cfif ckDUP.recordcount EQ 1><cfset ArrayDeleteAt(mailREC,
#i#)></cfif>
</cfloop>
</cfif>
</cfoutput>
<!--- at this point, we should have a clean list. Let's write it to the
database, plus add the relational data to the catREL table --->
<cfquery name="insertLIST" datasource = "#req.datasource#"
username="#username#" password="#password#" timeout="90">
<cfloop from="1" index="i" to="#arrayLen(mailREC)#">
INSERT INTO nl_mailgroups (
ml_email,
ml_firstname,
ml_lastname,
other
) VALUES (
<cfqueryparam value="#mailREC[i][1]#"
cfsqltype="CF_SQL_VARCHAR" />,
<cfqueryparam value="#mailREC[i][2]#"
cfsqltype="CF_SQL_VARCHAR" null="#not len(mailREC[i][2])#" />,
<cfqueryparam value="#mailREC[i][3]#"
cfsqltype="CF_SQL_VARCHAR" null="#not len(mailREC[i][3])#" />,
<cfqueryparam value="#mailREC[i][4]#"
cfsqltype="CF_SQL_VARCHAR" null="#not len(mailREC[i][4])#" /> );
INSERT INTO nl_catREL (
groups_id, ml_id
) VALUES (
#req.thisGROUPID#, scope_identity() );
</cfloop>
</cfquery>
</cfif>
<!--- WHAT SCREWED UP? --->
<cfcatch type="Any">
<cfoutput>
<hr>
<h1>Error: #cfcatch.Type#</h1>
<ul>
<li><b>Message:</b> #cfcatch.Message#
<li><b>Detail:</b> #cfcatch.Detail#
</ul>
</cfoutput>
<cfset errorCaught = "General Exception">
</cfcatch>
</cftry>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:359645
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
