如何降低“排序”成本(T)

Question

我有一个T查询。使用(SSMS)中的显示估计执行计划，我发现最后一个sort操作将消耗83%的totalt成本。

奇怪的是，当我删除WHERE和ORDER BY时，它只会增加sort成本。因此，我的问题是:在这一环节中，还有什么能促成成本？

查询

SELECT                                                                  
o.boardordrenr      as 'SenDX Board Run', 
bo.boardordrerunnr  as 'RMED Board Run',                                                       
o.ordrerunnr        as 'Dispensing Run',                                                                             
o.ordreidnr         as 'Board ID', 
o.boardnr           as 'Board No.',
ba.raekkenr         as 'Row',       
ba.arraynr          as 'Array',
s0.sensor           as 'Sensor',
ks.diameter         as 'Cavity Diameter',
ks.kavitet_dybde    as 'Cavity Depth',
ms1.tykkelse        as 'CA Thickness',
ms1.membrane_Stdev  as 'CA Std. Dev.',
s1.void_procent     as 'CA Void Fraction',
s1.skannet_dt       as 'CA Scan Date',
ms2.tykkelse        as 'OM Thickness',
ms2.membrane_Stdev  as 'OM Std. Dev.',
s2.void_procent     as 'OM Void Fraction',
s2.skannet_dt       as 'OM Scan Date',
ft_sc_par.vaerdi    as 'Sens'


FROM 
boardordre bo                                                                        
INNER JOIN ordre o          ON bo.boardordrenr = o.boardordrenr AND DATEDIFF(MONTH, o.oprettet_dt, GETDATE()) < 3 AND o.ordrevarenr = 932029                                                 
INNER JOIN boardarray ba    ON ba.ordrenr = o.ordrenr    
LEFT JOIN sensorkassette sc ON sc.boardarraynr_bund = ba.boardarraynr

LEFT JOIN skanning s0           ON s0.boardarraynr = ba.boardarraynr AND s0.overflade_index = 0 AND s0.sensor in ('Glu','Lac')  -- Cavity
LEFT JOIN skanning s1           ON s1.boardarraynr = ba.boardarraynr AND s1.overflade_index = 1 AND s1.sensor = s0.sensor       -- CA Membrane
LEFT JOIN skanning s2           ON s2.boardarraynr = ba.boardarraynr AND s2.overflade_index = 2 AND s2.sensor = s0.sensor       -- Outer Membrane

LEFT JOIN kavitetskanning ks    ON ks.skanningnr  = s0.skanningnr    
LEFT JOIN membranskanning ms1   ON ms1.skanningnr = s1.skanningnr  
LEFT JOIN membranskanning ms2   ON ms2.skanningnr = s2.skanningnr AND (ms2.membrannavn = 'YM_UDV' OR ms2.membrannavn = 'YM')

LEFT JOIN funktionstest_sc ft_sc            ON ft_sc.sensorkassette_chipnr = sc.sensorkassette_chipnr
LEFT JOIN funktionstest_sc_param ft_sc_par  ON ft_sc_par.funktionstest_sc_nr = ft_sc.funktionstest_sc_nr AND ft_sc_par.sensor = s0.sensor AND ft_sc_par.parameter = 'Sens'

执行计划(链接)

actual.sqlplan

Answer 1

您做了一个不同的，为了高效地执行不同的SQL Server，可能首先需要进行排序。你的执行计划会给你你需要知道的答案。在这种情况下，索引肯定会有所帮助。

Answer 2

将索引放在所有连接条件上

S0.传感器(“Glu”，“Lac”)将其转化为内部连接

尝尝这个

FROM 
    boardordre bo 
INNER JOIN 
    ordre o ON bo.boardordrenr = o.boardordrenr 
            AND o.ordrevarenr = 932029  
            AND DATEDIFF(MONTH, o.oprettet_dt, GETDATE()) < 3 
INNER JOIN 
    boardarray ba ON ba.ordrenr = o.ordrenr    
LEFT JOIN 
    sensorkassette sc ON sc.boardarraynr_bund = ba.boardarraynr
INNER JOIN 
    skanning s0 ON s0.boardarraynr = ba.boardarraynr 
                AND s0.overflade_index = 0 -- Cavity 
                AND s0.sensor in ('Glu','Lac')

放下那个地方