8 Replies Latest reply on Dec 20, 2006 1:29 PM by Newsgroup_User

    A faster way to do this Insert

    Level 7
      Is there a faster way to do the insert below. The text file in question is
      15mb and the code below takes approximately 5 minutes to execute.

      Bare in mind the following conditions;

      1) I am in a hosted web environment using MX7 and a MS Sql database that
      reside on different boxes. Therefore I cannot simply use a DTS
      wizzard/package to import the file.
      2) I cannot get Bulk Insert permisions granted on my SQL hosting.
      3)The text file is downloaded daily and FTP'd over to a folder on my web
      server. I do not have a local MS SQL dataabse running 24/7 to run a DTS
      package locally to complete the task.
      4) Im hoping to automate the task at least 3 times a day by scheduling in
      coldfusion



      <cfsetting requesttimeout="420">

      <cfhttp method="get" textqualifier=" " firstrowasheaders="no" delimiter="|"
      username="xxxxxx" password="xxxxxxxxx" name="test"
      url=" http://208.106.197.112/prms/ordhist1xx.txt">

      <cfloop query="test">
      <cfquery datasource="development" username="xxxxxxxx" passWord="xxxxxxxxx">
      INSERT INTO order_history
      (OrderNo, Line, seq, AccountNo, Shipment, Invoice, PurchaseOrder, Completed,
      OrderDate, ShipmentDate, Backorder, Product, QtyOrdered, QtyShipped,
      QtyInvoiced, Price, Carrier, Connote, Delivery1, Delivery2, Delivery3,
      Delivery4, Delivery5, Delivery6, Delivery7, expected)
      VALUES
      ('#test.column_1#', '#test.column_2#', '#test.column_3#', '#test.column_4#',
      '#test.column_5#', '#test.column_6#', '#test.column_7#', '#test.column_8#',
      '#test.column_9#', '#test.column_10#', '#test.column_11#',
      '#test.column_12#', '#test.column_13#', '#test.column_14#',
      '#test.column_15#', '#test.column_16#', '#test.column_17#',
      '#test.column_18#', '#test.column_19#', '#test.column_20#',
      '#test.column_21#', '#test.column_22#', '#test.column_23#',
      '#test.column_24#', '#test.column_25#', '#test.column_26#')
      </cfquery>
      </cfloop>


        • 1. Re: A faster way to do this Insert
          Dan Bracuk Level 5
          If I'm not mistaken yet again, ms sql allows more than one sql statement inside a cfquery tag. That being the case, try putting your loop inside the query instead of the query inside the loop.

          Another way, that would work with pretty well any db, but is not necessarily faster is to do a union query with a loop. It would look something like this:

          <cfquery>
          insert into yourtable
          (yourfields)
          <cfloop>
          select distinct yourvalues
          from some_small_table
          <cfif you are not finished your loop>
          union
          </cfif>
          </cfloop>

          As an aside, if all your fields are char/varchar, this insert is the least of your problems.
          • 2. Re: A faster way to do this Insert
            MikerRoo Level 1
            Both DTS and bcp.exe can see a UNC directory on your web server. All you need to do is make sure there is read permission (on by default).

            Or, you could load bcp onto your web server and it can see the SQL server just like coldfusion can.

            There is no real reason NOT to use SQL for this and, as you see, it's painful to do otherwise. (Don't use a scalpel where a shovel will do.)
            • 3. Re: A faster way to do this Insert
              Level 7
              Mike I've tried so many times to create a DTS package using the text file
              inport connection without success.

              My unc path never seems to be able to connect. Ive tried the following
              paths. It has read access as you can access it through a browser;

              //208.106.197.112/test.txt

              the file is on the root of my web server so it has read access.

              The only thing I can think of is that my sql is in a hosted environment.
              Maybe to connect via the UNC I need to have special rights. the database
              login I use currently has access_admin rights

              Any ideas?



              Through the
              "MikerRoo" <webforumsuser@macromedia.com> wrote in message
              news:em7t6i$hit$1@forums.macromedia.com...
              > Both DTS and bcp.exe can see a UNC directory on your web server. All you
              > need
              > to do is make sure there is read permission (on by default).
              >
              > Or, you could load bcp onto your web server and it can see the SQL server
              > just
              > like coldfusion can.
              >
              > There is no real reason NOT to use SQL for this and, as you see, it's
              > painful
              > to do otherwise. (Don't use a scalpel where a shovel will do.)
              >
              >


              • 4. Re: A faster way to do this Insert
                MikerRoo Level 1
                Is your web server unix, or windows?

                If unix, you need to set up a dos share.
                If windows, that is not a valid path. Something like \\208.106.197.112\C\webroot\test.txt would be used.

                In order to see this, SQL server should be running under a user account (NOT local system) and/or must be configured to "allow desktop interaction".

                If you cannot do any of that, copy bcp.exe (and any needed DLL's) to your web server and use bcp to import the file.
                • 5. Re: A faster way to do this Insert
                  Level 7
                  Thanks for your help Mike I'll give that a go!

                  I didnt realise the UNC path had to have the physical path included.


                  "MikerRoo" <webforumsuser@macromedia.com> wrote in message
                  news:em9o1d$pfc$1@forums.macromedia.com...
                  > Is your web server unix, or windows?
                  >
                  > If unix, you need to set up a dos share.
                  > If windows, that is not a valid path. Something like
                  > \\208.106.197.112\C\webroot\test.txt would be used.
                  >
                  > In order to see this, SQL server should be running under a user account
                  > (NOT
                  > local system) and/or must be configured to "allow desktop interaction".
                  >
                  > If you cannot do any of that, copy bcp.exe (and any needed DLL's) to your
                  > web
                  > server and use bcp to import the file.
                  >
                  >


                  • 6. Re: A faster way to do this Insert
                    healey_mark
                    If the DTS package stuff doesn't work, you should get a boost to your existing statment if you use the cfqueryparam tag for each of your VALUES(). CFQUERYPARAM permits the query to be compiled and reused (so prepared once and executed many times) instead of doing a prepare/execute for each iteration of the loop.

                    So your query would be something like:

                    INSERT INTO order_history( OrderNo, Line, ... )
                    VALUES( <cfqueryparam type="numericr" value=#test.column1>,
                    <cfqueryparam type=...>,
                    ...etc with one cfqueryparam for each of the values...
                    )

                    etc. Read the coldfusion docs on cfqueryparam for clarification.
                    • 7. Re: A faster way to do this Insert
                      MikerRoo Level 1
                      I figured out a way for DTS to read the file from your web server without any additional setup.

                      Be warned that the method may need adjustment from one machine to the next depending on your versions of MicroCrap DAO, .net, SQL server SP level, etc.

                      That said, the attached version works on my company's typical W2K Advanced Server, MS SQL 2000 SP4, setups.

                      In your DTS job, create an ActiveX task as the first task. Paste the following code in the "properties" window.
                      This task will use http to get the file from your server and then copy it to the temp directory where normal DTS can see it without any other setup.


                      • 8. Re: A faster way to do this Insert
                        Level 7
                        Ok all this info is good.

                        Mike ill give your Active X suggestion a go.

                        Mark I do usually use cfqueryparam but i wasnt aware it's more efficient in
                        a speed sense.

                        Great I'll use that as last resort and see if i get a speed improvemnet.

                        thanks for your help guys


                        "healey_mark" <webforumsuser@macromedia.com> wrote in message
                        news:em9s7d$n9$1@forums.macromedia.com...
                        > If the DTS package stuff doesn't work, you should get a boost to your
                        > existing
                        > statment if you use the cfqueryparam tag for each of your VALUES().
                        > CFQUERYPARAM permits the query to be compiled and reused (so prepared once
                        > and
                        > executed many times) instead of doing a prepare/execute for each iteration
                        > of
                        > the loop.
                        >
                        > So your query would be something like:
                        >
                        > INSERT INTO order_history( OrderNo, Line, ... )
                        > VALUES( <cfqueryparam type="numericr" value=#test.column1>,
                        > <cfqueryparam type=...>,
                        > ...etc with one cfqueryparam for each of the
                        > values...
                        > )
                        >
                        > etc. Read the coldfusion docs on cfqueryparam for clarification.
                        >
                        >