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

Yin He,

Are you using autoincrement. This means twice as much instructions in the
tableadapter as by instance using an uniqueidentifier.

Just as idea

Cor

Does SqlBulkCopy help?

Show quoteHide quote

Yes
I finally found the SqlBulkCopy class in MSDN, it improves the speed greatly.
Now the 2M bytes file uploading only costs less than 2 second.



Show quoteHide quote

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
__________________________________________________________


Show quoteHide quote

Using Typed Dataset with Winform vs ASP.NET
Timeout expired - on a simple SqlDataReader:Read() call
DAAB ExecuteReader method causes Exception
What is the quickest/most efficient way to insert 100,000 Rows
Advatages of Stored Procedures?