SQL Server视图带有“select where x is not null”需要很长时间才能完成。

Question

我有一个复杂的视图，描述在这里：多个表的视图。需要删除由1个表定义的“重复项”

我在其中使用了Cross Apply，代码如下：（请查看上面的网址以理解该视图）

    SELECT    dbo.InstellingGegevens.INST_SUBTYPE
            , dbo.InstellingGegevens.INST_BRON
            , dbo.InstellingGegevens.INST_INSTELLINGSNUMMER
            , dbo.InstellingGegevens.INST_NAAM
            , dbo.InstellingGegevens.INST_KORTENAAM
            , dbo.InstellingGegevens.INST_VESTIGINGSNAAM
            , dbo.InstellingGegevens.INST_ROEPNAAM
            , dbo.InstellingGegevens.INST_STATUUT
            , dbo.InstellingGegevens.ONDERWIJSNIVEAU_REF
            , dbo.InstellingGegevens.ONDERWIJSSOORT_REF
            , dbo.InstellingGegevens.DATUM_TOT
            , dbo.InstellingGegevens.DATUM_VAN
            , dbo.InstellingGegevens.VERBOND_REF
            , dbo.InstellingGegevens.VSKO_LID
            , dbo.InstellingGegevens.NET_REF
            , dbo.Instellingen.Inst_ID
            , dbo.Instellingen.INST_TYPE
            , dbo.Instellingen.INST_REF
            , dbo.Instellingen.INST_LOC_REF
            , dbo.Instellingen.INST_LOCNR
            , dbo.Instellingen.Opt_KalStandaard
            , dbo.InstellingTelecom.INST_TEL
            , dbo.InstellingTelecom.INST_FAX
            , dbo.InstellingTelecom.INST_EMAIL
            , dbo.InstellingTelecom.INST_WEB
            , dbo.InstellingAdressen.SOORT
            , dbo.InstellingAdressen.STRAAT
            , dbo.InstellingAdressen.POSTCODE
            , dbo.InstellingAdressen.GEMEENTE
            , dbo.InstellingAdressen.GEM_REF
            , dbo.InstellingAdressen.FUSIEGEM_REF
            , dbo.InstellingAdressen.FUSIEGEM
            , dbo.InstellingAdressen.ALFA_G
            , dbo.InstellingAdressen.PROVINCIE
            , dbo.InstellingAdressen.BISDOM
            , dbo.InstellingAdressen.ARRONDISSEMENT
            , dbo.InstellingAdressen.GEWEST
            , dbo.InstellingContPersDirecteurs.AANSPREKING
            , dbo.InstellingContPersDirecteurs.CONTACTPERSOON
            , dbo.InstellingContPersDirecteurs.FUNCTIE
            , InstellingLogin.Inst_Gebruikersnaam
            , InstellingLogin.Inst_Concode
            , InstellingLogin.Inst_DirCode
            , InstellingLogin.DOSSNR
            , InstellingLogin.Instelling_ID
FROM dbo.InstellingGegevens 
RIGHT OUTER JOIN dbo.Instellingen 
    ON dbo.InstellingGegevens.INST_TYPE = dbo.Instellingen.INST_TYPE 
    AND dbo.InstellingGegevens.INST_REF = dbo.Instellingen.INST_REF 
    AND dbo.InstellingGegevens.INST_LOC_REF = dbo.Instellingen.INST_LOC_REF
    AND dbo.InstellingGegevens.INST_LOCNR = dbo.Instellingen.INST_LOCNR 
LEFT OUTER JOIN dbo.InstellingTelecom 
    ON dbo.InstellingGegevens.INST_TYPE = dbo.InstellingTelecom.INST_TYPE 
    AND dbo.InstellingGegevens.INST_REF = dbo.InstellingTelecom.INST_REF 
    AND dbo.InstellingGegevens.INST_LOC_REF = dbo.InstellingTelecom.INST_LOC_REF 
