Insert into DB from the coldfusion query recordset

[ekoucher]

Hi,

I need to insert to the DB from CF query recordset.
The simplest way to do is using cfloop on the query and the INSERT INTO TABLENAME within the loop.
It is working, but it's creating multiple insert SQL statements and it is very match slow. Some times, if the records are more then thousands, it giving the query timeout error.
How else I can accommodate this task to make it faster?

Thanks in advance
Ed

[Newsgroup_User]

depending on your database, some derivation of the
INSERT INTO ...
SELECT FROM ...
sql should be alot faster
HTH
--
Tim Carley
www.recfusion.com
info@NOSPAMINGrecfusion.com

[Newsgroup_User]

yes, have your db do all the work instead of cf - that's what db's were
made for in the first place...

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[Dan_Bracuk]

If it's all in the same db, the easiest way is to not insert anything. Just use the data you have.

The next easiest way, if it's all in the same db, is like this:
insert into yourTable
(list_of_fields)
select some_stuff
from etc

If they are from different dbs, and you really need the data in both places, you can try this. It may or may not be faster than what you have now.

<cfquery>
<cfloop query="somequery">
insert into yourtable
(field1,field2,etc)
values
select distinct #value1#, #value2#, etc
from some_small_table
<cfif currentrow is recordcount>
union
closing tags

[Coos]

There's a few ways to insert 1000+ records.
1. This way is more db intensive but you can put db transaction and cftry/catch blocks to deal with errors.
<cfloop>
<cfquery name="i">
INSERT INTO TABLENAME columnname
VALUES (columnvalue)
<cfquery>
</cfloop>

2. This way uses less network traffic and only 1 cfquery statement but it may be harder to catch errors depending which db you're using.
<cfquery name="i">
<cfloop>
INSERT INTO TABLENAME columnname
VALUES (columnvalue)
</cfloop>
<cfquery>

3. Do one of the above but use CFQUERYPARAM for the column value. This tells the db datatype of the input parameter which can potentially make things faster. I think you can use this tag in an insert statement.

4. Create a stored procedure for the insert. Problem is that you can only do 1 insert at a time.

Using #2 with cfqueryparam is probably the quickest.

For the query timeouts, you can increase the CFQuery Timeout attribute (in seconds).
If you get a loop timeout, you can adding the <cfsetting requesttimeout="numberofseconds"> tag to the top of the page.

You may also want to look for insert triggers on the table you're working with. Maybe there's some other work being done on every insert that's contributing to the slowdowns.

[Michael8]

Disable any indexes on the table into which you are inserting the records.

The DB must update the indexes each time you insert a record. When dealing with thousands of records, that also includes thousands of index inserts, as well.

Then, after your import is complete, enable your indexes again.