Drew Wutka
DWUTKA at Marlow.com
Mon Nov 24 15:29:10 CST 2008
Not a surprise to me. OLEDB drivers are going to always be faster then ODBC drivers. ODBC is a layer over the native engine, where as an OLEDB driver is talking the same language as the database, so it is processed much faster. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters Sent: Monday, November 24, 2008 3:06 PM To: 'Access Developers discussion and problem solving'; SQL Server List Subject: [AccessD] 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.