left_join和dbplyr低效SQL

Question

我从许多不同的表中提取数据，并将它们连接到一个基表中，如下所示：

df <- tbl(ch, dbplyr::in_schema("schem1", 'education_data')) %>% 
  select(ID, ProviderKey,ReportingPeriodKey,EthnicityKey) %>%
  left_join(
    tbl(ch, dbplyr::in_schema("data_schem", 'ReportingPeriodDimensions')) %>% 
      select(ReportingPeriodKey,ReportingYear), 
    by = "ReportingPeriodKey"
  ) %>% 
  left_join(
    tbl(ch, dbplyr::in_schema("data_schem", 'ProviderDimensions')) %>%
        select(ProviderKey, ProviderName), 
    by = "ProviderKey"
  ) %>%
  left_join(
    tbl(ch, dbplyr::in_schema("sdr", 'EthnicityDim')) %>%
      select(EthnicityKey,Ethnicity_description), 
    by = "EthnicityKey"
  ) %>% 
  filter(ReportingYear %in% ("2015","2016","2017"))

当我使用show_query(df)检查SQL代码时，基表'education_data‘有一个'SELECT’SQL命令，它为每个执行的联接选择了所有变量，在本例中为3x。我通常需要加入大约8或9个其他表，这取决于我需要加入的维度表的数量。

这似乎非常低效，尤其是当我拉着记录数以百万计的表时。是否需要使用R命令，而不是直接编写SQL代码？

Answer 1

我猜想，与其看到类似于以下内容的整洁SQL查询，不如：

SELECT a.col1, a.col2, b.col3, c.col4
FROM table_A AS a
LEFT JOIN table_B AS b
ON a.col = b.col
LEFT JOIN table_C AS c
ON b.col = c.col

相反，您看到的嵌套查询看起来更像：

SELECT LHS.col1, LHS.col2, LHS.col3, RHS.col4
FROM (
    SELECT LHS.col1, LHS.col2, RHS.col3
    FROM table_A AS LHS
    LEFT JOIN table_B AS RHS
    ON LHS.col = RHS.col
) AS LHS
LEFT JOIN table_C AS RHS
ON LHS.col = RHS.col

但布局更糟糕。

我同意第一个例子是格式更好、更容易阅读和良好的编码实践。相反，dbplyr自动生成的SQL代码非常难看。

一般来说，我并不担心这个问题。人工编写的SQL代码旨在使人和机器可读。从dbplyr自动生成的SQL代码主要用于机器可读性。您的SQL解释器很可能以相同的方式执行这两个变体。随着dbplyr工作的复杂性增加，我相信SQL解释器能够找到一种高效的方法来执行查询。

我从未注意到dbplyr自动生成的SQL与类似的人工代码之间的主要性能差异。但我还没有对此进行广泛的测试。如果您确实运行了这样的测试，它们将是一个很好的答案来补充这个问题。

如果你关心性能，你可以做一些事情：

1. 按@danblack的建议，确保您的表在要加入的列上有索引。可能是最重要列上的聚集索引。在不离开R的情况下这样做的一种方法是DBI::dbExecute("sql string for adding an index goes here")，它将执行在数据库中给出的命令。

1. 将关键的中间表写入数据库&索引。例如，如果表A是通过连接表B和C来创建的，那么如果直接从表B和表C中工作，则执行A与A的自联接将涉及三个联接(( but )-(But)-(But))，但如果您已经将表A写入数据库( and )，则只有一个联接。

根据上下文的不同，

1. 可以在加入之前对每个表进行分区，然后将输出表附加到一起。例如，在R中使用一个循环来为每个报告年运行一个查询。在较小的输入数据集上运行联接往往执行得更快。