5 Replies Latest reply on Aug 2, 2007 4:11 AM by Michael

    Insert into DB from the coldfusion query recordset

    ekoucher Level 1
      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
        • 1. Re: Insert into DB from the coldfusion query recordset
          Level 7
          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
          • 2. Re: Insert into DB from the coldfusion query recordset
            Level 7
            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
            • 3. Re: Insert into DB from the coldfusion query recordset
              Dan Bracuk Level 5
              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
              • 4. Re: Insert into DB from the coldfusion query recordset
                Coos Level 1

                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.
                • 5. Insert into DB from the coldfusion query recordset
                  Michael Level 1
                  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.