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

[Seth Falcon]

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