使用DuckDB在R中执行SQL查询时程序崩溃问题求助

问题根源 #

你遇到的崩溃本质是内存过载：EXCEPT操作需要对两张1.83亿行表的UserId进行全量去重、比对，中间结果会占用大量内存，直接撑爆R的可用内存；而你加的LIMIT是对最终结果的限制，无法减少中间计算过程的数据量，因此无效。

具体优化措施 #

1. 给UserId列创建索引 #

索引能大幅加速DuckDB的集合比对操作，减少内存消耗：

# 给两张表的UserId列建索引
dbExecute(con, "CREATE INDEX idx_d14072021_userid ON d14072021(UserId)")
dbExecute(con, "CREATE INDEX idx_d15072021_userid ON d15072021(UserId)")

2. 分块读取结果，避免一次性加载全量数据 #

放弃dbGetQuery一次性拉取所有结果的方式，改用dbSendQuery+dbFetch分块读取，降低R内存压力：

# 发送查询请求（无需LIMIT）
res <- dbSendQuery(con, "
  (SELECT UserId FROM d14072021 EXCEPT SELECT UserId FROM d15072021)
  UNION ALL
  (SELECT UserId FROM d15072021 EXCEPT SELECT UserId FROM d14072021)
")

# 分块读取，每次读取10000行（可根据内存调整）
chunk_size <- 10000
compareid <- data.frame()
while (!dbHasCompleted(res)) {
  chunk <- dbFetch(res, n = chunk_size)
  compareid <- rbind(compareid, chunk)
}

# 清理查询资源
dbClearResult(res)

3. 改用更高效的对称差写法 #

用分组聚合替代两次EXCEPT，逻辑等价但内存效率更高：

# 分组聚合求对称差（仅保留出现一次的UserId）
res <- dbSendQuery(con, "
  SELECT UserId
  FROM (
    SELECT UserId FROM d14072021
    UNION ALL
    SELECT UserId FROM d15072021
  ) AS combined
  GROUP BY UserId
  HAVING COUNT(*) = 1
")

# 同样分块读取结果
chunk_size <- 10000
compareid <- data.frame()
while (!dbHasCompleted(res)) {
  chunk <- dbFetch(res, n = chunk_size)
  compareid <- rbind(compareid, chunk)
}
dbClearResult(res)

4. 限制DuckDB的内存使用 #

通过设置DuckDB的内存上限，避免其占用过多系统内存导致R崩溃：

# 设置内存限制为16GB（根据你的机器内存调整，比如8GB/32GB）
dbExecute(con, "SET memory_limit = '16GB'")

内容的提问来源于stack exchange，提问作者dominik hauser