[R] How can I change SQLite cache size for R session?

[David James]

Hi,

charles loboz wrote:
> 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?)

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

> 
>  
>  
>  
> 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.
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html