本文介绍了如何删除大型表的嵌套循环连接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
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)
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
可能仍有一些其他优化的空间，但这将比您当前的执行计划好几个数量级(因为这是灾难性的糟糕)。
这篇关于如何删除大型表的嵌套循环连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！