如何更快将Azure中的数据导入R开展数据分析

1 优先优化数据库侧查询效率 #

• 给查询涉及的Column1、Column2、Column3创建非聚集索引，减少Azure SQL侧的查询扫描耗时
• 查询语句增加WITH (NOLOCK)参数，避免事务锁等待，适合非强一致性要求的分析场景：

SELECT Column1, Column2, Column3 FROM myTable WITH (NOLOCK)

• 若不需要全量6700万行数据，优先在SQL语句中用WHERE条件过滤行，不要把全量数据拉到本地再筛选。

2 更换更高性能的数据读取接口 #

• 放弃dbGetQuery一次性全量加载的模式，改用分批拉取+data.table::rbindlist合并的方式，避免内存溢出同时提升读取效率，示例代码：

# 每次拉取100万行，可根据本机内存调整批次大小
batch_size <- 1000000
res <- DBI::dbSendQuery(con, "SELECT Column1, Column2, Column3 FROM myTable WITH (NOLOCK)")
myData <- list()
i <- 1
while(!DBI::dbHasCompleted(res)){
  batch <- DBI::dbFetch(res, n = batch_size)
  myData[[i]] <- data.table::setDT(batch)
  i <- i + 1
}
DBI::dbClearResult(res)
# 合并所有批次
myData <- data.table::rbindlist(myData)

• 优先使用Arrow格式读取接口，列式存储的Arrow数据传输和类型转换效率远高于传统data.frame，转data.table几乎无额外开销：

library(arrow)
myData <- DBI::dbGetQueryArrow(con, "SELECT Column1, Column2, Column3 FROM myTable WITH (NOLOCK)") |> 
  data.table::as.data.table()

3 优化ODBC连接参数 #

• 连接时增大网络包大小，默认包大小仅4KB，调至最大32KB可大幅提升大块数据的传输效率，连接代码调整如下：

con <- DBI::dbConnect(odbc::odbc(), 
                      UID = rstudioapi::askForPassword("myEmail"),
                      Driver="ODBC Driver 17 for SQL Server",
                      Server = server, Database = database,
                      Authentication = "ActiveDirectoryInteractive",
                      # 新增以下参数
                      PacketSize = 32767,
                      stringsAsFactors = FALSE)

• 可升级至ODBC Driver 18 for SQL Server，相较17版本对Azure SQL的传输性能有明确优化。

4 避免不必要的内存拷贝 #

• 不要对返回结果做多余的赋值、类型转换操作，setDT本身是原地修改无拷贝，不会拖慢速度，瓶颈不在该步骤。
• 若后续分析不需要用到tidyverse系列函数，加载包时不要加载tidyverse，减少不必要的内存占用和方法冲突。

可选：不需要全量加载到内存的替代方案 #

如果本机内存无法容纳全量数据，可以直接通过dplyr连接数据库做服务器端计算，只拉取最终需要的统计结果，不需要加载全量数据：

library(dbplyr)
tbl(con, "myTable") |> 
  select(Column1, Column2, Column3) |> 
  # 所有过滤、分组、统计操作都会翻译成SQL在Azure侧执行
  group_by(Column1) |> 
  summarise(cnt = n()) |> 
  collect()

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