[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

cfloop running slow and timing out

Hi all,
I have a cfloop that is timing out after it reaches 15,000 rows. I have the timeout setting to over 400 in cf admin. Any ideas?


<cfoutput>

<cffile action = "read" file = "inventoryupdate.csv" variable = "Data">

<cfloop list="#data#" delimiters="#chr(10)##chr(13)#" index="Items">

<cfset Array = ArrayNew(1)>
<cfset Array = ListToArray(Items)>

      <cfquery datasource="rmnew" >
            Update db1_products
            Set SKU = '#TRIM(Array[1])#',
            list_price = #TRIM(Array[3])#,
            price = #TRIM(Array[4])#,
            item_price_level3 = #TRIM(Array[5])#,
            item_price_level4 = #TRIM(Array[6])#,  
            item_price_level5 = #TRIM(Array[7])#,
            item_price_level6 = #TRIM(Array[8])#,
            item_price_level7 = #TRIM(Array[9])#
            where SKU = '#TRIM(Array[1])#'
      </cfquery>

      <cfquery datasource="rmnew" >
            Update db1_products_ext
            Set item_available_qty = '#TRIM(Array[2])#'
            where ID = '#TRIM(Array[1])#'
      </cfquery>


#TRIM(Array[1])# - #TRIM(Array[2])# - #TRIM(Array[3])# - #Array[4]# - #Array[5]# - #Array[6]# - #Array[7]# - #Array[8]# - #Array[9]#<BR>
<cfflush>
</cfloop>
</cfoutput>
0
bluskyGuy
Asked:
bluskyGuy
  • 4
  • 2
  • 2
  • +1
1 Solution
 
INSDivision6Commented:
You may set an option in CF Admin not to time-out at all, if you wish.

What is your database? If it is MS SQL Server, you could use BULK INSERT to do this in one shot (and really fast).
0
 
bluskyGuyAuthor Commented:
Yea, I don't want to set no time out since I don't want other clients scripts to take over processes/resources indefinitely. We're currently running on mySQL.

0
 
INSDivision6Commented:
Well, another solution would be to split this loop in parts and insert only certain number of rows in one call to avoid time-out.
0
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
INSDivision6Commented:
Also, I would suggest to split the initial file into an array by chr(13).  To avoid looping thru a list.  The split will take some time, but the loop itself will work faster.
0
 
bluskyGuyAuthor Commented:
Yup, that's a good idea....how would I do that with the existing syntax?
0
 
INSDivision6Commented:
Create a processing page that takes the file name (or how you do it now) plus startRow and rowNum (or something like this).  So, the page reads the file and processes rowNum rows starting from startRow.  Now you need to launch approriate number of pages ("threads").  If you under Windows, the best solution is to use this tag:

http://www.cftagstore.com/tags/cfxhttp5.cfm

If you are not under WIndows, you can use CFHTTP to start "detached" threads (no resut will be returned back to the caller).  For this, you need to use a small timeout value in CFHTTP (like 1-2 sec), ignore (trap) error message, and launch another "thread", until you launch them all.  Even CFHTTP is erroring-out, the page you call will continue to execute.  Therefore, you will have a few pages making inserts simultaneously.  This should reduce overall time needed to do the job.  Yes, it is a little bit ugly, but it works.
0
 
pinaldaveCommented:
<cfsettings timeout="120000(very high value)">
On the top of the page will override the admin settings.
you can use that.
Regards,
--Pinal
0
 
Tacobell777Commented:
you might want to remove

#TRIM(Array[1])# - #TRIM(Array[2])# - #TRIM(Array[3])# - #Array[4]# - #Array[5]# - #Array[6]# - #Array[7]# - #Array[8]# - #Array[9]#<BR>
<cfflush>

I dont think a browser can handle the output of 15,000 rows, actually I am pretty possitive it can't handle it.
0
 
Tacobell777Commented:
how often do you import this file?
You might be better of creating a datasource for it.

if you had MS SQL installed or access to the drivers you could use OPENQUERY
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now