Solved

The request has exceeded the allowable time limit Tag: CFQUERY

Posted on 2007-03-30
17
7,198 Views
Last Modified: 2011-02-11
I am running a script to upload about 50,000 lines into my DB with Coldfusion and after 2 mintues or so I get this error:

 The request has exceeded the allowable time limit Tag: CFQUERY

How can I fix this ?
0
Comment
Question by:ostashenp
  • 8
  • 6
  • 3
17 Comments
 
LVL 2

Author Comment

by:ostashenp
ID: 18824819
The error came around line 300 and during this test I only entered 1000 records but 700 never made it.
Let me know if you need any code examples.
0
 
LVL 5

Expert Comment

by:NickVd
ID: 18825514
I don't know the first thing about coldfusion, but if it's anything like PHP then the scripts are allowed to run for a certain amount of time before being stopped by the system.  In PHP, there is a  set_memory_limit() function that allows the programmer to increase the time allowed (or if used in a loop, to constantly run forever).  There may be something similar in CF.

Hope this helps, or at least leads you in the right direction.
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18825606
Ok but here is my next question.

If that is the case how does a programmer work around that.  I have to add about 50,000 entries to my DB a day with some sort of script...Anyone have any ideas.
0
 
LVL 5

Expert Comment

by:NickVd
ID: 18825809
Try putting this at the top of your page:

<cfsetting requesttimeout="3600">

Set the time to be whatever you want in seconds.
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18825865
That does not work, no matter what value I put in there it still seems to timeout.  
0
 
LVL 5

Expert Comment

by:NickVd
ID: 18826657
Then you will have to figure out how to optimize your query.  A slightly slow query will be a very slow query when run 50,000 times :)
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18835602
Thats what I ended up doing, I took a huge insert chunk out of it.
0
 
LVL 1

Expert Comment

by:dl8453
ID: 18836064
Can you break the upload in segments (something similar to pagination)?  You could do a query.recordcount and divide that by some preselected number (50000/200).  The result would be how many "paginations" your loop would need.  

What are you retrieving the 50k records from (flatfile, access database)?  Are you running subqueries?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Author Comment

by:ostashenp
ID: 18839447
It is a text file that I get over a URL.
0
 
LVL 1

Expert Comment

by:dl8453
ID: 18839533
Can you use cfftp to retrieve the file? If so, you can ftp the file directly to the db server and then run the query to insert your data directly into the server via the CFM page and that should run very quickly.

Does the file name change or is it constant?  You may need to ask the peple providing the data to move it to an FTP server.  This sounds like a major undertaking you are doing so I'm sure they will work with you on this.  
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18840172
The filename never changes and is always at the same URL. I tried using cfhttp but it failed becuase it was to much info.  So then I would manually FTP it to my server then run the upload but it kept saying the query was to large and it would fail.  Yes this is a HUGE project im stuck in the middle of.
0
 
LVL 1

Expert Comment

by:dl8453
ID: 18844339
I've cfftp'd close to 500mb flatfile (txt format) without a hitch and inserted that data into an SQL table so this is odd.

Is your cfftp (not cfhttp) working?  If you need some help with this, I can post the cfftp routine I use.

