Sql query takes longer to run in view

Question

Hi all,

I have a huge sql query. When I put this request into a saved Proc, it takes 5 seconds to execute, and I run it just like a request, it takes 4-5 seconds, but when I run it in a view, it takes 5 minutes.

Please advise why its launch is slow in view

sql query below:

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 

Answer 1

I agree with Vash that the extra time running as a vaiew may be related to the extra time to plan.

Try to run

 Set Statistics Time On Select * from view and then Set Statistics On Exec yourSPHere 

You will get something like this

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

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

(5475 rows (rows) affected)) ContactBase table. The number of scans is 1, the logical reads 428, the physical reads 0, read-ahead reads 0, the logical reads lob 0, the physical reads lob 0, the lob read-ahead reads 0.

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

If SQL Server Parsing and Compiling Time takes into account the difference between the two times, your answer is that the View should create an execution plan each time, while Sproc uses the caching execution plan.

Answer 2

Based on your comment, I suspect that you clicked the " predicate pushing " (search for this phrase)

Observation ... WHERE FOR LEFT INTERACTION changes this to JOIN

 LEFT JOIN RefCountry As DestRefCountry... .... where (DestRefCountry.RN_Desc = 'United States') 

Answer 3

Tip: replace

 CASE WHEN freight IS NULL THEN 0 ELSE freight END AS FREIGHT, 

from

 ISNULL(freight, 0); 

When you use a stored procedure, the execution plan is compiled and stored, so SQL Engine does not need to be created.

When you run this as a request, you probably already have this plan in the cache, so there is no difference in execution.

It is likely that when you use the view, the execution plan should be created from scratch.