在视图中运行时，sql 查询需要更多时间 [英] sql query takes more time when run in a view

问题描述

大家好，

我有一个巨大的 sql 查询.当我将该查询放入存储的 Proc 时，它需要 5 秒才能执行，我将它作为查询运行它需要 4-5 秒，但是当我在视图中运行它时需要 5 分钟.

I have a huge sql query. When i put that query in a stored Proc it takes 5 seconds to execute which i run it just as a query it takes 4-5 seconds but when i run it in a view it takes 5 mins.

请告知为什么它在视图中运行缓慢

Please advise why its running that slow in a view

sql 查询如下:

CREATE VIEW dbo.Client_Billing_RS      
AS        
SELECT DISTINCT TOP (100) PERCENT       
CLIENT.OH_Code AS CLIENT,       
BUYER.OH_Code AS BUYER,       
dbo.Client_ReturnWK(pallet.MB_PR_CLOSED_DT) AS WEEKNUM,       
dbo.Client_PadString(DATEPART(MONTH, CONVERT(varchar(8), pallet.MB_PR_CLOSED_DT, 112)), '0', 2) AS MONTH,      
DATEPART(YEAR, CONVERT(varchar(8), pallet.MB_PR_CLOSED_DT, 112)) AS YEAR,      
shipment.JS_ActualVolume,      
shipment.JS_ActualWeight,       
packing.MB_PD_Units ,       
orderHeader.JD_OH_Buyer,       
OrgMiscServ_1.OM_CustomAttrib3,         
shipment.JS_TransportMode AS MODE,       
shipment.JS_UniqueConsignRef ,       
consol.JK_UniqueConsignRef,         
DestRefCountry.RN_Desc,       
part.OP_PartNum,       
part.OP_Desc AS TITLE,       
CONVERT(VARCHAR(8), part.OP_CustomAttrib1, 1) AS COVER_DATE,       
docketLine.WE_CustomDate1 AS ON_SALE_DATE,      
docketLine.WE_CustomAttrib3 AS US_BARCODE,       
part.OP_CustomAttrib3 AS UK_BARCODE,       
CASE WHEN freight IS NULL THEN 0 ELSE freight END AS FREIGHT,       
CASE WHEN war IS NULL THEN 0 ELSE war END AS WAR,       
CASE WHEN fuel IS NULL THEN 0 ELSE FUEL END AS FUEL,       
shipment.JS_ActualChargeable * 1000 AS CHRG_KG,       
shipment.JS_PackingMode,       
'' AS MIN_CHRG,      
BUYER.OH_FullName AS BUYER_NAME,         
CASE WHEN Client_MF_Billing_Job_Cost_PIVOT.FUEL + Client_MF_Billing_Job_Cost_PIVOT.WAR IS NULL         
                      THEN 0 ELSE Client_MF_Billing_Job_Cost_PIVOT.FUEL + Client_MF_Billing_Job_Cost_PIVOT.WAR END AS SUR_AMT1,         
(packing.MB_PD_Units  * part.OP_Weight) * (CASE WHEN rate IS NULL THEN 0 ELSE RATE END + (CASE WHEN JS_TransportMode = 'AIR' THEN CASE WHEN FUEL_LOOKUP IS NULL         
                      THEN 0 ELSE FUEL_LOOKUP END ELSE 0 END)) AS TITLE_AMT,       
consol.JK_CustomDate1 AS LOAD_DATE_OLD,       
docket.WD_DocketID,       
orderHeader.JD_IsCancelled,       
CASE WHEN RATE IS NULL THEN 0 ELSE rate END AS RATE,      
packing.MB_PD_Units  * part.OP_Weight * CASE WHEN Client_Tariff_Job_Rate.RATE IS NULL THEN 0 ELSE rate END AS FRT_AMT,       
part.OP_PK,       
CASE WHEN LEN(part.OP_CustomAttrib2)= 1 THEN '0' + OP_CustomAttrib2 ELSE OP_CustomAttrib2 END AS ISSUE_NUMBERx,      
'9' + LEFT(dbo.Client_PadString(part.OP_CustomAttrib2, '0', 2), 2) AS ISSUE_NUMBER,       
Client_Consol.ETD AS LOAD_DATE,         
CASE WHEN docketLine.WE_CustomAttrib3 IS NULL THEN '' ELSE SUBSTRING(docketLine.WE_CustomAttrib3, 7, 5) END AS UPC_CODE,       
BUYER.OH_PK AS BUYER_PK,       
CLIENT.OH_PK AS CLIENT_PK,       
CASE WHEN LEN(SUBSTRING(part.OP_PartNum,1, CASE WHEN CHARINDEX('-', part.OP_PartNum) > 0 THEN CHARINDEX('-', part.OP_PartNum) - 1 ELSE 0 END))         
     = 0 THEN OU_LocalPartNumber ELSE SUBSTRING(part.OP_PartNum, 1, CASE WHEN CHARINDEX('-',         
                      part.OP_PartNum) > 0 THEN CHARINDEX('-', part.OP_PartNum) - 1 ELSE 0 END) END AS MAG_CODE,      

