MS SQL: extremely slow query with 4 OR condition, but each condition alone run very quickly-CodePudding

I have two physical tables:

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

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

and I have a query, which executing for 2 hours


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_
    )

If I run query with any one condition from "ORs" group - it executing for 1 sec, but when I run (A or B or C or D) - something strange happens and query executing for 2 hours.

Any ideas, what happening and how I can fix it? I cant replace OR with UNION, cause in real query I have few same blocks of ORs conditions (block-of-OR AND block-of-OR AND ... )

Here are the plans:

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

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

CodePudding user response：

It looks like you can store the conditions in a temp table and do a conditional join to them. I hope this helps. The last join may not be the best. You could try converting the last join to a where clause.

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_];

CodePudding user response：

I am seeing in your query plan a clustered index scan. And based on your statement, it appears that both tables are being scanned to find appropriate data. That leads me to believe that your indexes are not set up appropriately and that you may miss out on an opportunity to reduce the search space. I recommend adding indexes to your table (see below) to use the equality comparison operation defined in your where statement.

PS: move your equality comparison to where statement instead of join, it smells.

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_

Good luck.

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