You need to turn use some debugging tools to help you identify the key culprits. For example, if your query to check if a user has unsubscribed is taking 500ms, that alone would be the reason for the slowness.
Either use the step debugger, a tool like FusionReactor or just turn on verbose debugging output, so you can understand your current bottleneck. It's very possible you have some inefficiencies in some of your SQL lookups (do to missing indexes, etc) that would cause slowness over lots of loops. You might look into using Java libs to read in the Excel file more efficiently. On Saturday, November 15, 2014, Les Mizzell <[email protected]> wrote: > > 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:359646 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
