如何删除大型表的嵌套循环连接 [英] How to remove the nested loop join for large tables

问题描述

SQL Server中有3个数据量很大的表，每个表大约包含100000行。只有一个SQL可以从这三个表中提取行。它的性能非常差。

WITH t1 AS 
(
    SELECT 
        LeadId, dbo.get_item_id(Log) AS ItemId, DateCreated AS PriceDate
    FROM 
        (SELECT 
             t.ID, t.LeadID, t.Log, t.DateCreated, f.AskingPrice
         FROM 
             t
         JOIN 
             f ON f.PKID = t.LeadID
         WHERE 
             t.Log LIKE '%xxx%') temp
)
SELECT COUNT(1)
FROM t1
JOIN s ON s.ItemID = t1.ItemId

在检查其估计的执行计划时，我发现它使用了带有大行的嵌套循环联接。看看下面的截图吧。图像的上半部分返回124277行，下半部分执行124277次！我想这就是为什么它这么慢的原因。

我们知道，对于大数据，嵌套循环存在很大的性能问题。如何删除它，并改用哈希联接或其他联接？

编辑：以下是相关功能。

CREATE FUNCTION [dbo].[get_item_Id](@message VARCHAR(200))
RETURNS VARCHAR(200) AS
BEGIN
    DECLARE @result VARCHAR(200),
            @index int

    --Sold in eBay (372827580038).
    SELECT @index = PatIndex('%([0-9]%)%', @message)
    IF(@index = 0)
     SELECT @result='';
    ELSE 
     SELECT @result= REPLACE(REPLACE(REPLACE(SUBSTRING(@message, PatIndex('%([0-9]%)%', @message),8000), '.', ''),'(',''),')','')
    -- Return the result of the function
    RETURN @result
END;

推荐答案

出于某种原因，它已决定执行s cross join t1，然后计算函数(结果别名为Expr1002)，然后对[s].[ItemID]=[Expr1002](而不是执行等联接)进行筛选。

它估计它将有88,969和124,277行进入交叉连接(这意味着它将生成11,056,800,413)

在交叉联接之后执行标量UDF大约110亿次，然后过滤大约110亿行，这看起来确实很疯狂。如果在联接之前对其求值，则它的求值次数会少得多，而且也是等联接，因此也可以使用HASH或MERGE内部联接，并且只读取所有表一次，而不会增加行数。

我在本地复制了它，在创建UDF时行为发生了变化WITH SCHEMABINDING-SQL Server将看到它不访问任何表，并且在其定义中是确定性的。

跟踪标志8606输出似乎支持这是问题所在。 在这两种情况下，简化树阶段都将查询表示为与ScalarUdf上的谓词的交叉联接。标量UDF被注释为"；IsDet"；或"；IsNonDet"；取决于函数是否受模式约束。在前一种情况下，项目标准化阶段在联接之前将计算推回，并为其提供在联接本身中引用的别名，在不确定的情况下不会发生这种情况。

我强烈建议删除此标量函数，并将其替换为内联版本，因为除此之外，非内联标量函数还有许多众所周知的其他性能问题。

新函数将为

CREATE FUNCTION get_item_Id_inline (@message VARCHAR(200))
RETURNS TABLE
AS
    RETURN
      (SELECT item_Id = CASE
                          WHEN PatIndex('%([0-9]%)%', @message) = 0 THEN ''
                          ELSE REPLACE(REPLACE(REPLACE(SUBSTRING(@message, PatIndex('%([0-9]%)%', @message), 8000), '.', ''), '(', ''), ')', '')
                        END) 

和重写的查询

WITH t1
     AS (SELECT t.LeadID,
                i.item_Id     AS ItemId,
                t.DateCreated AS PriceDate
         FROM   t
                CROSS apply dbo.get_item_Id_inline(t.Log) i
                JOIN f
                  ON f.PKID = t.LeadID
         WHERE  t.Log LIKE '%xxx%')
SELECT COUNT(1)
FROM   t1
       JOIN s
         ON s.ItemID = t1.ItemId 

可能仍有一些其他优化的空间，但这将比您当前的执行计划好几个数量级(因为这是灾难性的糟糕)。

这篇关于如何删除大型表的嵌套循环连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！