SQL联接与SQL子查询(性能)?

我希望第一个查询更快,主要是因为您具有等价和显式连接。以我的经验 IN 是一个非常慢的运算符,因为SQL通常将其作为一系列 WHERE 用“或”分隔的条款( WHERE x=Y OR x=Z OR... )

不过,和所有SQL一样,您的里程可能会有所不同。速度很大程度上取决于索引(两个ID列上都有索引吗?这将有很大的帮助……)除此之外。

唯一真正能够100%确定哪个更快的方法是打开性能跟踪(IO统计信息特别有用)并同时运行它们。确保在跑步之间清除缓存!

好吧,我认为这是一个“老生常谈,但很重要”的问题。答案是:“视情况而定!”. 表演是一个如此微妙的主题,以至于说“永远不要使用子查询,永远要连接”太愚蠢了。 在下面的链接中,您将发现一些基本的最佳实践,我发现它们非常有用: Here 1 Here 2 Here 3

我有一个包含50000个元素的表,我查找的结果是739个元素。

我最初的问题是:

SELECT  p.id,
    p.fixedId,
    p.azienda_id,
    p.categoria_id,
    p.linea,
    p.tipo,
    p.nome
FROM prodotto p
WHERE p.azienda_id = 2699 AND p.anno = (
    SELECT MAX(p2.anno) 
    FROM prodotto p2 
    WHERE p2.fixedId = p.fixedId 
)

执行耗时7.9秒。

我的问题是:

SELECT  p.id,
    p.fixedId,
    p.azienda_id,
    p.categoria_id,
    p.linea,
    p.tipo,
    p.nome
FROM prodotto p
WHERE p.azienda_id = 2699 AND (p.fixedId, p.anno) IN
(
    SELECT p2.fixedId, MAX(p2.anno)
    FROM prodotto p2
    WHERE p.azienda_id = p2.azienda_id
    GROUP BY p2.fixedId
)

花了0.0256秒

好的SQL,很好。

开始查看执行计划,以了解SQL Server将如何解释它们。您还可以使用profiler来实际运行查询多次并获取差异。

我不希望它们有如此大的不同,在这里,当使用关联子查询时,使用联接而不是子查询可以获得真正的、巨大的性能提升。

exists通常比这两个中的任何一个都要好,当您谈论左联接时,如果您希望所有记录不在左联接表中,那么not exists通常是一个更好的选择。

这两个查询在语义上可能不等价。如果一个员工为多个部门工作(可能在我工作的企业中工作;诚然,这意味着您的表没有完全规范化),那么第一个查询将返回重复的行,而第二个查询将不会返回。要使查询在本例中等效,请 DISTINCT 必须将关键字添加到 SELECT 条款,这可能会影响性能。

注意,有一个经验设计规则规定表应该为实体/类或实体/类之间的关系建模,但不能同时为两者建模。因此,我建议您创建第三个表,比如 OrgChart 建立员工与部门之间的关系模型。

性能取决于正在执行的数据量…

如果20K左右的数据较少,join会更好。

如果数据更像100k+,那么in的效果更好。

如果您不需要来自另一个表的数据,那么in是好的,但是最好还是选择exists。

我测试的所有标准和表格都有适当的索引。

性能应该是相同的;在表上应用正确的索引和集群(存在 some good resources 关于这个话题)。

(编辑以反映更新的问题)

我知道这是一篇老文章,但我认为这是一个非常重要的话题,特别是现在我们有超过1000万条记录,并且讨论了太字节的数据。

我还将对以下观察结果进行权衡。我的表中有大约4500万条记录([数据]),我的[猫]表中有大约300条记录。我已经为我将要讨论的所有查询建立了广泛的索引。

考虑示例1:

UPDATE d set category = c.categoryname
FROM [data] d
JOIN [cats] c on c.id = d.catid

与实施例2相比:

UPDATE d set category = (SELECT TOP(1) c.categoryname FROM [cats] c where c.id = d.catid)
FROM [data] d

示例1运行大约需要23分钟。例2大约花了5分钟。

所以我可以得出结论,在这种情况下,子查询要快得多。当然,请记住,我使用的是M.2SSD驱动器,它的I/O速度为每秒1GB(字节而不是位),所以我的索引也非常快。所以在你的情况下,这也会影响速度

如果是一次性的数据清理,最好还是让它继续运行并完成。我使用top(10000)并查看它需要多长时间,然后乘以记录数,然后再点击大查询。

如果您正在优化生产数据库,我强烈建议预处理数据,即使用触发器或作业代理异步更新记录,以便实时访问检索静态数据。

你可以用解释计划得到客观的答案。

对于你的问题, an Exists filter 可能表现最快。