CASE WHEN JS_TransportMode = 'AIR' THEN (FUEL_LOOKUP) * (ROUND(packing.MB_PD_Units  * part.OP_Weight + .5, 0)) ELSE 0 END AS SUR_AMT,       
CASE WHEN JS_TransportMode = 'AIR' THEN FUEL_LOOKUP ELSE 0 END AS FUEL1,         
Client_Tariff_Job_Rate.WAR_LOOKUP AS SUGGESTED_WAR,       
Client_Tariff_Job_Rate.FUEL_LOOKUP AS SUGGESTED_FUEL,         
Client_Tariff_Job_Rate.SHIPPING_LINE,       
OrgMiscServ.OM_CustomAttrib1,       
OrgMiscServ.OM_CustomDate1,       
CLIENT.OH_PK AS ClientPK,      
part.OP_Weight,       
 packing.MB_PD_Units * part.OP_Weight AS FRT_WEIGHT,      

packing.MB_PD_Units * part.OP_Weight + Client_CHRG_PALLET.PALLET_KG_PORTION AS FRT_WEIGHT_GROSS,         
(packing.MB_PD_Units  * part.OP_Weight + Client_CHRG_PALLET.PALLET_KG_PORTION)         
                      * CASE WHEN Client_Tariff_Job_Rate.RATE IS NULL THEN 0 ELSE rate END AS FRT_AMT_GROSS,      

(packing.MB_PD_Units  * part.OP_Weight + Client_CHRG_PALLET.PALLET_KG_PORTION) * (CASE WHEN rate IS NULL         
                      THEN 0 ELSE RATE END + (CASE WHEN JS_TransportMode = 'AIR' THEN CASE WHEN FUEL_LOOKUP IS NULL         
                      THEN 0 ELSE FUEL_LOOKUP END ELSE 0 END)) AS TITLE_AMT_GROSS,       
orderHeader.JD_PK,       
orderHeader.JD_OrderNumber,         
Client_Tariff_Job_Rate.ONWARD_DELIVERY,         
packing.MB_PD_Units  * part.OP_Weight * CASE WHEN Client_Tariff_Job_Rate.ONWARD_DELIVERY IS NULL         
                      THEN 0 ELSE ONWARD_DELIVERY END AS ONWARD_DELIVERY_AMT,       
docketLine.WE_CustomDecimal4 AS COVER_PRICE,      
CLIENT.OH_FullName,       
(packing.MB_PD_Units  * (part.OP_Weight + 0.009) + Client_CHRG_PALLET.PALLET_KG_PORTION)         
                      * CASE WHEN Client_Tariff_Job_Rate.RATE IS NULL THEN 0 ELSE rate END AS FRT_AMT_GROSS_UPLIFT,         
(packing.MB_PD_Units  * (part.OP_Weight + 0.009) + Client_CHRG_PALLET.PALLET_KG_PORTION)      
                      * (CASE WHEN rate IS NULL THEN 0 ELSE RATE END + (CASE WHEN JS_TransportMode = 'AIR' THEN CASE WHEN FUEL_LOOKUP IS NULL         
                      THEN 0 ELSE FUEL_LOOKUP END ELSE 0 END)) AS TITLE_AMT_GROSS_UPLIFT,         
packing.MB_PD_Units  * (part.OP_Weight + 0.009) AS FRT_WEIGHT_UPLIFT,         
packing.MB_PD_Units  * (part.OP_Weight + 0.009) + Client_CHRG_PALLET.PALLET_KG_PORTION AS FRT_WEIGHT_GROSS_UPLIFT,       
part.OP_Weight + 0.009 AS COPY_KG_UPLIFT,      
(packing.MB_PD_Units  * (part.OP_Weight + 0.009)) * (CASE WHEN rate IS NULL         
                      THEN 0 ELSE RATE END + (CASE WHEN JS_TransportMode = 'AIR' THEN CASE WHEN FUEL_LOOKUP IS NULL         
                      THEN 0 ELSE FUEL_LOOKUP END ELSE 0 END)) AS TITLE_AMT_UPLIFT,       
