SQL Server : COALESCE causing excessive runtime

Question

早起的人们，

我在 COALESCE 方面遇到了一个小问题，导致将数据写入表的存储过程过度运行。 (不使用 COALESCE 需要 4 分钟，使用 COALESCE 则需要 1 小时 30 分钟左右)。

我需要此临时表的 f_ 列清除 NULL，因为这些列稍后将在仓库中用于微积分，然后显示到 Cognos reporting studio。

有 161 列需要这个，我也尝试过执行“CASE WHEN X IS NULL THEN 0 ELSE X”，这显然影响较小，但仍然需要一个半小时。

还在构建的表中添加了“DEFAULT 0”，但没有任何乐趣。

存储过程如下(提前为它的庞大规模道歉)

TRUNCATE TABLE
    ST_PIT_BOOKINGCOSTS

INSERT INTO
    ST_PIT_BOOKINGCOSTS
SELECT DISTINCT
    ST_PIT_BOOKINGHEADERS.i_SK_Bkg,
    ST_PIT_BOOKINGHEADERS.i_SK_Version,
    0 AS f_BkgTransportPax,
    0 AS f_BkgTransportQuantity,
    0 AS f_BkgTransportAdults,
    0 AS f_BkgTransportChildren,
    0 AS f_BkgTransportInfants,
    0 AS f_BkgTransportRevenue,
    0 AS f_BkgTransportCost,
    COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Pax,0) AS f_BkgFltPax,
    COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Quantity,0) AS f_BkgFltQuantity,
    COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Adults,0) AS f_BkgFltAdults,
    COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Children,0) AS f_BkgFltChildren,
    COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Infants,0) AS f_BkgFltInfants,
    COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Revenue,0) AS f_BkgFltRevenue,
    COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Cost,0) AS f_BkgFltCost,
    COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Pax,0) AS f_BkgFerryPax,
    COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Quantity,0) AS f_BkgFerryQuantity,
    COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Adults,0) AS f_BkgFerryAdults,
    COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Children,0) AS f_BkgFerryChildren,
    COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Infants,0) AS f_BkgFerryInfants,
    COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Revenue,0) AS f_BkgFerryRevenue,
    COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Cost,0) AS f_BkgFerryCost,
    COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Pax,0) AS f_BkgTrainPax,
    COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Quantity,0) AS f_BkgTrainQuantity,
    COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Adults,0) AS f_BkgTrainAdults,
    COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Children,0) AS f_BkgTrainChildren,
    COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Infants,0) AS f_BkgTrainInfants,
    COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Revenue,0) AS f_BkgTrainRevenue,
    COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Cost,0) AS f_BkgTrainCost,
    COALESCE(ST_V_AC_ACCOMCOSTS.f_Pax,0) AS f_BkgAccomPax,
    COALESCE(ST_V_AC_ACCOMCOSTS.f_Quantity,0) AS f_BkgAccomQuantity,
    COALESCE(ST_V_AC_ACCOMCOSTS.f_Adults,0) AS f_BkgAccomAdults,
    COALESCE(ST_V_AC_ACCOMCOSTS.f_Children,0) AS f_BkgAccomChildren,
    COALESCE(ST_V_AC_ACCOMCOSTS.f_Infants,0) AS f_BkgAccomInfants,
    COALESCE(ST_V_AC_ACCOMCOSTS.f_Revenue,0) AS f_BkgAccomRevenue,
    COALESCE(ST_V_AC_ACCOMCOSTS.f_Cost,0) AS f_BkgAccomCost,
    COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Pax,0) AS f_BkgExtraACTIPax,
    COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Quantity,0) AS f_BkgExtraACTIQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Adults,0) AS f_BkgExtraACTIAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Children,0) AS f_BkgExtraACTIChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Infants,0) AS f_BkgExtraACTIInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Revenue,0) AS f_BkgExtraACTIRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Cost,0) AS f_BkgExtraACTICost,
    COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Pax,0) AS f_BkgExtraCARHPax,
    COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Quantity,0) AS f_BkgExtraCARHQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Adults,0) AS f_BkgExtraCARHAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Children,0) AS f_BkgExtraCARHChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Infants,0) AS f_BkgExtraCARHInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Revenue,0) AS f_BkgExtraCARHRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Cost,0) AS f_BkgExtraCARHCost,
    COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Pax,0) AS f_BkgExtraCARIPax,
    COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Quantity,0) AS f_BkgExtraCARIQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Adults,0) AS f_BkgExtraCARIAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Children,0) AS f_BkgExtraCARIChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Infants,0) AS f_BkgExtraCARIInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Revenue,0) AS f_BkgExtraCARIRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Cost,0) AS f_BkgExtraCARICost,
    COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Pax,0) AS f_BkgExtraCHLDPax,
    COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Quantity,0) AS f_BkgExtraCHLDQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Adults,0) AS f_BkgExtraCHLDAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Children,0) AS f_BkgExtraCHLDChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Infants,0) AS f_BkgExtraCHLDInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Revenue,0) AS f_BkgExtraCHLDRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Cost,0) AS f_BkgExtraCHLDCost,
    COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Pax,0) AS f_BkgExtraCLUBPax,
    COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Quantity,0) AS f_BkgExtraCLUBQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Adults,0) AS f_BkgExtraCLUBAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Children,0) AS f_BkgExtraCLUBChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Infants,0) AS f_BkgExtraCLUBInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Revenue,0) AS f_BkgExtraCLUBRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Cost,0) AS f_BkgExtraCLUBCost,
    COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Pax,0) AS f_BkgExtraCRECPax,
    COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Quantity,0) AS f_BkgExtraCRECQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Adults,0) AS f_BkgExtraCRECAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Children,0) AS f_BkgExtraCRECChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Infants,0) AS f_BkgExtraCRECInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Revenue,0) AS f_BkgExtraCRECRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Cost,0) AS f_BkgExtraCRECCost,
    COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Pax,0) AS f_BkgExtraEQUIPax,
    COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Quantity,0) AS f_BkgExtraEQUIQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Adults,0) AS f_BkgExtraEQUIAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Children,0) AS f_BkgExtraEQUIChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Infants,0) AS f_BkgExtraEQUIInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Revenue,0) AS f_BkgExtraEQUIRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Cost,0) AS f_BkgExtraEQUICost,
    COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Pax,0) AS f_BkgExtraEXCUPax,
    COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Quantity,0) AS f_BkgExtraEXCUQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Adults,0) AS f_BkgExtraEXCUAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Children,0) AS f_BkgExtraEXCUChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Infants,0) AS f_BkgExtraEXCUInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Revenue,0) AS f_BkgExtraEXCURevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Cost,0) AS f_BkgExtraEXCUCost,
    COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Pax,0) AS f_BkgExtraFLTRPax,
    COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Quantity,0) AS f_BkgExtraFLTRQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Adults,0) AS f_BkgExtraFLTRAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Children,0) AS f_BkgExtraFLTRChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Infants,0) AS f_BkgExtraFLTRInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Revenue,0) AS f_BkgExtraFLTRRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Cost,0) AS f_BkgExtraFLTRCost,
    COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Pax,0) AS f_BkgExtraINSRPax,
    COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Quantity,0) AS f_BkgExtraINSRQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Adults,0) AS f_BkgExtraINSRAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Children,0) AS f_BkgExtraINSRChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Infants,0) AS f_BkgExtraINSRInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Revenue,0) AS f_BkgExtraINSRRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Cost,0) AS f_BkgExtraINSRCost,
    COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Pax,0) AS f_BkgExtraOTHRPax,
    COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Quantity,0) AS f_BkgExtraOTHRQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Adults,0) AS f_BkgExtraOTHRAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Children,0) AS f_BkgExtraOTHRChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Infants,0) AS f_BkgExtraOTHRInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Revenue,0) AS f_BkgExtraOTHRRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Cost,0) AS f_BkgExtraOTHRCost,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Pax,0) AS f_BkgExtraSKIPPax,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Quantity,0) AS f_BkgExtraSKIPQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Adults,0) AS f_BkgExtraSKIPAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Children,0) AS f_BkgExtraSKIPChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Infants,0) AS f_BkgExtraSKIPInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Revenue,0) AS f_BkgExtraSKIPRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Cost,0) AS f_BkgExtraSKIPCost,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Pax,0) AS f_BkgExtraSKITPax,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Quantity,0) AS f_BkgExtraSKITQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Adults,0) AS f_BkgExtraSKITAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Children,0) AS f_BkgExtraSKITChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Infants,0) AS f_BkgExtraSKITInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Revenue,0) AS f_BkgExtraSKITRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Cost,0) AS f_BkgExtraSKITCost,
    COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Pax,0) AS f_BkgExtraSPIOPax,
    COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Quantity,0) AS f_BkgExtraSPIOQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Adults,0) AS f_BkgExtraSPIOAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Children,0) AS f_BkgExtraSPIOChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Infants,0) AS f_BkgExtraSPIOInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Revenue,0) AS f_BkgExtraSPIORevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Cost,0) AS f_BkgExtraSPIOCost,
    COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Pax,0) AS f_BkgExtraSPORPax,
    COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Quantity,0) AS f_BkgExtraSPORQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Adults,0) AS f_BkgExtraSPORAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Children,0) AS f_BkgExtraSPORChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Infants,0) AS f_BkgExtraSPORInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Revenue,0) AS f_BkgExtraSPORRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Cost,0) AS f_BkgExtraSPORCost,
    COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Pax,0) AS f_BkgExtraSTNDPax,
    COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Quantity,0) AS f_BkgExtraSTNDQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Adults,0) AS f_BkgExtraSTNDAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Children,0) AS f_BkgExtraSTNDChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Infants,0) AS f_BkgExtraSTNDInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Revenue,0) AS f_BkgExtraSTNDRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Cost,0) AS f_BkgExtraSTNDCost,
    COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Pax,0) AS f_BkgExtraTRAFPax,
    COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Quantity,0) AS f_BkgExtraTRAFQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Adults,0) AS f_BkgExtraTRAFAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Children,0) AS f_BkgExtraTRAFChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Infants,0) AS f_BkgExtraTRAFInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Revenue,0) AS f_BkgExtraTRAFRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Cost,0) AS f_BkgExtraTRAFCost,
    COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Pax,0) AS f_BkgExtraXXXXPax,
    COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Quantity,0) AS f_BkgExtraXXXXQuantity,
    COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Adults,0) AS f_BkgExtraXXXXAdults,
    COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Children,0) AS f_BkgExtraXXXXChildren,
    COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Infants,0) AS f_BkgExtraXXXXInfants,
    COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Revenue,0) AS f_BkgExtraXXXXRevenue,
    COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Cost,0) AS f_BkgExtraXXXXCost,
    0 AS f_DiffTransportPax,
    0 AS f_DiffTransportQuantity,
    0 AS f_DiffTransportAdults,
    0 AS f_DiffTransportChildren,
    0 AS f_DiffTransportInfants,
    0 AS f_DiffTransportRevenue,
    0 AS f_DiffTransportCost,
    0 AS f_DiffFltPax,
    0 AS f_DiffFltQuantity,
    0 AS f_DiffFltAdults,
    0 AS f_DiffFltChildren,
    0 AS f_DiffFltInfants,
    0 AS f_DiffFltRevenue,
    0 AS f_DiffFltCost,
    0 AS f_DiffFerryPax,
    0 AS f_DiffFerryQuantity,
    0 AS f_DiffFerryAdults,
    0 AS f_DiffFerryChildren,
    0 AS f_DiffFerryInfants,
    0 AS f_DiffFerryRevenue,
    0 AS f_DiffFerryCost,
    0 AS f_DiffTrainPax,
    0 AS f_DiffTrainQuantity,
    0 AS f_DiffTrainAdults,
    0 AS f_DiffTrainChildren,
    0 AS f_DiffTrainInfants,
    0 AS f_DiffTrainRevenue,
    0 AS f_DiffTrainCost,
    0 AS f_DiffAccomPax,
    0 AS f_DiffAccomQuantity,
    0 AS f_DiffAccomAdults,
    0 AS f_DiffAccomChildren,
    0 AS f_DiffAccomInfants,
    0 AS f_DiffAccomRevenue,
    0 AS f_DiffAccomCost,
    0 AS f_DiffExtraACTIPax,
    0 AS f_DiffExtraACTIQuantity,
    0 AS f_DiffExtraACTIAdults,
    0 AS f_DiffExtraACTIChildren,
    0 AS f_DiffExtraACTIInfants,
    0 AS f_DiffExtraACTIRevenue,
    0 AS f_DiffExtraACTICost,
    0 AS f_DiffExtraCARHPax,
    0 AS f_DiffExtraCARHQuantity,
    0 AS f_DiffExtraCARHAdults,
    0 AS f_DiffExtraCARHChildren,
    0 AS f_DiffExtraCARHInfants,
    0 AS f_DiffExtraCARHRevenue,
    0 AS f_DiffExtraCARHCost,
    0 AS f_DiffExtraCARIPax,
    0 AS f_DiffExtraCARIQuantity,
    0 AS f_DiffExtraCARIAdults,
    0 AS f_DiffExtraCARIChildren,
    0 AS f_DiffExtraCARIInfants,
    0 AS f_DiffExtraCARIRevenue,
    0 AS f_DiffExtraCARICost,
    0 AS f_DiffExtraCHLDPax,
    0 AS f_DiffExtraCHLDQuantity,
    0 AS f_DiffExtraCHLDAdults,
    0 AS f_DiffExtraCHLDChildren,
    0 AS f_DiffExtraCHLDInfants,
    0 AS f_DiffExtraCHLDRevenue,
    0 AS f_DiffExtraCHLDCost,
    0 AS f_DiffExtraCLUBPax,
    0 AS f_DiffExtraCLUBQuantity,
    0 AS f_DiffExtraCLUBAdults,
    0 AS f_DiffExtraCLUBChildren,
    0 AS f_DiffExtraCLUBInfants,
    0 AS f_DiffExtraCLUBRevenue,
    0 AS f_DiffExtraCLUBCost,
    0 AS f_DiffExtraCRECPax,
    0 AS f_DiffExtraCRECQuantity,
    0 AS f_DiffExtraCRECAdults,
    0 AS f_DiffExtraCRECChildren,
    0 AS f_DiffExtraCRECInfants,
    0 AS f_DiffExtraCRECRevenue,
    0 AS f_DiffExtraCRECCost,
    0 AS f_DiffExtraEQUIPax,
    0 AS f_DiffExtraEQUIQuantity,
    0 AS f_DiffExtraEQUIAdults,
    0 AS f_DiffExtraEQUIChildren,
    0 AS f_DiffExtraEQUIInfants,
    0 AS f_DiffExtraEQUIRevenue,
    0 AS f_DiffExtraEQUICost,
    0 AS f_DiffExtraEXCUPax,
    0 AS f_DiffExtraEXCUQuantity,
    0 AS f_DiffExtraEXCUAdults,
    0 AS f_DiffExtraEXCUChildren,
    0 AS f_DiffExtraEXCUInfants,
    0 AS f_DiffExtraEXCURevenue,
    0 AS f_DiffExtraEXCUCost,
    0 AS f_DiffExtraFLTRPax,
    0 AS f_DiffExtraFLTRQuantity,
    0 AS f_DiffExtraFLTRAdults,
    0 AS f_DiffExtraFLTRChildren,
    0 AS f_DiffExtraFLTRInfants,
    0 AS f_DiffExtraFLTRRevenue,
    0 AS f_DiffExtraFLTRCost,
    0 AS f_DiffExtraINSRPax,
    0 AS f_DiffExtraINSRQuantity,
    0 AS f_DiffExtraINSRAdults,
    0 AS f_DiffExtraINSRChildren,
    0 AS f_DiffExtraINSRInfants,
    0 AS f_DiffExtraINSRRevenue,
    0 AS f_DiffExtraINSRCost,
    0 AS f_DiffExtraOTHRPax,
    0 AS f_DiffExtraOTHRQuantity,
    0 AS f_DiffExtraOTHRAdults,
    0 AS f_DiffExtraOTHRChildren,
    0 AS f_DiffExtraOTHRInfants,
    0 AS f_DiffExtraOTHRRevenue,
    0 AS f_DiffExtraOTHRCost,
    0 AS f_DiffExtraSKIPPax,
    0 AS f_DiffExtraSKIPQuantity,
    0 AS f_DiffExtraSKIPAdults,
    0 AS f_DiffExtraSKIPChildren,
    0 AS f_DiffExtraSKIPInfants,
    0 AS f_DiffExtraSKIPRevenue,
    0 AS f_DiffExtraSKIPCost,
    0 AS f_DiffExtraSKITPax,
    0 AS f_DiffExtraSKITQuantity,
    0 AS f_DiffExtraSKITAdults,
    0 AS f_DiffExtraSKITChildren,
    0 AS f_DiffExtraSKITInfants,
    0 AS f_DiffExtraSKITRevenue,
    0 AS f_DiffExtraSKITCost,
    0 AS f_DiffExtraSPIOPax,
    0 AS f_DiffExtraSPIOQuantity,
    0 AS f_DiffExtraSPIOAdults,
    0 AS f_DiffExtraSPIOChildren,
    0 AS f_DiffExtraSPIOInfants,
    0 AS f_DiffExtraSPIORevenue,
    0 AS f_DiffExtraSPIOCost,
    0 AS f_DiffExtraSPORPax,
    0 AS f_DiffExtraSPORQuantity,
    0 AS f_DiffExtraSPORAdults,
    0 AS f_DiffExtraSPORChildren,
    0 AS f_DiffExtraSPORInfants,
    0 AS f_DiffExtraSPORRevenue,
    0 AS f_DiffExtraSPORCost,
    0 AS f_DiffExtraSTNDPax,
    0 AS f_DiffExtraSTNDQuantity,
    0 AS f_DiffExtraSTNDAdults,
    0 AS f_DiffExtraSTNDChildren,
    0 AS f_DiffExtraSTNDInfants,
    0 AS f_DiffExtraSTNDRevenue,
    0 AS f_DiffExtraSTNDCost,
    0 AS f_DiffExtraTRAFPax,
    0 AS f_DiffExtraTRAFQuantity,
    0 AS f_DiffExtraTRAFAdults,
    0 AS f_DiffExtraTRAFChildren,
    0 AS f_DiffExtraTRAFInfants,
    0 AS f_DiffExtraTRAFRevenue,
    0 AS f_DiffExtraTRAFCost,
    0 AS f_DiffExtraXXXXPax,
    0 AS f_DiffExtraXXXXQuantity,
    0 AS f_DiffExtraXXXXAdults,
    0 AS f_DiffExtraXXXXChildren,
    0 AS f_DiffExtraXXXXInfants,
    0 AS f_DiffExtraXXXXRevenue,
    0 AS f_DiffExtraXXXXCost
