MS SQL:非常慢的查询，有4个OR条件，但每个条件单独运行非常快

Question

我有两个物理表:

notTempBaseLine with 50k records (index on Version, SrcDimension2_)

notTempTrans with 400k records (index on BaseLineVersion, Dimension2_)

和我有一个查询，它执行2小时

select
BaseLine.RECID,
notTempTrans.recid
from notTempBaseLine baseLine
join notTempTrans
on
baseLine.Version  = notTempTrans.baseLineVersion 
and
(
baseLine.SrcDimension2_ = ''
or 
(baseLine.SrcDimension2_ = '""' and notTempTrans.Dimension2_ = '') 
or
(baseLine.SrcDimension2_ = '!""' and notTempTrans.Dimension2_  '') 
or
baseLine.SrcDimension2_ = notTempTrans.Dimension2_
)

如果我从" or "组-它执行1秒，但当我运行(A或B或C或D) -一些奇怪的事情发生，查询执行2小时。

有什么想法，发生了什么，我怎么能解决它?我不能用UNION代替OR，因为在实际查询中，我有几个相同的OR条件块(OR块和OR块和…)

计划如下:

https://www.brentozar.com/pastetheplan/?id=rkOr1oc85

https://www.brentozar.com/pastetheplan/?id=rJQKyo98c

Answer 1

看起来您可以将条件存储在临时表中并对它们执行条件连接。我希望这对你有帮助。最后的连接可能不是最好的。您可以尝试将最后一个连接转换为where子句。

WITH
condition_mapping
AS
(
SELECT tbl.* FROM (VALUES
(NULL, NULL)
, ( '', NULL)
, ( '', '')
, ( '', '""')
, ( '', '!""')
) tbl ([NT_Dimension2_], [BL_Dimension2_]) 
)
SELECT 
[BaseLine].[RECID]
, [notTempTrans].[recid]
FROM
[notTempBaseLine] AS [baseLine]
INNER JOIN [notTempTrans] ON [baseLine].[Version] = [notTempTrans].[baseLineVersion]
INNER JOIN condition_mapping AS cm 
ON ISNULL(cm.[BL_Dimension2_], [baseLine].[Dimension2_]) = [baseLine].[Dimension2_] 
AND ISNULL(cm.[NT_Dimension2_], [notTempTrans].[Dimension2_]) = [notTempTrans].[Dimension2_]
AND (CASE WHEN cm.[BL_Dimension2_] IS NULL AND cm.[NT_Dimension2_] IS NULL THEN [baseLine].[Dimension2_] ELSE [notTempTrans].[Dimension2_] END) = [notTempTrans].[Dimension2_];

Answer 2

我在您的查询计划中看到群集索引扫描。根据您的陈述，似乎正在扫描两个表以查找适当的数据。这使我相信您的索引没有适当地设置，并且您可能会错过减少搜索空间的机会。我建议在表中添加索引(见下文)，以使用where语句中定义的相等比较操作。

PS:将相等性比较移到where语句而不是join，它有异味。

select
BaseLine.RECID,
notTempTrans.recid
from notTempBaseLine baseLine
join notTempTrans
on
baseLine.Version  = notTempTrans.baseLineVersion 
WHERE
baseLine.SrcDimension2_ = ''
or 
(baseLine.SrcDimension2_ = '""' and notTempTrans.Dimension2_ = '') 
or
(baseLine.SrcDimension2_ = '!""' and notTempTrans.Dimension2_ <> '') 
or
baseLine.SrcDimension2_ = notTempTrans.Dimension2_

好运。

CREATE NONCLUSTERED INDEX [notTempBaseLine_SrcDimension2__Version] ON [dbo].[notTempBaseLine]
(
[SrcDimension2_] ASC, [Version] ASC
) INCLUDE([RECID]) WITH (ONLINE = OFF, MAXDOP=0);
GO
CREATE NONCLUSTERED INDEX [notTempTrans_Dimension2_BaseLineVersion] ON [dbo].[notTempTrans]
(
[Dimension2_] ASC, [BaseLineVersion] ASC
) INCLUDE([recid]) WITH (ONLINE = OFF, MAXDOP=0);
GO

Answer 3

试试这个:

select  BaseLine.RECID,
notTempTrans.recid
from notTempBaseLine AS baseLine
join notTempTrans
on baseLine.Version = notTempTrans.baseLineVersion
and
(
baseLine.SrcDimension2_ IN ('', notTempTrans.Dimension2_)
or 
(notTempTrans.Dimension2_ = '' AND (baseLine.SrcDimension2_ = '""' OR baseLine.SrcDimension2_ = '!""'))
)