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

Dan Waters dwaters at usinternet.com
Mon Nov 24 15:05:43 CST 2008


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






More information about the dba-SQLServer mailing list