r - 提高 RODBC-Postgres 写入性能

我最近开始使用 RODBC 作为 I couldn't get RPostgreSQL to compile and run in Windows x64 连接到 PostgreSQL。我发现这两个包的读取性能相似，但写入性能却不同。例如，使用 RODBC(其中 z 是 ~6.1M 行数据帧):

library(RODBC)
con <- odbcConnect("PostgreSQL84")

#autoCommit=FALSE seems to speed things up
odbcSetAutoCommit(con, autoCommit = FALSE)
system.time(sqlSave(con, z, "ERASE111", fast = TRUE))

user  system elapsed
275.34  369.86 1979.59

odbcEndTran(con, commit = TRUE)
odbcCloseAll()

而对于使用 RPostgreSQL(32 位以下)的相同 ~6.1M 行数据帧:

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="gisdb", user="postgres", password="...")
system.time(dbWriteTable(con, "ERASE222", z))

user  system elapsed
467.57   56.62  668.29

dbDisconnect(con)

因此，在本次测试中，RPostgreSQL 在写表方面的速度大约是 RODBC 的 3 倍。无论数据帧中的行数如何(但列数的影响要小得多)，该性能比似乎或多或少保持不变。我注意到 RPostgreSQL 使用类似 COPY <table> FROM STDIN 的东西，而 RODBC 发出一堆 INSERT INTO <table> (columns...) VALUES (...) 查询。我还注意到 RODBC 似乎为整数选择了 int8，而 RPostgreSQL 在适当的情况下选择了 int4。

我需要经常做这种数据帧复制，所以我非常真诚地感谢 对加速 RODBC 的任何建议。例如，这是 ODBC 固有的，还是我没有正确调用它？

最佳答案

似乎没有立即回答这个问题，所以我会发布一个笨拙的解决方法，以防它对任何人都有帮助。

Sharpie 是正确的——COPY FROM 是迄今为止将数据导入 Postgres 的最快方法。根据他的建议，我编写了一个函数，该函数可显着提升 RODBC::sqlSave() 的性能。例如，通过 sqlSave 编写 110 万行(24 列)数据帧需要 960 秒(经过)，而使用下面的函数则需要 69 秒。我不会想到这一点，因为数据一次写入磁盘然后再次写入数据库。

library(RODBC)
con <- odbcConnect("PostgreSQL90")

#create the table
createTab <- function(dat, datname) {

  #make an empty table, saving the trouble of making it by hand
  res <- sqlSave(con, dat[1, ], datname)
  res <- sqlQuery(con, paste("TRUNCATE TABLE",datname))

  #write the dataframe
  outfile = paste(datname, ".csv", sep = "")
  write.csv(dat, outfile)
  gc()   # don't know why, but memory is
         # not released after writing large csv?

  # now copy the data into the table.  If this doesn't work,
  # be sure that postgres has read permissions for the path
  sqlQuery(con,
  paste("COPY ", datname, " FROM '",
    getwd(), "/", datname,
    ".csv' WITH NULL AS 'NA' DELIMITER ',' CSV HEADER;",
    sep=""))

  unlink(outfile)
}

odbcClose(con)

关于r - 提高 RODBC-Postgres 写入性能，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5708719/