Can you paste what two or three records would look like so we can see the format (include what the delimiter is)?  Also, what db are you using?  And what version of MX?  Lastly, do you have any CF code you can post for the insert?  
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18875464
It is just a flat text file and the size is about 50 MB and I am using SQL server 2000 with Dreamweaver 8 (But that does not really matter becuase I cannot get it to work interactively with  CF and the database so im basicly using it as a color coded editor.

Here is what 2 lines of the file would look like:

9343|Restaurant Assistant Manager|Houston|Texas|LOVE'S TRAVEL STOPS & COUNTRY STORES|Management|URL HERE ~
3455|Paralegal|New York City|New York|In Confidence|Legal|URL HERE ~


This might be very inefficient BUT I have to make sure the company exists in the company table before the job is added, here is my entire script:

<CFSET crlf = "~">
<CFLOOP INDEX="orec" LIST="#datavar#" DELIMITERS="#crlf#">

<cfif len(trim(orec)) >

<cfquery name="getjobnumberInfo" datasource="test" dbtype="ODBC">
      select max(jid) as NaxNum from Jobs
</cfquery>




<CFSCRIPT>
         Jobnumber = ListGetAt(orec, 1,"|");
         Jobtitle = ListGetAt(orec, 2,"|");
         Location = ListGetAt(orec, 3,"|");
         State = ListGetAt(orec, 4,"|");
         Company = ListGetAt(orec, 5,"|");
         Jobfield2 = ListGetAt(orec, 6,"|");
         Website = ListGetAt(orec, 7,"|");
</CFSCRIPT>
                 
      <cfparam name="CBia" default="N">
      <cfparam name="Experience" default="">
      <cfparam name="Referred" default="">
      <cfparam name="Single" default="J">
      <cfparam name="CBIA" default="N">
      <cfparam name="exp_date" default="">
      <cfparam name="eresumes" default="N">
      <cfparam name="send_resumes" default="0">
      <cfset dateentered = CreateODBCDate(now())>
      <cfset timeentered = CreateODBCtime(now())>
      <cfset Moddate = CreateODBCDate(now())>
      <cfset Modtime = CreateODBCtime(now())>
      <cfset exp_date = CreateODBCdate(dateadd('d',+60,now()))>
      <cfset Temp = Replace(getjobnumberInfo.NaxNum, 'J', '', 'ONE') + 3000 >
      <cfset jobnumber = "J#Temp#" >
      <cfset login = "login">
      <cfset pw = "ocjobs">
      <cfset topusa = 1>
        <cfset CTDue = CreateODBCdate(dateadd('d',+60,now()))>
        <cfset CTPackage = "30">
        <cfset CTRenewed = CreateODBCdate(dateadd('d',+55,now()))>
        <cfset OSJ = "Y">
        <cfset OSR = "N">
        <cfset CBIA = "N">
        <cfset CTR = "N">
        <cfset CTVis = "Y">
        
        
<cfif (state) is "District of Columbia">
<cfset state = "Maryland">
</cfif>
<CFQUERY name="getCounties" DATASOURCE="test" dbtype="odbc">
Select Counties
From Counties
Where Counties like '%#state#%'
</CFQUERY>

<cfquery name="getClients" datasource="test" dbtype="ODBC">
      select Company
      from Clients
      where Company = '#Company#'
</cfquery>


<cfif getClients.recordCount eq 0>

<cfquery name="insertCompany" datasource="ocjobs" dbtype="odbc">
INSERT INTO Clients
(City,Company,CTDue,CTPackage,CTR,CTRenewed,CTVis,DateCreated,DateMod,Login,OSJ,OSR,Password,Referred,State,TimeCreated,TimeMod,Single,CBIA)
VALUES('#Location#','#Company#',#CTDue#,'#CTPackage#','#CTR#',#CTRenewed#,'#CTVis#',#dateentered#,#Moddate#,'#Login#','#OSJ#','#OSR#','#PW#','#Referred#','#State#',#timeentered#,#Modtime#,'#Single#','#CBIA#')
</cfquery>
</cfif>
                 
      <CFQUERY name="insertJobs" DATASOURCE="test" dbtype="odbc">
         INSERT INTO JOBS (CBia,Company,jobnumber,City,State,location,JobTitle,topurl,JobField2,County,login,pw,single,dateentered,timeentered,Moddate,Modtime,eResumes,send_resumes,exp_date,topusa)
         VALUES ('#CBia#','#Company#','#jobnumber#','#location#','#state#','#location#','#JobTitle#','#website#','#jobfield2#','#ValueList(getCounties.Counties)#','#login#','#pw#','#single#',#dateentered#,#timeentered#,#Moddate#,#Modtime#,'#eResumes#','#send_resumes#',#exp_date#,'1')
      </CFQUERY>
   </cfif>
</CFLOOP>
0
 
LVL 1

Accepted Solution

by:
dl8453 earned 500 total points
ID: 18875633
As for the version of MX, I meant CF :) sorry about that.  :)

From what I see here, if you have 50k+ records, then you are really banging the heck out of your CF server by running the query inside the loop.  I'm no king of efficiency myself but I cannot imagine many servers can afford that many 'hits.'  You may want to try something like:

<hr />
If you cannot rewrite your query/insert statements, my suggestion would be to:
<li>read the input data (50mb+ flat file)</li>
<li>perform your error checking (e.g. checking that a company exists)</li>
<li>properly format it</li>
<li>write the clean data it to a tempFile on the server</li>
<li>perform your inserts based on the new tempFile</li>
Your read/insert would not need to do any error checking since you did that before the new tempFile was created.  You could take it a step further and set the tempFile name to tempFile_#(dateformat(now(), 'yyyymmdd')#.txt and this could give you a historical data too (just be sure to delete files older than 20 days or something since you get updates daily).  
0
 
LVL 1

Expert Comment

by:dl8453
ID: 18875655
Sorry, left out th equery to try...

INSERT INTO clients
(CBia,Company,jobnumber,City,State,location.......)
            SELECT distinct
            CBia,Company,jobnumber,City,State,location......
                           FROM table

and then repeat for JOBS insert using applicable query.
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18882604
Thank you for all of your help I have it working perfectly now !!!
0
 
LVL 1

Expert Comment

by:dl8453
ID: 18882688
I'm glad to hear.  Would you mind sharing your results (remove any proprietary data) if you can?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now