与"INT"字段上的"> 0"相比，"不是空"的性能损失非常大？

Question

我有一个非常奇怪的问题，如果我测试INT字段中的NULL值，查询会减慢到爬行。

查询是这样的：

WITH CommonRows (Dm2Id) AS (
    SELECT LibraryId
      FROM dbo.Items AS i1
      JOIN Test.Items AS i2
        ON i1.Dm2Id = i2.Dm2Id
     WHERE HasNew = 1
       AND HasOld = 1
       -- Note this: odd things happening here
       AND LibraryId IS NOT NULL
)
SELECT COALESCE(New.ImageLibraryId, Old.ImageLibraryId) AS ImageLibraryId,
        COALESCE(New.Field, Old.Field) AS Field,
        New.Value AS New,
        Old.Value AS Old
   FROM (SELECT ImageLibraryId,
                LEFT(CAST(TitleItemId AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS TitleItemId,
                LEFT(CAST(Title AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS Title,
                LEFT(CAST(Author AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS Author,
                LEFT(CAST(AuthorFirstname AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS AuthorFirstname,
                LEFT(CAST(AuthorLastname AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS AuthorLastname,
                LEFT(CAST(Teaser AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS Teaser,
                LEFT(CAST(TeaserListView AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS TeaserListView,
                LEFT(CAST(Language AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS Language,
                LEFT(CAST(PubYear AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS PubYear,
                LEFT(CAST(FictionNonFiction AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS FictionNonFiction,
                LEFT(CAST(TargetAudience AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS TargetAudience,
                LEFT(CAST(SeriesTitle AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS SeriesTitle,
                LEFT(CAST(SeriesSeqNo AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS SeriesSeqNo,
                LEFT(CAST(SeriesTotalCnt AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS SeriesTotalCnt
           FROM dbo.TitleItems
          WHERE ImageLibraryId IN (SELECT * FROM CommonRows)) p
UNPIVOT (Value FOR Field IN
            (TitleItemId, Title, Author, AuthorFirstname, AuthorLastname, Teaser,
             TeaserListView, Language, PubYear, FictionNonFiction, TargetAudience,
             SeriesTitle, SeriesSeqNo, SeriesTotalCnt)) AS New
   FULL OUTER JOIN
        (SELECT ImageLibraryId,
                LEFT(CAST(TitleItemId AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS TitleItemId,
                LEFT(CAST(Title AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS Title,
                LEFT(CAST(Author AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS Author,
                LEFT(CAST(AuthorFirstname AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS AuthorFirstname,
                LEFT(CAST(AuthorLastname AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS AuthorLastname,
                LEFT(CAST(Teaser AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS Teaser,
                LEFT(CAST(TeaserListView AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS TeaserListView,
                LEFT(CAST(Language AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS Language,
                LEFT(CAST(PubYear AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS PubYear,
                LEFT(CAST(FictionNonFiction AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS FictionNonFiction,
                LEFT(CAST(TargetAudience AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS TargetAudience,
                LEFT(CAST(SeriesTitle AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS SeriesTitle,
                LEFT(CAST(SeriesSeqNo AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS SeriesSeqNo,
                LEFT(CAST(SeriesTotalCnt AS NVARCHAR(MAX)), 20) COLLATE DATABASE_DEFAULT AS SeriesTotalCnt
           FROM CatalogSearch.dbo.TitleItems
          WHERE ImageLibraryId IN (SELECT * FROM CommonRows)) p
UNPIVOT (Value FOR Field IN
            (TitleItemId, Title, Author, AuthorFirstname, AuthorLastname, Teaser,
             TeaserListView, Language, PubYear, FictionNonFiction, TargetAudience,
             SeriesTitle, SeriesSeqNo, SeriesTotalCnt)) AS Old
     ON (New.ImageLibraryId = Old.ImageLibraryId AND New.Field = Old.Field COLLATE DATABASE_DEFAULT)
  WHERE (New.Value <> Old.Value COLLATE DATABASE_DEFAULT
         OR New.Value IS NULL AND Old.Value IS NOT NULL
         OR New.Value IS NOT NULL AND Old.Value IS NULL)
         -- fjernet rækker hvor New.Value indeholder mere information end Old.Value, men Old.Value indgår i starten af New.Value
         AND NOT (New.Field IN ('Teaser', 'TeaserListView') AND New.Value LIKE Old.Value + '%')
         -- fjernet række fordi den har fået et ekstra space med i Old.Value i midten af teksten
         AND NOT (New.Field IN ('Teaser', 'TeaserListView') AND New.ImageLibraryId = 800314)
         -- fjernet række fordi den tolkes som ukendt i New.Value (pga. fejldata i Bibkat) og som dansk i Old.Value
         AND NOT (New.Field = 'Language' AND New.ImageLibraryId = 800252)
        -- This is being tested separately in TitleItemGroups
        AND NOT New.Field = 'TitleItemId'

如果我按照上面写的运行它，查询将运行几分钟，然后开始一次只返回几行。我没有耐心等待查询完成 - 我怀疑它会在完成之前运行 10+ 分钟。

但是，如果我用 AND LibraryId > 0 替换AND LibraryId IS NOT NULL（在这种情况下会给出完全相同的结果 - 我已经检查过了），查询将在 30 秒内运行。

为什么？

Answer 1

查看解释计划。

如果对列使用 IS [NOT] NULL 运算符，SQL 服务器将执行表的完全扫描。如果在 NULLABLE 列上使用索引，则不会使用该索引。

您可以创建一个组合索引，其中包含一个非空列和一个可为空的列，以解决问题。