Re: Inserting large number of records are too slow

Yin He,

2M log file ~ 50 seconds ...

That says very little on what may be affecting your performance. It could be
the table structure, other dependencies on the table - constraints/triggers,
the CPU speed, network traffic/bandwidth etc.

Luckily you are using .NET 2.0 so you have a couple of faster options than
DataAdapter.UpdateBatchSize.

DataAdapter.UpdateBatchSize simply tricks TDS into sending multiple queries
between the same endmarker and begin marker. There is a deeper explanation
of it in my book. But the important point to consider is, even though you
are saving on network roundtrips, you are still spending time on sending and
executing each query one by one.

The two ways faster than this are -

a) SqlBulkcopy .. and
b) SSIS

I'd recommend giving these a try.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________


"Yin He" <YinHe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1C11D1EB-C285-4C3B-9239-51644A18EDA7@xxxxxxxxxxxxxxxx
I am developing a tool to upload huge log files into SQL Server 2005.
The method I use is DbDataAdapter.Update() and I also set the
UpdateBatchSize property to 5000.
Wen I upload a 2M bytes log file, which contains about 30000+ records, to
the SQL server 2005, it costs about 50 seconds.
The speed is unacceptable.
Is there any suggestions to improve the speed?
Thanks.

BTW, the language I use is C#.



.