在R中加速sqldf的方法

Question

在R中加速sqldf的方法

3

我有一个在R中的程序，已经运行了一天左右，只完成了大约10%。主要的缓慢来自于使用R包sqldf从长度为~100万的数据集中进行数千次sqldf(SELECT ...)调用。我的选择语句目前采用以下形式：

sqldf(SELECT V1, V2, FROM mytable WHERE cast(start as real) <= sometime and cast(realized as real) > sometime)

sometime只是一个表示Unix时间戳的整数，start和realized是mytable表中也填充有Unix时间戳条目的列。我还知道的是，|realized - start| < 172800 总是成立的，这是一个很短的时间段，因为数据集跨越了一年。我的想法是，我应该能够利用这个事实，告诉R在每次调用时仅从时间+- 172800检查数据框。
在这里使用sqldf包是否不合适？我应该使用传统的 [,] 遍历data.frame吗？有没有一种简单的方法来加快程序速度？我的直觉是分解数据框，对向量进行排序，然后构建自定义函数来遍历并选择适当的条目，但我正在寻找一些确认这是否是最佳方法的方法。

- mt88

3

你可能希望考虑使用data.table包，它可以让你为表格构建真正的索引，从而使搜索更快。 - MrFlick

除了上面这样非常通用的建议之外，实际上没什么人能够提供帮助，因为我们并没有坐在你的电脑旁看着你的全部代码。 - joran

我明白你的意思。我更感兴趣的是关于sqldf搜索如何工作的任何信息，以及是否有一种方法将像我所拥有的条件纳入到加速搜索中。似乎应该有一种方法告诉R仅从特定索引开始搜索，我现在正在研究data.table包。 - mt88

1

尝试摆脱强制转换，以便索引可以正常工作并添加索引。在sqldf主页上有示例。否则，请尝试使用data.table。 - G. Grothendieck

data.table 不会 "建立真正的索引"。请参考这个问题/答案。但是，使用它会更快。 - jlhoward

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jlhoward · Accepted Answer

首先，慢的部分可能是cast(...)，因此不要在每个查询中两次执行此操作，而是将start和realized保留为时间戳，并更改查询以适应此情况。

其次，data.table选项仍然快了约100倍（但是请参阅末尾关于使用sqldf索引的部分）。

library(sqldf)
library(data.table)

N <- 1e6
# sqldf option
set.seed(1)
df <- data.frame(start=as.character(as.POSIXct("2000-01-01")+sample(0:1e6,N,replace=T)),
                 realized=as.character(as.POSIXct("2000-01-01")+sample(0:1e6,N,replace=T)),
                 V1=rnorm(N), V2=rpois(N,4))

sometime <- "2000-01-05 00:00:00"
query <- "SELECT V1, V2 FROM df WHERE start <= datetime('%s') and realized > datetime('%s')"
query <- sprintf(query,sometime,sometime)          
system.time(result.sqldf <- sqldf(query))
#    user  system elapsed 
#   12.17    0.03   12.23 

# data.table option
set.seed(1)
DT <- data.table(start=as.POSIXct("2000-01-01")+sample(0:1e6,N,replace=T),
                 realized=as.POSIXct("2000-01-01")+sample(0:1e6,N,replace=T),
                 V1=rnorm(N), V2=rpois(N,4))
setkey(DT,start,realized)
system.time(result.dt <- DT[start<=as.POSIXct(sometime) & realized > as.POSIXct(sometime),list(V1,V2)])
#    user  system elapsed 
#    0.15    0.00    0.15

请注意，这两个结果集将以不同的方式排序。

根据@G.Grothendieck（sqldf包的作者）下面的评论进行编辑。

这正在成为包之间的一个非常好的比较...

# code from G. Grothendieck comment
sqldf()      # opens connection
sqldf("create index ix on df(start, realized)")
query <- fn$identity("SELECT V1, V2 FROM main.df WHERE start <= '$sometime' and realized > '$sometime'")
system.time(result.sqldf <- sqldf(query))
sqldf()      # closes connection
#    user  system elapsed 
#    1.28    0.00    1.28

因此，在这种情况下，创建索引可以使sqldf的速度提高约10倍。索引创建很慢，但您只需要执行一次。在data.table中创建“key”（物理排序表）非常快，但在这种情况下并没有显著提高性能（仅提高约2倍）。

使用system.time()进行基准测试有一定风险（1个数据点），因此最好使用microbenchmark(...)。请注意，为了使其正常工作，我们必须运行上面的代码，并保持连接打开（例如，删除对sqldf()的最后一次调用）。

f.dt    <- function() result.dt <- DT[start<=as.POSIXct(sometime) & realized > as.POSIXct(sometime),list(V1,V2)]
f.sqldf <- function() result.sqldf <- sqldf(query)
library(microbenchmark)
microbenchmark(f.dt(),f.sqldf())
# Unit: milliseconds
#       expr      min        lq    median       uq       max neval
#     f.dt() 110.9715  184.0889  200.0634  265.648  833.4041   100
#  f.sqldf() 916.8246 1232.6155 1271.6862 1318.049 1951.5074   100

我们可以看到，在这种情况下，使用关键字的 data.table 比使用索引的 sqldf 快了约 6 倍。实际时间将取决于结果集的大小，因此您可能希望比较这两个选项。