packing.MB_PD_Units  * (part.OP_Weight + 0.009) * CASE WHEN Client_Tariff_Job_Rate.RATE IS NULL THEN 0 ELSE rate END AS FRT_AMT_UPLIFT,         
packing.MB_PD_Units  * (part.OP_Weight + 0.009) * CASE WHEN Client_Tariff_Job_Rate.ONWARD_DELIVERY IS NULL THEN 0 ELSE ONWARD_DELIVERY END AS ONWARD_DELIVERY_AMT_UPLIFT,    
packing.MB_PR_PalletRef    

FROM Client_whspalletpacking packing WITH (NOLOCK)     
inner join whsDocketLine docketLine WITH (NOLOCK) on  packing.we_fk  =docketline.we_pk      
inner join whsDocket  docket WITH (NOLOCK) on docketline.we_wd =docket.wd_pk      
inner join client_whspallet pallet WITH (NOLOCK) on packing.MB_PD_PR = pallet.MB_PR_PK      
inner join jobshipment shipment WITH (NOLOCK) on packing.mb_js= shipment.js_pk      
inner join jobOrderHeader orderHeader WITH (NOLOCK) on docket.WD_ExternalReference =OrderHeader.JD_OrderNumber      
inner join orgheader Client WITH (NOLOCK) on docket.wd_oh_client= Client.oh_pk      
inner join orgheader Buyer WITH (NOLOCK) on packing.MB_PR_OH=Buyer.oh_pk      
inner join jobconsol consol WITH (NOLOCK) on  packing.mb_jk=consol.jk_pk      
INNER JOIN OrgSupplierPart part WITH (NOLOCK) ON docketline.WE_OP = part.OP_PK      
inner join OrgPartRelation WITH (NOLOCK) on part.op_pk=OrgPartRelation.OU_OP      
LEFT JOIN RefUNLOCO As DestUNLOCO WITH (NOLOCK) On DestUNLOCO.RL_Code = shipment.JS_RL_NKDestination      
LEFT JOIN RefCountry As DestRefCountry WITH (NOLOCK)  On DestRefCountry.RN_PK = DestUNLOCO.RL_RN      
LEFT OUTER JOIN Client_Tariff_Job_Rate WITH (NOLOCK)  ON orderHeader.JD_PK = Client_Tariff_Job_Rate.JOB_ORDER_PK       
LEFT OUTER JOIN Client_MF_Billing_Job_Cost_PIVOT WITH (NOLOCK) ON packing.MB_JH = Client_MF_Billing_Job_Cost_PIVOT.JR_JH      

inner join Client_CHRG_PALLET WITH (NOLOCK) on packing.WE_FK = Client_CHRG_PALLET.WE_FK  and packing.mb_pr_palletref=Client_CHRG_PALLET.mb_pr_palletref ---change    
left outer join OrgMiscServ WITH (NOLOCK) on CLIENT.OH_PK =OrgMiscServ.OM_OH       
inner join OrgMiscServ AS OrgMiscServ_1 WITH (NOLOCK) on BUYER.OH_PK =OrgMiscServ_1.OM_OH      
left outer join Client_Consol  WITH (NOLOCK) on consol.jk_pk=Client_Consol.CONSOL_PK      
where (DestRefCountry.RN_Desc = 'United States')        
ORDER BY CLIENT, TITLE, BUYER, MONTH DESC, WEEKNUM DESC, MODE DESC  

推荐答案

我同意 Vash 的观点，作为 vaiew 运行的额外时间可能是由于编译执行计划的额外时间.

I agree with Vash in that the additional time when run as a vaiew may be due to the extra time to compile an execution plan.

试试运行这个

Set Statistics Time On
Select * from view

and then Set Statistics On
Exec yourSPHere

你会得到这样的东西

SQL Server 解析和编译时间:
CPU 时间 = 0 毫秒，经过时间 = 1 毫秒.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server 执行时间:CPU时间 = 0 毫秒，经过时间 = 1 毫秒.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.

(5475 行受影响)表'联系基础'.扫描计数 1，逻辑读取 428，物理读取 0，预读读取 0，lob 逻辑读取0, lob 物理读取 0, lob预读为 0.

(5475 row(s) affected) Table 'ContactBase'. Scan count 1, logical reads 428, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server 执行时间:CPU时间 = 47 毫秒，经过时间 = 470 毫秒.

SQL Server Execution Times: CPU time = 47 ms, elapsed time = 470 ms.

如果SQL Server 解析和编译时间:"解释了两次之间的差异，您的答案是每次 Sproc 使用缓存执行计划时，视图都必须创建执行计划.

If the "SQL Server parse and compile time:" accounts for the difference between the two times that your answer is that the View is having to Create a execution plan each time while the Sproc is using a cached execution plan.

这篇关于在视图中运行时，sql 查询需要更多时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！