• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • 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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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