| Groups | Blog | Home |
dotnet ado.net : What is the faster way to insert/buck insert rows into SQL Server
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) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "Hao" <Hao@newsgroup.nospam> wrote in message news:e%23XXoy4oHHA.1240@TK2MSFTNGP04.phx.gbl... >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 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
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.
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" <Hao@newsgroup.nospam> schreef in bericht news:e%23XXoy4oHHA.1240@TK2MSFTNGP04.phx.gbl... [quoted text, click to view] >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 > > >
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 [quoted text, click to view] "WenYuan Wang [MSFT]" <v-wywang@online.microsoft.com> wrote in message news:Z1hJJCBpHHA.1144@TK2MSFTNGHUB02.phx.gbl... > 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. >
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). [quoted text, click to view] Hao wrote:
> 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 > > "WenYuan Wang [MSFT]" <v-wywang@online.microsoft.com> wrote in message > news:Z1hJJCBpHHA.1144@TK2MSFTNGHUB02.phx.gbl... >> 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. >> >
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.
"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) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "Hao" <Hao@newsgroup.nospam> wrote in message news:u08x0nqpHHA.2372@TK2MSFTNGP02.phx.gbl... > 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 > > "WenYuan Wang [MSFT]" <v-wywang@online.microsoft.com> wrote in message > news:Z1hJJCBpHHA.1144@TK2MSFTNGHUB02.phx.gbl... >> 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. >> > >
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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |