Menu
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.
866
The SQLite docs explains why this is so slow: Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe.
With Actian Zen, developers and product managers get all the advantages of SQLite but in a powerful, secure, and scalable engine that can run serverless or as a client-server. Actian Zen is orders of magnitude faster than SQLite.
Or, developers can write their own overloads based on their own Unicode-aware comparison routines already contained within their project. Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second.
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.
161
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