FROM
    AMI_Stage.dbo.ST_PIT_BOOKINGHEADERS ST_PIT_BOOKINGHEADERS
LEFT JOIN
    AMI_GALAXY.dbo.CONF_DT_BOOKING CONF_DT_BOOKING
    ON
        CONF_DT_BOOKING.i_SK_Bkg = ST_PIT_BOOKINGHEADERS.I_SK_BKG
LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_XXXX
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_XXXX.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_XXXX.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_XXXX.i_BK_ExtraType = 'XXXX'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_TRAF
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_TRAF.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_TRAF.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_TRAF.i_BK_ExtraType = 'TRAF'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_STND
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_STND.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_STND.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_STND.i_BK_ExtraType = 'STND'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_SPOR
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_SPOR.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_SPOR.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_SPOR.i_BK_ExtraType = 'SPOR'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_SPIO
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_SPIO.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_SPIO.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_SPIO.i_BK_ExtraType = 'SPIO'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_SKIT
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_SKIT.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_SKIT.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_SKIT.i_BK_ExtraType = 'SKIT'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_SKIP
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_SKIP.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_SKIP.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_SKIP.i_BK_ExtraType = 'SKIP'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_OTHR
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_OTHR.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_OTHR.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_OTHR.i_BK_ExtraType = 'OTHR'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_INSR
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_INSR.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_INSR.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_INSR.i_BK_ExtraType = 'INSR'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_FLTR
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_FLTR.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_FLTR.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_FLTR.i_BK_ExtraType = 'FLTR'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_EXCU
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_EXCU.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_EXCU.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_EXCU.i_BK_ExtraType = 'EXCU'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_EQUI
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_EQUI.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_EQUI.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_EQUI.i_BK_ExtraType = 'EQUI'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CREC
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CREC.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CREC.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_CREC.i_BK_ExtraType = 'CREC'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CLUB
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CLUB.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CLUB.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_CLUB.i_BK_ExtraType = 'CLUB'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CHLD
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CHLD.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CHLD.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_CHLD.i_BK_ExtraType = 'CHLD'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CARI
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CARI.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CARI.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_CARI.i_BK_ExtraType = 'CARI'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CARH
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CARH.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CARH.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_CARH.i_BK_ExtraType = 'CARH'

