[dba-SQLServer] Data Transfer Speed: ODBC vs. OLEDB

Jim Lawrence accessd at shaw.ca
Tue Nov 25 06:42:59 CST 2008


Hi Dan:

That is about right. OLEDB is significantly faster than going through an
ODBC connection. There is really no comparison performance wise. If you want
to see how slow slow is try using a large ODBC SELECT with a Pass-Through
type query... and then go for lunch. ;-) 

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Monday, November 24, 2008 1:06 PM
To: 'Access Developers discussion and problem solving'; SQL Server List
Subject: [dba-SQLServer] Data Transfer Speed: ODBC vs. OLEDB

I've been preparing to upsize an Access database for a customer, and I first
set up a test to see how much faster data transfer would using an OLEDB
provider vs. using ODBC table links, both to the same SQL Server database.

My test was simple:  From a client PC, copy a record from a table and insert
it into an identical table.  Do this 50 times for each method.  The results
are quite interesting.  You might want to paste this into a spreadsheet.

Location	Test	 Msecs	  Secs	Secs/Cycle	Factor
						
Rem Desk	ODBC	  1922	  1.92	0.038	
Rem Desk	OLEDB	    62	  0.06	0.001	  	31
						
LAN		ODBC	  2922	  2.92	0.058	
LAN		OLEDB	   140	  0.14	0.003	  	21
						
WAN: MPLS	ODBC	 27641	 27.64	0.553	
WAN: MPLS	OLEDB	  2219	  2.22	0.044	  	12
						
WAN: Asia	ODBC	205235	205.24	4.105	
WAN: Asia	OLEDB	 19703	 19.70	0.394	  	10


The test in Asia was done from Thailand, with the server in Minneapolis.  We
used Access 2003 as the FE, and SQL Server 2005 for data tables.  

Has anyone else set up tests like this?  Were your results comparable?

By the way, I also did a LAN test using table links to an Access BE with a
permanently open recordset, and the results were almost identical to ODBC
table links to SQL Server.

Thanks!
Dan



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list