LEFT OUTER JOIN dbo.InstellingAdressen 
    ON dbo.InstellingGegevens.INST_TYPE = dbo.InstellingAdressen.INST_TYPE 
    AND  dbo.InstellingGegevens.INST_REF = dbo.InstellingAdressen.INST_REF 
    AND  dbo.InstellingGegevens.INST_LOC_REF = dbo.InstellingAdressen.INST_LOC_REF 
LEFT OUTER JOIN dbo.InstellingContPersDirecteurs 
    ON dbo.InstellingGegevens.INST_TYPE = dbo.InstellingContPersDirecteurs.INST_TYPE 
    AND dbo.InstellingGegevens.INST_REF = dbo.InstellingContPersDirecteurs.INST_REF 
    AND dbo.InstellingGegevens.INST_LOC_REF = dbo.InstellingContPersDirecteurs.INST_LOC_REF 
CROSS APPLY
      (SELECT  TOP (1) *
        FROM  InstellingLogin AS il
        WHERE Instellingen.INST_LOC_REF = il.Inst_Loc_REF 
            AND Instellingen.INST_LOCNR = il.Inst_Loc_Nr 
            AND Instellingen.INST_REF = il.Inst_InstellingIKON_REF 
            AND Instellingen.INST_TYPE = il.Inst_InstellingIKONType
        ORDER BY CASE 
                    WHEN il.datum_tot IS NULL 
                    THEN 0 ELSE 1 
                    END
                    , il.datum_tot DESC) InstellingLogin

这个视图返回了大约5.5k行数据，只用了约1秒钟。速度很快！

但是！

当我使用where子句调用这个视图时：

SELECT *
  FROM [Tink].[dbo].[InstellingAlleDetails]
  where gemeente is not null and (DATUM_TOT is null or DATUM_TOT > GETDATE())
  order by GEMEENTE, POSTCODE,STRAAT, INST_NAAM

返回所有行需要1分20秒。

当我删除gemeente不为空的部分时，时间又变成了1秒。

Gemeente是一个varchar（255）。我也尝试了使用Inst_Naam不为空，时间大约也需要1分30秒。

为什么这个不为空要花费这么长时间？更重要的是：我该如何解决这个问题？

- Stefanvds

---

“Inst_Naam”在此视图中未显示...正如HLGEM所说，您确实需要运行两个查询（带/不带“is null”条件），并比较执行计划。如果您能确定差异，请将它们添加到您的问题中。 - Damien_The_Unbeliever

Answer 1

我不知道原因。可能是SQL Server生成了一个不太好的查询计划。

你可以尝试先运行没有gemeente is not null条件的查询，并将结果放入临时表中，然后再用gemeente is not null条件查询临时表。

select *
into #TempTable
from YourView

select *
from #TempTable
where gemeente is not null

drop table #TempTable

- Mikael Eriksson

---

那就意味着要创建一个表，还是有一种简单的方法可以创建临时表？ - Stefanvds

@Stefanvds - 更新了答案，加入了一些创建临时表、使用它并在完成后删除它的代码。 - Mikael Eriksson

Answer 2

首先检查带有和不带有“不为空”的查询的执行计划，看看它们之间的差异。

顺便问一下，这些查询中是否有与其他视图的连接？这可能会导致严重的性能问题。

- HLGEM

---

是的，InstellingContPersDirecteurs 是一个视图，但我认为这不是问题所在。在没有 cross apply 的情况下，它运行速度很快（1秒），我猜想这可能与 cross apply 有关。 - Stefanvds

我怀疑问题并不在于 CROSS APPLY。对于这样一个复杂的查询（包含多个视图、外连接和交叉应用），你可能已经达到了优化器试图从“IS NOT NULL”谓词中获得好处的极限，从而导致错误的结论。将结果插入到一个中间表中，然后处理该中间表，是我个人发现防止这种情况的唯一方法。 - MatBailie