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

[Hao]

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

 

[William \(Bill\) Vaughn]

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)

 

[WenYuan Wang [MSFT]]

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://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.b
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.

 

[Cor Ligthert [MVP]]

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]

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

 

[Charles Zhang]

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.speedydb.com
SpeedyDB ADO.NET Provider is the fastest ADO.NET provider over Wide Area
Network (WAN).

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

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://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.b
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.

 

[WenYuan Wang [MSFT]]

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.

 

[William \(Bill\) Vaughn]

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

You've got to be kidding. I suggest you look around. Many (many) of the
Fortune 1000 companies have implemented deadly serious large scale
applications using .NET.


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

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

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://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.b
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.

 

[Johnny Chin]

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