4

I am using R in combination with SQLite using RSQLite to persistate my data since I did not have sufficient RAM to constantly store all columns and calculate using them. I have added an empty column to the SQLite database using:

dbGetQuery(db, "alter table test_table add column newcol real)

Now I want to fill this column using data I calculated in R and which is stored in my data.table column dtab$newcol. I have tried the following approach:

dbGetQuery(db, "update test_table set newcol = ? where id = ?", bind.data = data.frame(transactions$sum_year, transactions$id))

Unfortunately, R seems like it is doing something but is not using any CPU time or RAM allocation. The database does not change size and even after 24 hours nothing has changed. Therefore, I assume it has crashed - without any output.

Am I using the update statement wrong? Is there an alternative way of doing this?

UPDATE

I have also tried the RSQLite functions dbSendQuery and dbGetPreparedQuery - both with the same result. However, what does work is updating a single row without the use of bind.data. A loop to update the column, therefore, seems possible but I will have to evaluate the performance since the dataset is huge.

Sebastian Hätälä
  • 955
  • 12
  • 34
  • 1
    Did you tried `dbSendQuery`? – jangorecki Jul 20 '15 at 18:16
  • Sorry forgot to mention, also tried dbSendquery and dbGetPreparedQuery. – Sebastian Hätälä Jul 20 '15 at 18:18
  • You used parametrized queries, make sure your RSQLite version supports them as this is quite recent feature in RSQLite. If this is not the case then try to perform updates without parametrized queries. If this will not help take the update statements from previous step and check if they will work on sqlite directly, not from R. – jangorecki Jul 20 '15 at 18:30
  • 1
    Did you set index on `id`? the sqlite index, not data.table one. SQLite without indexes can be really slow. – jangorecki Jul 20 '15 at 18:33
  • @jangorecki I use RSQLite version 1.0.0 from cran (which is the newest there). I did not explicitly set an index on id but created the column as PRIMARY KEY which should auto create an indes afaik. If I need another version of RSQLite or need to explicitly set an index please let me know :-) – Sebastian Hätälä Jul 20 '15 at 18:39
  • 1
    Indeed, the problem had to do with the SQLite performance. I disabled synchronous and set journal_mode to off. Also I changed my RSQLite code to use dbBegin(), dbSendPreparedQuery() and dbCommit(). It is takes a while but at least it works not and has an acceptable performance :-) Thank you @jangorecki – Sebastian Hätälä Jul 20 '15 at 20:08
  • You can self answer your question for the future readers and for closing open question. – jangorecki Jul 20 '15 at 20:22

1 Answers1

2

As mentioned by @jangorecki the problem had to do with SQLite performance. I disabled synchronous and set journal_mode to off (which has to be done for every session).

dbGetQuery(transDB, "PRAGMA synchronous = OFF") 
dbGetQuery(transDB, "PRAGMA journal_mode = OFF")

Also I changed my RSQLite code to use dbBegin(), dbSendPreparedQuery() and dbCommit(). It is takes a while but at least it works not and has an acceptable performance.

Sebastian Hätälä
  • 955
  • 12
  • 34