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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

"Hao" <Hao@xxxxxxxxxxxxxxxx> wrote in message
news:e%23XXoy4oHHA.1240@xxxxxxxxxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • Re: Really want a answer, is there a mature way to access different data providers without performan
    ... I dll can support both SQL server and mysql, ... > You can use OleDB and ODBC also. ... > direct adapter as SqlClient. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Different empty_string/NULL handling under ODBC and OLE DB?
    ... NULL whereas empty string is stored as a space. ... When migrating from ODBC to OLE DB we get errors when inserting an ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Samplclnt example in MDAC 2.8: My Story
    ... It's funny how Microsoft appears to be pushing OLE DB hard, ... > people seem reluctant to abandon ODBC, and again, I've heard from ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.data.oledb)
  • SQL Server ODBC Driver Ignores Authentication Setting
    ... set their ODBC connections with SQL Server Authentication, ... Microsoft Data Access Components 2.6 RTM, ... authentication to log into the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Linking tables access - sql server 2005
    ... Another advantage of this method is that you don't need an ODBC setting on the local computer, ... Create a linked table to SQL Server without using a DSN ... Name of the table that you are linking to on the SQL Server database ... Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As ...
    (microsoft.public.access.adp.sqlserver)