Skip to content

How can I change SQLite cache size for R session?

2 messages · charles loboz, David James

charles loboz
# 
How can I change SQLite cache size for R session?

SQLite cache size can be set by a pragma, for the
duration of the session - or by default.
    .pragma cache_size
    .pragma default_cache_size
 
my questions are about RSQLite, version 0.4, running
on Windows:
 - what is the cache size set to when SQLite is
invoked from R?
 - if the page_size is set to 4096 what will be the
cache size in bytes when cache_size 1000 is specified?
(in other words: does R know about the page size
setting?)

 
 
 
P.S. Very impressed with SQLite and its (embedded)
integration with R - using it fully really simplifies
a lot of programming as well as moving computations
between computers. It is also, for my current needs,
much faster than postgress and much less cumbersome in
operation (permissions, administration, mobility). The
price to pay - need to be careful with crafting SQL -
no optimizer here. However, controlling cache size is
an important thing for any database and I would like
to know how to do it for R. Documentation does not
mention it and quick scan of the source code of
RSQLite did not show any obvious comments.
David James
# 
Hi,
charles loboz wrote:
No, R doesn't know about these settings, nor it needs to.  According
to the SQLite documentation "Pragmas to modify library operation" at
http://www.sqlite.org/pragma.html, you can set the cache size (for the 
current session) with

  > library(RSQLite)
  Loading required package: DBI
  > con <- dbConnect(SQLite(), "/tmp/foo3.db")
  > dbGetQuery(con, "pragma cache_size")
    cache_size
  1       2000
  > dbGetQuery(con, "pragma cache_size=2500")
  NULL
  > dbGetQuery(con, "pragma cache_size")
    cache_size
  1       2500

Hope this helps,

--
David