Remote SQL Server Insert (Speed issue)

  • Thread starter Thread starter Smartikat
  • Start date Start date
S

Smartikat

Hello,

I have a Text File that I need to insert into a SQL Server 2000's Table on a
remote machine via the internet. The Text File contains about 60000
records, each record is about 100 Bytes long. My connection speed is T1 and
everything is quite secured (i.e. firewall, etc).

I have tried quite a following methods:

1) SQLCommand... with TSQL Insert statement
2) Dataset, create a table, insert data, SQLDataAdapter.Update

I have no problem getting the data there, but the problem is the Speed. I
am, at best, getting about 400 Records per min. i.e. A simple upload to
the SQL server from my system here takes over 2 hours. Which means, a tiny
6MB Text file will take over 2 hours to insert to a Remote SQL Server. I
cannot upload the file to the server first then do the insert (at least, not
easily.) Is there any other faster method?

Thanks,

Alex
 
BCP/DTS. ADO is not designed to handle bulk data (until 2.0).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Can you run an application (say a web service) on the same machine as the
database? Something that could accept the request and store the data for
you?
 
I am trying to prevent that. One too many things to deploy already. But
from the look of it, it is the only solution.
 
Smartikat said:
I have tried quite a following methods:

1) SQLCommand... with TSQL Insert statement
Click to expand...

Not sure if you've tried this, but it's worked great for me... You can
batch inserts into a SQLCommand by separating each statment with a
semi-colon. You easily be able to batch a 1,000 inserts at a time.
There are some drawbacks to this method, but it's very fast.

Scott
 
Bulk copy or DTS is far faster... but this is not a bad approach for many
complex applications.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top