[R-sig-DB] Managing transactions with RSQLite?
Seth Falcon
@eth @end|ng |rom u@erpr|m@ry@net
Tue Jan 5 06:37:49 CET 2010
On 1/4/10 6:02 PM, Steve Lianoglou wrote:
> Hi all,
>
> I'm sorry if the answer is obvious, but my searches haven't been
> bearing fruit. I'm trying to wrap a large "insert into" query into a
> transaction to optimize its speed. I have something like so:
>
> dbGetPreparedQuery(conn, "INSERT INTO restriction_read (id, nhits)
> VALUES(?, ?)",
> bind.data=counts.df)
>
> where:
> * `conn` is my sqlite connection
> * `counts.df` is my data.frame with id and nhits colums
>
> `counts.df` can be several 100k to 1 million rows.
>
> I thought this would wrap the inserts into a transaction, but I just
> happened to notice that a "mydb.sqlite3-journal" file is being created
> and removed lots of times on my file system while R is running this
> query (upwards of 30mins), so I'm guessing the queries are being
> executed 1 at a time, and not wrapped into a transaction.
>
> Am I correct? If so, what's the appropriate way to handle transactions
> (if any) using RSQLite?
Try doing:
dbBeginTransaction(db)
## insert here
dbCommit(db)
+ seth
More information about the R-sig-DB
mailing list