Native vs ODBC database connections

Question

I understand that some databases have native support in R (e.g. MySQL) but you can connect to other DBs like MS SQL Server using RODBC. How much speed improvement does one gain for reading/writing with the native drivers vs. RODBC? What other DBs have native drivers in R? Is reading faster or slower than writing generally?

Answer 1

If you're specifically interested in SQL Server, the reference below is a little bit out of date but I imagine it probably still holds.

Using ODBC with Microsoft SQL Server

Performance of ODBC as a Native API

One of the persistent rumors about ODBC is that it is inherently slower than a native DBMS API. This reasoning is based on the assumption that ODBC drivers must be implemented as an extra layer over a native DBMS API, translating the ODBC statements coming from the application into the native DBMS API functions and SQL syntax. This translation effort adds extra processing compared with having the application call directly to the native API. This assumption is true for some ODBC drivers implemented over a native DBMS API, but the Microsoft SQL Server ODBC driver is not implemented this way.

The Microsoft SQL Server ODBC driver is a functional replacement of DB-Library. The SQL Server ODBC driver works with the underlying Net-Libraries in exactly the same manner as the DB-Library DLL. The Microsoft SQL Server ODBC driver has no dependence on the DB-Library DLL, and the driver will function correctly if DB-Library is not even present on the client.

Microsoft's testing has shown that the performance of ODBC-based and DB-Library–based SQL Server applications is roughly equal.

Answer 2

• It's an empirical question, so why don't measure it for the combination you are interested in?
• Public code is not hidden, so why don't you count what other DB interfaces CRAN has? For DBI alone, we have SQLite, MySQL, Postgresql, Oracle; for custom db backends there are things like Vhayu.
• Specialised forums exist, so why don't you ask on r-sig-db?
• Lastly, as soon as there is an API and a need people tend to combine the two. I have written two different (at-work and hence unreleased) packages to two highly specialised and fast backends.