Re: SQL Server execution duration difference

Tech-Archive recommends: Fix windows errors by optimizing your registry

I am trying to figure out what you two are talking about now that doesn't make any sense about the speed of something running through SQL Server QA, which is in direct contact with SQL Server, as opposed to something that's running from a program that using OleDb

<from a post to me in another NG about the speed>

One of the reasons the SQL .NET Data Provider has gotten so much hype (which
is why all the samples use it), is how it has been optimized. The SQL
Managed Provider talks directly to SQL Server without using OLEDB (the
benefits of using products from the same company). Microsoft claims that the
speed of moving data between SQL Server and your ASP.NET application can
increase as much as 300% or more using the SQL Managed Provider because of
this direct communication.

I posted the results of a little test here back in June:

SQL Client (2.0): INSERT 20000 rows: 9,109375 seconds
SQL Client (2.0): 50 SELECT 20000 rows: 1,75 seconds
OLE DB (2000): INSERT 20000 rows: 20 seconds
OLE DB (2000): 50 SELECT 20000 rows: 62,140625 seconds
ODBC (2000): INSERT 20000 rows: 16,65625 seconds
ODBC (2000): 50 SELECT 20000 rows: 47,8125 seconds
OLE DB (2005): INSERT 20000 rows: 17,28125 seconds
OLE DB (2005): 50 SELECT 20000 rows: 61,484375 seconds
ODBC (2005): INSERT 20000 rows: 13,96875 seconds
ODBC (2005): 50 SELECT 20000 rows: 47,734375 seconds

<end>

Anything coming from OLE DB or ODBC is going to be slower because those solutions are not in direct contact with SQL Server, as opposed to SQL Client or something running within SQL Server QA with those solutions being in direct contact with SQL Server.

.

Relevant Pages

  • 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: What is the faster way to insert/buck insert rows into SQL Server
    ... I would have said SqlClient would be the fastest--that or DBLib. ... Hitchhiker's Guide to Visual Studio and SQL Server ... code in C++ and used ODBC driver SQLBindParameter to bind the data in the ... OLE DB .Net ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Is ADO.NET OleDb .NET Data Provider a COM object?
    ... Okay, okay... ... OLE DB is a one-size-fits-all set of providers that can access anything from a relational database to a tuna salad. ... in order to access SQL Server we used OLE DB to access ODBC or SQL Server. ... In a similar way, just because you can connect to MySQL, Paradox, Oracle or FarkleStar databases from an OLE DB or other OSFA provider does not mean your code can interface with these other back-ends. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Q: what is the relation between DB-Lib, OLE-DB, and ODBC?
    ... >> I had reason some time ago to make a choice between ODBC and OLE DB ... can you recommend any sources of decent sample code for this (OLE ... > DB with MS Sql server), and also, while we're here, on writing extended ...
    (microsoft.public.data.oledb)
  • Re: SqlServer 2005 Transfer Flame
    ... database between the two servers. ... I do understand your concerns with SQL Server 2005. ... Drag a OLE DB Destination to the panel; ...
    (microsoft.public.sqlserver.dts)