• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

Inserting records into DB thru Coldfusion Suggestion required

Hi,
I am trying to insert around 2000 record into DB from coldfusion web page but facing with a  time out error.

Can anyone pls help with workaround or suggestion for this issue.
0
new_perl_user
Asked:
new_perl_user
1 Solution
 
gdemariaCommented:
Set the timeout on your page...

<cfsetting requestTimeout = "60000">

0
 
new_perl_userAuthor Commented:
I am new to cold fusion so my question may be silly but..

Is 60000 secs or mins and what is the maximum time we can allow.
0
 
gdemariaCommented:

Glad you asked, I thought it was milliseconds, but it's actually seconds.

So a setting of 600 should be 10 minutes, 1800 is 30 minutes.

0
 
_agx_Commented:
no points ... gd already answered your original question.

Just curious. What are you inserting, a CSV file? What's your db type?
0
 
hyperfuseCommented:
Another tipp:
If you insert a lot of records into the same database table, you can improve performance a lot by using aggregative sql script:
Instead of looping over a list or query and inserting one record one after the other with seperate cfquery tags within the loop, it is much quicker to insert a bunch of records within a single transaction. Due to buffering issues at the database you should limit the number of records to e.g. 200 to 500 recs per transaction (=cfquery tag).

The correct syntax depends on your database, here is a sample for mySQL:
<cfquery name="dummy" datasource="yourdbname">
INSERT INTO tablename (fieldname1,fieldname2) VALUES 
 ('f1value1','f2value1'),
 ('f1value2','f2value2'),
 ('f1value3','f2value3')
;
</cfquery>

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now