A faster way to do this Insert

[Newsgroup_User]

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>

[Dan_Bracuk]

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.

[MikerRoo]

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.)

[Newsgroup_User]

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.)
>
>

[MikerRoo]

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.

[Newsgroup_User]

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.
>
>

[MikerRoo]

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.

[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.

[Newsgroup_User]

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.
>
>