What is the faster way to insert/buck insert rows into SQL Server

I am developing a data center server in C#. We alaready have a legacy system 
that worked with great performance. The old system was unmanaged code in C++ 
and used ODBC driver SQLBindParameter to bind the data in the memory before 
dumping to the SQL Server. In the current C# version, I used the SqlBulkCopy 
to dump rows. However, I found that the performance still cannot compete 
with the old way of ODBC binding. The only operation I need is to append 
rows to tables.

I understand that MS recommens using the SqlClient, which is what I used 
(SqlBulkCopy). But I am still not happy about its performance (comparing to 
the old ODBC way).

Which driver/provider should I use to achieve the highest insert row(s) to 
SQL Server perfomance?
- SQL Server .Net
- OLE DB .Net
- ODBC.Net
- Others?

Thanks.
Hao

I would echo this thread to the microsoft.public.sqlserver.programming 
group.
I would have said SqlClient would be the fastest--that or DBLib. ODBC, and 
OLE DB are OSFA interfaces that have more layers to cross before sending or 
receiving anything from SQL Server.

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Hello Hao.

I agree with William. SqlClient is the fastest way for SQL server database 
in ADO.net.

Bye the way, if you using ADO.net 2.0, I would like to suggest you specify 
SqlBulkCopy.BatchSize. This property is new in the .NET Framework version 
2.0 and indicates the Number of rows in each batch. The default value is 
zero, which means each WriteToServer operation is a single batch.

 http://www.**--****.com/ 
atchsize.aspx
[SqlBulkCopy.BatchSize Property ]

Hope this helps. Please let me know if you face any further issue on this. 
I'm glad to assist you.

Have a nice day,
Sincerely,
Wen Yuan
Microsoft Online Community Support 
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hao,

Probably you will with your small program never reach the time with managed 
code as with unmanaged code. The same that you never can reach the 
performance, for small programs as you describe,  with C++ as you would have 
done it in Intel Assembler. However has a little bit more to do, than only 
to write to the SQLserver, you will see that you will most probably win it 
in the overall time. Let us not speak about maintainability because than you 
will win it with your C# in miles.

Just my thought,

Cor

"Hao" < XXXX@XXXXX.COM > schreef in bericht 

Thanks for all the replies.
So far, my plan is to develop some buffer to utilize the BatchSize. There 
are challenges to having buffer implemented on the server side because of 
the amount of the data (hundreds of thousands of columns).

The product I am architecturing has a few large customers (data centers), 
such as eBay. The amount of data is huge. The old version of the software is 
working fine with archieving 3 to 4 GB data every day using ODBC and SQL 
binding, not to mention many other networking operations. The data block was 
constructed and bound before sending to the database. That is why the 
performance is fast.

In .Net bcp operation, I can buffer data to 5 rows, but I cannot buffer data 
for many more rows (like 30) because that will blow the memory (with the 
huge amount of columns in hundreds of tables). Fortunately, my testing shows 
that if I buffer 5 rows before BCP, I can probably achieve the task by 
requiring a high end server machine. I was hoping to see if there is any 
other "low level" or more "native" way in .Net to append data to SQL Server.

The perforamance of .Net really worries me a lot. We've spent a year in 
re-writing the server code in .Net but failed in overall performance. Now we 
are re-architecturing the entire system, and we are still not happy about 
the database perforamnce.

Does anyone know any serious networking or server application developed in 
.Net by Microsoft or other venders?

Thanks again.
Hao

You might want to try out our SpeedyDB ADO.NET Provider.  It has two 
components, the server and the client. The server is using ODBC drives.


Charles Zhang
 http://www.**--****.com/ 
SpeedyDB ADO.NET Provider is the fastest ADO.NET provider over Wide Area 
Network (WAN).

Hello Hao,
Thanks for your reply.

I'm sorry to hear BatchSize doesn't help on your scenario. As far as I 
know, SqlBulkCopy is the best component to copy large block data in ADO.net 
so far. There is truly some performance issue in managed code. In some 
field, it cannot reach the performance as with unmanaged code. 

I'm indeed sorry for any inconvenience this may have caused. 
Wen Yuan
Microsoft Online Community Support 
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Dim AsyncDataReader As IDataReader =
command.ExecuteReader(CommandBehavior.SequentialAccess)
Using transaction As SqlTransaction = Target.DBConn.BeginTransaction()

Using BulkCopy As SqlBulkCopy = New SqlBulkCopy(Target.DBConn,
SqlBulkCopyOptions.KeepIdentity Or SqlBulkCopyOptions.KeepNulls,
transaction)
  Dim dt As DataTable = New DataTable
  dt.Load(AsyncDataReader)

  BulkCopy.BatchSize = BatchSize
  BulkCopy.DestinationTableName = Tablename
  BulkCopy.BulkCopyTimeout = 0
  Try
   BulkCopy.WriteToServer(dt, DataRowState.Added)
  transaction.Commit()
   Catch ex As Exception
   MessageBox.Show(ex.Message)
   transaction.Rollback()
End Try

*** Sent via Developersdex  http://www.**--****.com/  ***