CFloop Processing Extremely slow

Question

I am teaching myself CF Development and am using a shared CF Hosted Site for testing as I learn.

I have a spreadsheet containing 2500 plus records of data, in about 15 columns. The spreadsheet contains data critical to the basis of this application. It is maintained offline and periodically uploaded to the server using cffile. After the upload, the spreadsheet is imported into a MySQL table, line by line.

The problem is it's taking entirely too long. For example, it is currently running now. In the last 43 minutes, only 192 of 2500 records have been added to the MySQL table. Is there a more efficient way to do this?

If you review the code below, inside the cfloop it runs the cfspreadsheet tag, and retrieves the current line. Then adds that line as "CSV" data to the MySQL table.

Any suggestions on how to do this more efficiently?

<cfloop index="RecordNumber" from="2" to="#Records#" step="1"> 

    <cfspreadsheet action="read"
                format="csv"
                name="siteinfo"
                src="#floc#"
                headerrow="1"
                rows="#RecordNumber#" 
                columns="1-7,15,16,22,23"
                >

    <cfset ColumnCount = ListLen(siteinfo)>
    <cfset CSVSiteData = #siteinfo#>
    <cfset EntryType = "Site_Data">


    <cfquery name="Insertsite" datasource="TechData">
        INSERT INTO sitelist (EntryType, CSVSiteData, DateInserted)
        VALUES ('#EntryType#', '#CSVSiteDataf#', '#DateInserted#')
    </cfquery>

</cfloop>

score 1 · Answer 1 · answered Sep 13 '14 at 23:47

1

You're reading the entire XLS file every iteration, just to fetch a single row. Just read the file once (before the loop), then loop over its rows.

answered Sep 13 '14 at 23:47

Adam Cameron

29,677
4
37
78

score -1 · Answer 2 · answered Sep 15 '14 at 00:51

Yes'r, was trying to find a way to keep from opening the spreadsheet each pass, This fixed it... Much faster now..

<body bgcolor="#0033FF">
    <cfset filenm = "SitesTest.xlsx">
    <cfset thisPath = GetDirectoryFromPath(ExpandPath("*.*"))>
    <cfset uploaddirectory = "#thisPath#Uploads\">
    <cfset uploadfile = "#uploaddirectory##filenm#">
    <cfset floc = "#uploadfile#">






<!--- --->
<!--- --->
<!--- --->


<!--- Read Spreadsheet Count number of Rows --->

<cfspreadsheet action="read"
                src="#floc#"
                headerrow="1"
                query="SiteSS"
                >

<cfset Records = "#SiteSS.RecordCount#">                
<!--- Get Header Row from Row 1 --->
<cfspreadsheet action="read"
                format="csv"
                name="ColumnHeaderRow"
                src="#floc#"
                headerrow="1"
                rows="1" 
                columns="1-7,15,16,22,23">

<cfset Headeritems = ListLen(ColumnHeaderRow)>

<cfset csvallVar = "">
<cfset CSVSiteData = "">


<cfspreadsheet 
        action = "read" 
        src="#floc#" 
        query="siteinfo" 
        rows="1-#Records#" 
        columns="1-7,15,16,22,23" >



<cfset rowcount = 2>

<cfloop Query="siteinfo" startrow="2" endrow="#Records#" > 

<cfset EntryType = "SiteInfo">

<!--- Create Line Item Lists--->
<cfset CSVSiteData = "#siteinfo.col_1#, #siteinfo.col_2#, #siteinfo.col_3#, #siteinfo.col_4#, #siteinfo.col_5#, #siteinfo.col_6#, #siteinfo.col_7#, #siteinfo.col_8#, #siteinfo.col_9#, #siteinfo.col_10#, #siteinfo.col_11#">

<cfset CSVSiteDataCheck = "#siteinfo.col_1#, #siteinfo.col_2#, #siteinfo.col_3#, #siteinfo.col_4#, #siteinfo.col_5#, #siteinfo.col_6#, #siteinfo.col_7#, #siteinfo.col_8#, #siteinfo.col_9#, #siteinfo.col_10#, #siteinfo.col_11#">

<cfset Manager = #siteinfo.col_1#>
<cfset Market = #siteinfo.col_2#>
<cfset FALocationCode = #siteinfo.col_3#>
<cfset USID = #siteinfo.col_4#>
<cfset CommonID = #siteinfo.col_5#>
<cfset EquipmentName = #siteinfo.col_6#>
<cfset EquipmentType = #siteinfo.col_7#>
<cfset PrimaryTech = #siteinfo.col_8#>
<cfset Address = #siteinfo.col_9#>
<cfset LatitudeDecimal = #siteinfo.col_10#>
<cfset LongitudeDecimal = #siteinfo.col_11#>



<!------>

<cfquery name="CheckDup" datasource="TechData">
    select CSVSiteData From sitelist2
    where CSVSiteData = '#CSVSiteDataCheck#'
</cfquery>


<cfif CheckDup.recordcount lt 1>

<cfquery name="Insertsite" datasource="TechData">
    INSERT INTO sitelist2 (
                            EntryType, 
                            CSVSiteData,
                            Manager, 
                            Market, 
                            FALocationCode, 
                            USID, 
                            CommonID, 
                            EquipmentName, 
                            EquipmentType, 
                            PrimaryTech, 
                            Address, 
                            LatitudeDecimal, 
                            LongitudeDecimal                            
                            )
    VALUES (
            '#EntryType#', 
            '#CSVSiteData#', 
            '#Manager#', 
            '#Market#', 
            '#FALocationCode#', 
            '#USID#', 
            '#CommonID#', 
            '#EquipmentName#', 
            '#EquipmentType#', 
            '#PrimaryTech#', 
            '#Address#', 
            #LatitudeDecimal#, 
            #LongitudeDecimal#
            )
</cfquery>



"Record <cfoutput>#rowcount#</cfoutput> added"<br />
<cfelse>
"Record <cfoutput>#rowcount#</cfoutput> not added"<br />
</cfif>




<cfset rowcount = rowcount + 1>
</cfloop>






</body>

*data critical to the basis of this application* Then you might consider using a different approach, as the above it brittle. Inserting into a work table using `LOAD DATA`, would be faster and offer greater control over the import process. As an aside, always use `cfqueryparam` with query parameters. In addition to protecting against sql injection, it helps boost performance when executing the same query multiple times. — Leigh, Sep 16 '14 at 23:29
Also are you storing a list of values in a *single* column, ie "CSVSiteData"? Storing delimited data is almost never a good approach. — Leigh, Sep 17 '14 at 17:17

CFloop Processing Extremely slow

2 Answers2