sql query takes more time when run in a view

Question

HI all,

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 query is 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

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

Observation... the WHERE on the LEFT JOIN changes this to a JOIN

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

Answer 2

Tip: replace

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

with

ISNULL(freight, 0);

When You use the stored procedure the execution plan is compiled and stored so SQL Engine do not have to create it.

When You run this as query You probably have this plan already in cache, that why there is no diff in execution.

Probably when you are using the view the execution plan has to created from scratch.

Answer 3

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.

Try running this

Set Statistics Time On
Select * from view

and then Set Statistics On
Exec yourSPHere

You'll get something like this

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

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

(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 Execution Times: CPU time = 47 ms, elapsed time = 470 ms.

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.