Bulk-Update an SQLite column in R using RSQLite and bind.data

Question

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.

Sorry forgot to mention, also tried dbSendquery and dbGetPreparedQuery. — Sebastian Hätälä
– Sebastian Hätälä, Commented Jul 20, 2015 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
– jangorecki, Commented Jul 20, 2015 at 18:30
Did you set index on id? the sqlite index, not data.table one. SQLite without indexes can be really slow. — jangorecki
– jangorecki, Commented Jul 20, 2015 at 18:33
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ä
– Sebastian Hätälä, Commented Jul 20, 2015 at 20:08

Sebastian Hätälä · Accepted Answer · 2015-07-20 21:03:37Z

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.

answered Jul 20, 2015 at 21:03

Sebastian Hätälä

1,04514 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5359531 Over a year ago

do you mind posting the full R code you used for this, with the changes you mention here? Thanks.

Collectives™ on Stack Overflow

Bulk-Update an SQLite column in R using RSQLite and bind.data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related