Native vs ODBC Database Binding

Question

I understand that some databases have native support in R (for example, MySQL), but you can connect to other databases, such as MS SQL Server, using RODBC. How fast does read / write speed improve with native drivers and RODBC? What other DBs have their own drivers in R? Is reading faster or slower than writing at all?

Answer 1

If you are particularly interested in SQL Server, the link below is a bit outdated, but I think it probably still persists.

Using ODBC with Microsoft SQL Server

ODBC Performance as a Native API

One of the ongoing rumors about ODBC is that it is inherently slower than its own DBMS API. This reasoning is based on the assumption that ODBC drivers should be implemented as an additional layer over the native DBMS API, translating the ODBC statements coming from the application into the native DBMS API functions and SQL syntax. This translation job adds additional processing compared to the fact that the application call is directly linked to its own API. This assumption is true for some ODBC drivers implemented through the native DBMS API, but the Microsoft SQL Server ODBC driver is not implemented in this way.

The Microsoft SQL Server ODBC driver is a functional replacement for the DB library. The SQL Server ODBC driver works with basic Net-Libraries in exactly the same way as the DB DLLs. The Microsoft SQL Server ODBC driver is independent of the DB DLL, and the driver will work correctly if the DB library is not even present on the client.

Microsoft testing showed that the performance of SQL Server applications based on ODBC and DB-Library is approximately the same.

Answer 2

• This is an empirical question, so why not measure it for the combination you are interested in?
• Open source is not hidden, so why don't you figure out what other DB CRAN interfaces have? For DBI only, we have SQLite, MySQL, Postgresql, Oracle; for custom db backends, there are things like Vhayu.
• There are specialized forums, so why not ask about r-sig-db?
• Finally, as soon as the API and need appear, people tend to combine them. I wrote two different (at work and therefore not released) packages for two highly specialized and fast backends.