SELECT语句with，*在列列表中比不带*的相同语句更快

Question

Situation

当使用SELECT语句使用定义的一组列查询数据库时，结果将在大约21秒内收到。

如果定义的列列表末尾有一个附加星号(, *)，则查询将在2秒内返回结果。

查询执行计划

执行计划有很大不同。

您可以通过来自良好的实际查询执行计划的链接找到错误的实际查询执行计划和PasteThePlan。

在列列表(末尾)中包含*的

语句

            SELECT    -- DISTINCT -- 27.04.2020
                'SchuelerKlasse' AS EcoQuery,
                VX_PERSON.PER_MAN_ID, VX_PERSON.PER_ID, VX_PERSON.PER_NAME, VX_PERSON.PER_VORNAME, VX_PERSON.PER_LB_PER_ID, 
                VX_PERSON.PER_GESCHLECHT, VX_PERSON.PER_GEBURTSDATUM, VX_PERSON.PER_TELP, VX_PERSON.PER_MAILP, VX_PERSON.PER_NATP, VX_PERSON.PER_VERSICHERTENNUMMER, VX_PERSON.PER_LAND,
                VX_ADRESSE.ADR_STRASSE, VX_ADRESSE.ADR_PLZ, VX_ADRESSE.ADR_ORT,
                VX_KLASSE.KL_CODE, VX_KLASSE.KL_BEZEICHNUNG,
                VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDE_STATUS, 
                VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDETYP, VX_KLASSEABSCHNITTSCHUELER.KAS_ABSCHNITTSNR,
                VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSE, VX_KLASSE_ZEITRAUM.KLZ_ZR_NR,
                VX_ZEITRAUM.ZR_BEGINN, VX_ZEITRAUM.ZR_ENDE
                ,'' AS FA_CODE
                ,'' AS FA_BEZ_STP, '' AS FA_BEZ_STP_LANG
                , '' AS EcoOrig_FA_CODE, '' AS EcoOrig_FA_BEZ_STP, '' AS EcoOrig_FA_BEZ_STP_LANG
                , VX_ANGEBOT.ANG_BEGINN
            
 ,* 

            FROM 
                ECOLST.VX_KLASSE_ZEITRAUM, 
                ECOLST.VX_PERSON, 
                ECOLST.VX_KLASSE, 
                ECOLST.VX_KLASSEABSCHNITTSCHUELER, 
                ECOLST.VX_ZEITRAUM, 
                ECOLST.VX_ADRESSE 
                , ECOSYS.T_KLASSE
                , ECOLST.VX_ANGEBOT

            WHERE  
                    VX_KLASSE_ZEITRAUM.klz_kl_id = VX_KLASSE.kl_id 
                AND VX_KLASSE_ZEITRAUM.klz_zr_id = VX_ZEITRAUM.zr_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_ang_id = VX_KLASSE.kl_ang_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_zr_id = VX_ZEITRAUM.zr_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_per_id = VX_PERSON.per_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_kl_id = VX_KLASSE.kl_id 
                AND VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDE_STATUS LIKE 'De%'  -- LIKE 'Definitiv%'
                AND VX_PERSON.per_id = VX_ADRESSE.adr_per_id 
                AND VX_PERSON.per_man_id = VX_KLASSE.kl_man_id
                AND VX_KLASSE.KL_ANG_ID = VX_ANGEBOT.ANG_ID
                AND VX_KLASSE.KL_MAN_ID = 15 
                AND VX_KLASSE.KL_ID = T_KLASSE.KL_ID
                AND T_KLASSE.KL_STATUS_ID = 491   -- d.h. TS_CODE.CODE_UP_BEZEICHNUNG = 'AKTIV'
            

                AND VX_KLASSE.KL_KLASSENTYP_ID IN (742,743,1235,1926,2075,2076,2078,2079,2080,2081,2086,2103,2118,2119,2122,2152,2252,2308,2416)
        

                AND VX_PERSON.PER_NP = 1   -- Natürliche Person
                AND LEN(LTRIM(RTRIM(VX_PERSON.PER_VORNAME))) > 0        -- TRIM() kann erst ab SQL Server 2017 verwendet werden
                AND LEN(LTRIM(RTRIM(VX_PERSON.PER_NAME))) > 0           -- TRIM() kann erst ab SQL Server 2017 verwendet werden
        
 AND VX_ZEITRAUM.zr_beginn <= CONVERT(DATETIME, '20.05.2023', 104) 
 AND VX_ZEITRAUM.zr_ende   >= CONVERT(DATETIME, '14.02.2023', 104) 
 AND VX_PERSON.per_man_id IN ( 15 ) 

                --AND VX_Person.PER_ID IN  (233777,233779)

