SQLilte query much slower through RSqlite than sqlite3 command-line interface

Question

I'm using the RSQLite package to make queries to a local SQLite database, and for some queries the RSQLite interface is quite slow.

As a specific example, the following query takes under one second to run using the sqlite3 command-line utility:

$ sqlite3 data/svn.db
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select count(distinct svn_path.revision)  FROM src INNER JOIN svn_path ON src.filename=svn_path.path;
5039

But the equivalent query in R takes a little over two minutes and uses 100% of one of my CPUs:

> library(RSQLite)
Loading required package: DBI
> con <- dbConnect(SQLite(), dbname="data/svn.db")
> dbGetQuery(con, "select count(distinct svn_path.revision)  FROM src INNER JOIN svn_path ON src.filename=svn_path.path")
  count(distinct svn_path.revision)
1                              5039

Why is the performance so much slower through the R interface?

Note that I'm using R64 2.10.1 on Mac OS X 10.6.6.

Answer 1

What matters is the version of RSQLite you have. Your version of R seems to be over a year old so if your RSQLite is just as old it could be a much older engine (eg, 3.6.4) as suggested by Benoit.