代码之家 › 专栏 › 技术社区 › littlegreen

以两个或多个表作为输出的SQL联接-最有效的方法?

tsql sql-server sql

littlegreen · 技术社区 · 14 年前

我有一个SQL查询,它在另一个表上执行左联接,然后输出可以耦合到指定表中的所有结果。然后我有第二个SQL查询再次执行左联接,然后输出无法耦合到指定表的结果。在代码中,这类似于:

INSERT INTO coupledrecords
SELECT b.col1, b.col2... s.col1, s.col2... FROM bigtable AS b
LEFT JOIN smallertable AS s
ON criterium
WHERE s.col1 IS NOT NULL

INSERT INTO notcoupledrecords
SELECT b.col1, b.col2... bigtable AS b
LEFT JOIN smallertable AS s
ON criterium
WHERE s.col1 IS NULL

我的问题是:为了实现我想要的,我现在必须执行两次联接。我有一种感觉,这是速度的两倍。这是真的吗?如果是,有没有办法更有效地做到这一点?

5 回复 | 直到 14 年前

AdaTheDev 14 年前

如果要将不同的结果插入到两个不同的表中,则需要两个不同的查询。

我唯一建议的是,“coupledRecords”查询可以只是一个内部联接:

INSERT INTO coupledrecords
SELECT b.col1, b.col2... s.col1, s.col2... FROM bigtable AS b
INNER JOIN smallertable AS s 
ON criterium

如果您正在插入同一个表,其中有一个字段指示它是否是匹配的记录,那么可以将其作为一个查询来执行。

chris 14 年前

底线是,您需要2个查询,因为您要插入2个不同的表中。如果只有一个表而不是coupledRecords和notcoupledRecords,那么您可以在1个查询中完成它。:)

Martin Smith 14 年前

我认为您可以这样做的一种方法是在一列上创建一个带有检查约束的分区视图,该列指示耦合/不耦合。然后插入到视图中,让SQL Server计算出目标表。不是说你会这么做,只是觉得我可能会提到!

INSERT INTO coupledrecordsView
SELECT case WHEN s.col1 IS NULL THEN 1 ELSE 0 END AS IsCoupled,
b.col1, b.col2... s.col1, s.col2... FROM bigtable AS b
LEFT JOIN smallertable AS s
ON criterium

Justin Grant 14 年前

如果可以更改 coupledrecords 和 notcoupledrecords 要包含smalltable中的一列(包括在每个聚集索引的末尾附加一个计算位列,仅用于此目的——有关详细信息,请参阅上面的@martin smith's answer)。 Partitioned View 插入件。这很容易。

如果这不可能,那么您也可以尝试非分区视图解决方案。请看下面——它涉及的更多。

在不知道数据是如何分布的情况下(例如,行大小、可以为空的列数与不可以为空的列数、耦合与不耦合的比率),很难推荐一个通用的解决方案,但在大多数情况下,一个可能会很好地工作的解决方案是使用视图在单人间” maybecoupled “表。使用视图意味着您现有的查询代码(而不是插入代码)不必更改。

乍一看,这似乎效率极低,但请记住,空值占用了零存储空间,并且使用适当的索引,SQL不会浪费太多的时间来过滤“其他视图”的行。

它的工作原理如下:

插入 全部的 记录到基表中(例如 可组合 )一次通过
确保在从中获取的某一列上有索引(理想情况下是聚集索引,但非聚集索引也可以) smalltable . 假设这是 indexedcol1
在顶部创建两个视图: 联线 和 无耦合记录 ,其定义是 SELECT col1, ... FROM maybecoupled WHERE indexedcol1 IS NULL 和 SELECT col1, ... FROM maybecoupled WHERE indexedcol1 IS NOT NULL .
如果您在indexedcol1上有一个聚集索引,那么对于大多数查询,您只需支付很少或没有性能惩罚,因为对任一视图的每个查询只会命中相应的一半记录,而不会触及另一半记录。您的非聚集索引将变得更大,因此速度会变慢,但即使这样也可以通过 filtered indexes .
如果不能使用聚集索引,请确保indexcol1是(或)的一部分 INCLUDE-d into)每个非聚集索引。这就避免了必须返回到聚集索引来查找仅从非聚集索引中提取数据的查询的indexcol1。

以下是上述解决方案不起作用的一些情况:

如果耦合行的数量相对较少,并且您在 bigtable 或者你有很小的一排。那么,所有这些非耦合行的空间开销可能会受到伤害。(空值不占用空间,但不可为空的列占用空间。)
如果使用的是非聚集索引col1,并且无法更改索引以确保索引col1存在于非聚集索引中
如果由于查询的复杂性增加,上述错误导致SQL Server在查询计划中选择错误的索引(尽管您可以使用索引提示来解决此问题)

警告:您肯定希望测试任何基于视图的解决方案的性能,以确保它不会使情况变得更糟——SQL通常擅长选择好的查询计划,但并不总是如此。测试,测试,测试!

Preet Sangha 14 年前

你基本上把数据分成两部分。一旦你做了,一旦你有一套钥匙在对联记录中,应该只做一个不在从那个开始

INSERT INTO notcoupledrecords
SELECT b.col1, b.col2... bigtable AS b
WHERE some_col not in (select some_col from coupledrecords)