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