reading excel and inserting into database

Question

i am using the following code to handle the insets: but it is taking too much time, how can i enhance it or make in work faster:

<cfspreadsheet action="read" excludeheaderrow="yes" headerrow="1" rows="2-65536" query="mySheet" src="#dest#\#newfile#" sheet="1">
      <cftry> 
      <cfquery datasource="#request.dsn#" name="myInsert">
      <cfoutput>
      <cfloop query="mySheet">
        INSERT INTO mytable(memberID,lastname,firstname,pid,pname,gender,dob,workphone,homephone,address1,address2,city,county,state,zip,marketValue) 
        values('#mySheet.mem_id##RandRange(1,100)#','#mySheet.MEM_LAST_NAME#','#mySheet.MEM_FIRST_NAME#','#mySheet.PCP_ID#','#mySheet.pcp_name#','#mySheet.gender#','#mySheet.dob#','#mySheet.WORKTELEPHONENUMBER#','#mySheet.HOMETELEPHONENUMBER#','#mySheet.ADDRESSLINE1#','#mySheet.ADDRESSLINE2#','#mySheet.CITY#','#mySheet.COUNTY#','#mySheet.state#','#mySheet.zip#','#mySheet.GROUPNAME#')
      </cfloop>
      </cfoutput>
       </cfquery>

records in excel sheet are more than 50000

Does anything else happen on the page request after the data is inserted? — Dan Bracuk, Jun 28 '13 at 12:01
yes, it displays the total records inserted and a log is build for failed ones — Regual, Jun 28 '13 at 12:41
Well, Update here, Solved with POI Utility, but wondering doesn't Cfspreadsheet needs to be faster than Custom POI utility by Ben Nadel — Regual, Jun 28 '13 at 12:41
This has nothing to do with your question but .. 1) `randRange` is not guaranteed to be unique. If you are using it to ensure `memberID` is a unique value - it is not a good choice. 2) You should definitely be using `cfqueryparam`. One of its benefits is enhancing perform when executing the same sql statement within a loop. — Leigh, Jun 28 '13 at 17:35
ok, so using cfquery could have worked the same way, if i had used no loop, if is that so, i will do it right away and post the results the time it took to implement the both things: If RandRange is not a good choice, Then what CreateUUID or GenerateSecretKey or something else, i would like to explore more on this: Another thing: [Don't get me wrong] One of my Colleagues told me using excessive cfqueryparam slows the processing, is that true — Regual, Jul 02 '13 at 07:28
The simplest option is an identity column. Let the db do the work. (Do not use generateSecretKey. It is designed for a different purpose). *RE: excessive cfqueryparam slows the processing* No. It generally improves performance through the use of bind variables. However, there are a few exotic cases where using it may decrease performance: http://stackoverflow.com/questions/10543755/slow-query-with-cfqueryparam-searching-on-indexed-column-containing-hashes * http://stackoverflow.com/questions/17574276/how-can-cfqueryparam-affect-performance-for-constants-and-null-values/17582859#17582859 — Leigh, Jul 22 '13 at 15:18

score 0 · Answer 1 · answered Aug 06 '13 at 22:30

Here's one way to handle it:

Put the entire thing (cfspreadsheet and all inserts) into a threaded function so it can run in the background
Save the spreadsheet query (upload results) into a session variable
After the upload is complete, do your insert queries in batches; in each batch add any issues into a feedback array that is also in the session scope
On the user's end, have a page that tells the user that things are "Working..." and re-loads and displays the feedback issues as they come up (best to do with ajax)

reading excel and inserting into database

1 Answers1