[R-sig-DB] Managing transactions with RSQLite?

[Steve Lianoglou]

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?

BTW, I'm using RSQLite_0.8-0

Thanks,
-steve

-- 
Steve Lianoglou
Graduate Student: Computational Systems Biology
 | Memorial Sloan-Kettering Cancer Center
 | Weill Medical College of Cornell University
Contact Info: http://cbio.mskcc.org/~lianos/contact