Inserting large number of records are too slow

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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



Miha Markic said:
Does SqlBulkCopy help?

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Yin He said:
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#.
Click to expand...
Click to expand...
 
Back
Top