LEFT JOIN
    AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_ACTI
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_ACTI.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_ACTI.i_BK_Version
        AND
        ST_V_EC_EXTRACOSTS_ACTI.i_BK_ExtraType = 'ACTI'
LEFT JOIN
    AMI_Stage.dbo.ST_V_AC_ACCOMCOSTS ST_V_AC_ACCOMCOSTS
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_AC_ACCOMCOSTS.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_AC_ACCOMCOSTS.i_BK_Version
LEFT JOIN
    AMI_Stage.dbo.ST_V_FC_FLIGHTCOSTS ST_V_FC_FLIGHTCOSTS
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_FC_FLIGHTCOSTS.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_FC_FLIGHTCOSTS.i_BK_Version
LEFT JOIN
    AMI_Stage.dbo.ST_V_FC_FLIGHTCOSTS ST_V_FC_FLIGHTCOSTS_AIR
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_FC_FLIGHTCOSTS_AIR.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_FC_FLIGHTCOSTS_AIR.i_BK_Version
        AND
        ST_V_FC_FLIGHTCOSTS_AIR.i_BK_TransportType = 'AIR'
LEFT JOIN
    AMI_Stage.dbo.ST_V_FC_FLIGHTCOSTS ST_V_FC_FLIGHTCOSTS_FER
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_FC_FLIGHTCOSTS_FER.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_FC_FLIGHTCOSTS_FER.i_BK_Version
        AND
        ST_V_FC_FLIGHTCOSTS_FER.i_BK_TransportType = 'FER'
LEFT JOIN
    AMI_Stage.dbo.ST_V_FC_FLIGHTCOSTS ST_V_FC_FLIGHTCOSTS_TRN
    ON
        CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_FC_FLIGHTCOSTS_TRN.i_BK_Bkg
        AND
        ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_FC_FLIGHTCOSTS_TRN.i_BK_Version
        AND
        ST_V_FC_FLIGHTCOSTS_TRN.i_BK_TransportType = 'TRN'

Answer 1

首先，您可以尝试ISNULL，它的性能更好(但达不到您的需要)，但速度更快。

以下是旧版 StackOverlow 线程的引用:Which is quicker COALESCE OR ISNULL?

更新:

这意味着性能上的巨大差异:

"Marcus Culver: Across 730,000 rows 154 columns being ISNULL'd took 00:02:34 as opposed to coalesce at 01:32:43"