r-cran-rsqlite 0.11.2-1build1 / usr / lib / R / site-library / RSQLite / rsqlitePerf.txt

From Charles Loboz (edited to remove bug reports that have been fixed):

I think that the following remarks can be usefule for R users working
with SQLite. They were collected while testing my own computations on
Windows version of R and SQL, under XP home and W2K professional. They
may apply - or not - to other platforms


        Cache size

Default cache size is 2000 pages, default page size is 1kb. That means
that the default cache size is only two megabytes (!) That is the single
factor creating most slowdown. It may not matter much if the main usage
mode is just dbReadTable/dbWriteTable, but it will slow down most
dbGetQuery. Since SQLite does not use cache unnecessarily, it may be
safer to declare larges caches. Even on a 128mb computer I was running
my calculations with 32mb cache size quite comfortably.

Suggested: after opening the database
execute immediately pragma cache_size=32000 (or greater)


            Page size

It seems that it cannot be set when creating databases from R, though it
can be set when creating the database from sqlite3 utility. The default
is 1024. Changing it to 4096 does not seem to change performance much.
So - unless some evidence to the contrary is available - that should not
be tinkered with.


        Synchronization

Default synchronization for SQLite database (pragma synchronous) is
level 2 (FULL). That is good for transaction processing environments,
but hardly likely to be necessary in a typical R usage.

Suggested: after opening the database execute immediately pragma
synchronous 0. Computations storing small dataframes multiple times will
be running several times faster.


        Column names when storing tables


Be aware that some column names are silently changed when dbWriteTable
is performed - and dbReadTable does not restore them. I observed that in
particular column names like FROM etc are extended with __1.