问题

一般建议在定义列列表时不要使用*，但在我的示例中，在列列表末尾添加, *可以显著加快查询速度。(由21s降至2s)

在实际的执行计划中没有Missing索引建议。

I假设与使用 , * in语句时返回的特定列有关，这些语句可能包含在查询优化器认为有用的索引中，但我不确定如何指向这些列。

1. 我需要创建哪些索引才能说服Server运行性能不佳的语句，不包含, *列列表，并使用与列列表中包含额外, *的<#>表演性语句类似的计划？
2. 我是否必须分析好的执行计划中使用的所有索引，并创建减少的索引(省略某些列)，以便查询优化器考虑对语句使用类似的好执行计划，而不需要额外的, *？

按照

的建议尝试解决方案

1. 应用上述解决方案的OPTION (MIN_GRANT_PERCENT = 10, MAX_GRANT_PERCENT = 15)只暂时提高了大约1小时的性能。之后，查询返回到错误的执行计划。我不知道为什么..。
2. 数据库兼容性级别使用以下命令将数据库的兼容性级别降低到110 ( Server 2012)，从而使上述查询的性能不断提高，而无需在列列表中添加,*。使用GO ALTER COMPATIBILITY_LEVEL =110Go 兼容级别为110的查询执行计划显示，查询优化器在检索数据时选择了完全不同的方法，并且在分配正确的内存量(110 MB)方面没有任何问题。

后续问题

是否将兼容级别设置为110是我唯一的选择？

附加反馈

埃里克回答中提到的不祥的函数fi_kla_is_abschlussklasse是由ECOLST.VX_KLASSE_ZEITRAUM视图中的列VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSE触发的。底层表在检索数据时调用标量值函数。该函数本身返回0或1，这取决于学生是否在毕业班(1)。

但是，当兼容级别设置为110时，该函数似乎对查询的持续时间影响不大( Server 2012)。有关细节，请参阅兼容级别为110的查询执行计划。

Answer 1

虽然这两个查询计划有许多不同之处，但在较慢的计划中遇到的问题主要发生在溢出操作中，原因是内存分配不足：

至少还有一次漏油事件，但影响不大。

更快的计划没有溢出的原因是因为它获得了更高的内存授权，SQL Server根据通过内存消耗操作符的行数和估计大小来估算内存授权，比如排序和散列。

以下是内存授予信息：

在如此复杂的查询中很难获得准确的基数估计，但您可以尝试：

• 更新统计
• 使用遗留CE：OPTION(QUERYTRACEON 9481)
• 使用#temp表拆分查询
• 使用MIN_GRANT_PERCENT提示获得更高的内存授权

值得注意的是，对于此查询，您有一个抑制并行性的标量UDF，以及几个可能导致抛出估计值的非SARGable谓词。

功能：

查询反模式：

AND LEN(LTRIM(RTRIM(VX_PERSON.PER_VORNAME))) > 0
AND LEN(LTRIM(RTRIM(VX_PERSON.PER_NAME))) > 0

可以简化为查找单个字符通配符：

AND VX_PERSON.PER_VORNAME LIKE '_%'
AND VX_PERSON.PER_NAME LIKE '_%'

或者只是寻找非空字符串：

AND VX_PERSON.PER_VORNAME <> ''
AND VX_PERSON.PER_NAME <> ''