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