dbAppendTable is slow while dbWriteTable is fast

[gaborcsardi]

dbAppendTable() is very slow on a file. It is fast for the in-memory DB.

tab <- pkgcache::meta_cache_list()

## Remove the list columns
tab <- tab[, !sapply(tab, is.list)]
dim(tab)
#> [1] 35359    31

con <- DBI::dbConnect(RSQLite::SQLite(), tempfile(), create = TRUE)
system.time(DBI::dbWriteTable(con, "pkgs", tab))
#>    user  system elapsed 
#>   0.162   0.043   0.211
dim(DBI::dbReadTable(con, "pkgs"))
#> [1] 35359    31
rm(con)

con2 <- DBI::dbConnect(RSQLite::SQLite(), tempfile(), create = TRUE)
DBI::dbCreateTable(con2, "pkgs", tab)
dim(DBI::dbReadTable(con2, "pkgs"))
#> [1]  0 31
system.time(DBI::dbAppendTable(con2, "pkgs", tab))
#>    user  system elapsed 
#>   1.122  11.886  15.088
dim(DBI::dbReadTable(con2, "pkgs"))
#> [1] 35359    31

^{Created on 2019-12-23 by the reprex package (v0.3.0)}

[gaborcsardi]

I suspect that every row is added in its own transaction for dbAppendTable(). Which lead me to this workaround, so maybe there is nothing to fix here.

tab <- pkgcache::meta_cache_list()

## Remove the list columns
tab <- tab[, !sapply(tab, is.list)]
dim(tab)
#> [1] 35359    31

con3 <- DBI::dbConnect(RSQLite::SQLite(), tempfile(), create = TRUE)
DBI::dbCreateTable(con3, "pkgs", tab)
dim(DBI::dbReadTable(con3, "pkgs"))
#> [1]  0 31

system.time({
  DBI::dbBegin(con3)
  DBI::dbAppendTable(con3, "pkgs", tab)
  DBI::dbCommit(con3)
})
#>    user  system elapsed 
#>   0.151   0.040   0.193

dim(DBI::dbReadTable(con3, "pkgs"))
#> [1] 35359    31

Created on 2019-12-23 by the reprex package (v0.3.0)

[krlmlr]

dbWriteTable() calls dbBegin() and dbCommit() with the name argument, which translates into a SAVEPOINT ... + RELEASE SAVEPOINT ... pair. Is there a performance impact for nested savepoints? If not, we can easily provide an implementation of dbAppendTable() that uses a savepoint.

[gaborcsardi]

The overhead should be minimal, I think.

tab <- pkgcache::meta_cache_list()

## Remove the list columns
tab <- tab[, !sapply(tab, is.list)]
dim(tab)
#> [1] 35359    31

sp1 <- function() {
  con3 <- DBI::dbConnect(RSQLite::SQLite(), tempfile(), create = TRUE)
  DBI::dbCreateTable(con3, "pkgs", tab)
  DBI::dbExecute(con3, "SAVEPOINT foo;")
  DBI::dbAppendTable(con3, "pkgs", tab)
  DBI::dbExecute(con3, "RELEASE foo;")
  DBI::dbDisconnect(con3)
}

sp2 <- function() {
  con3 <- DBI::dbConnect(RSQLite::SQLite(), tempfile(), create = TRUE)
  DBI::dbCreateTable(con3, "pkgs", tab)
  DBI::dbExecute(con3, "SAVEPOINT foo;")
  DBI::dbExecute(con3, "SAVEPOINT bar;")
  DBI::dbAppendTable(con3, "pkgs", tab)
  DBI::dbExecute(con3, "RELEASE bar;")
  DBI::dbExecute(con3, "RELEASE foo;")
  DBI::dbDisconnect(con3)
}

bench::mark(sp1(), sp2(), iterations = 10)

❯ bench::mark(sp1(), sp2(), iterations = 10)
# A tibble: 2 x 13
  expression   min median `itr/sec` mem_alloc `gc/sec` n_itr  n_gc total_time
  <bch:expr> <bch> <bch:>     <dbl> <bch:byt>    <dbl> <int> <dbl>   <bch:tm>
1 sp1()      193ms  195ms      5.12     383KB    0        10     0      1.95s
2 sp2()      190ms  196ms      5.08     401KB    0.565     9     1      1.77s
# … with 4 more variables: result <list>, memory <list>, time <list>, gc <list>

[GitHunter0]

In fact, for me DBI::dbAppendTable() is so slow that the operation never ends and RStudio freezes

[krlmlr]

Implemented now, performance not tested yet.

[plantton]

dbWriteTable() calls dbBegin() and dbCommit() with the name argument, which translates into a SAVEPOINT ... + RELEASE SAVEPOINT ... pair. Is there a performance impact for nested savepoints? If not, we can easily provide an implementation of dbAppendTable() that uses a savepoint.

I think the key point here is that, for SQL statement, it's efficient to use ONE commit to append the whole transactions. If transactions are appended by many unique commits, the process would be apparently slow. The performance will be much slower on large on-disk files.

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.