Question :
I have a big view joining multiple tables that in a regular basis materialists itself into a table (this step occurs for viz’s tool read a table instead of a view during data source refresh):
select *
into TableName
from ViewName
This process takes around 5 hrs to complete.
Any Ideas how to approach to this insert statement to get better performance?
View code looks like this :
create view [GAPDatabase].[rmbStock] as
select
t.APPR_DT
, t.CR_DBT_MEMO_AMT
, GAPDatabase.fnGetUSD(t.CURNCY_CD,t.CR_DBT_MEMO_AMT) as CR_DBT_MEMO_AMT_USD
, t.CURNCY_CD
, t.CUST_REF
, t.DOUBLE_TM_BILL_RATE
, GAPDatabase.fnGetUSD(t.CURNCY_CD, t.DOUBLE_TM_BILL_RATE) as DOUBLE_TM_BILL_RATE_USD
, t.DOUBLE_TM_HR
, t.END_DT
, t.INV_REF
, t.INV_TYPE
, t.LAST_MOD_DT
, t.ORD_REF
, t.OT_BILL_RATE
, GAPDatabase.fnGetUSD(t.CURNCY_CD, t.OT_BILL_RATE ) as OT_BILL_RATE_USD
, t.OT_HR
, t.REG_BILL_RATE
, GAPDatabase.fnGetUSD(t.CURNCY_CD,t.REG_BILL_RATE ) as Reg_Bill_Rate_USD
, t.REG_HR
, t.OLD_CODE
, t.START_DT
, t.SUBMIT_DT
, t.TMSHEET_REF
, t.TOT_BILLABLE_HR
, GAPDatabase.fnGetUSD(t.CURNCY_CD, t.TOT_TMSHEET_AMT) as Tot_TMSheetAmt_USD
, t.TOT_TMSHEET_AMT
, t.waStock_REF
, tss.STAT_UKHP
, p2.CUST_NAME
, p2.CUST_NAME_UKHP
, o.VEND_MGR
, o.REQ_OR_SOW
, mj.LABOR_CATG_UKHP as [Labor Type]
, r.LABOR_CATG
, r.REQ_RSN_UK
, o.BUS_UNIT
, o.CANDREFATE_REF
, p2.PGM_NAME_UKHP as Client
, ms.SUPL_NAME_UKHP as Supplier
, mj.JOB_TTLE_UKHP as Title
, mj.JOB_TTLE
, p2.VERT_UKHP as Vertical
, p2.INDUSTRY_UKHP as Industry
, mo.WRK_LOC_CITY_UKHP as City
, mo.WRK_LOC_ST_PRVC_CD_UKHP as [State]
, moc.WRK_LOC_CTRY_UKHP as Country
, moc.WRK_LOC_REG_UKHP as Region
, ms.SUPL_RECOGNITION_UKHP
, ms.SUPL_DIV_IND_UKHP
, (case
when (isnull(o.[REQ_TYPE_UK],'')+isnull(o.[ASGN_TYPE_UK],'')+isnull(o.[WRKR_TYPE_UK],'')+isnull(o.[AGNCY_CONTR_TYPE],'')
+isnull(o.[NON_EMPL_CLS_UK],'')+isnull(o.[PREREFNT_REQ],'')+isnull(o.[PYRL_SERV],'')+ isnull(SUPL_NAME,'')) like '%payroll%' then 'Yes'
when (isnull(o.[REQ_TYPE_UK],'')+isnull(o.[ASGN_TYPE_UK],'')+isnull(o.[WRKR_TYPE_UK],'')+isnull(o.[AGNCY_CONTR_TYPE],'')
+isnull(o.[NON_EMPL_CLS_UK],'')+isnull(o.[PREREFNT_REQ],'')+isnull(o.[PYRL_SERV],'')+ isnull(SUPL_NAME,'')) like '%PPO%' then 'Yes'
else 'No' end) as Payroll
, (case when isnull(o.[REQ_TYPE_UK],'')+isnull(o.[REQ_RCTR_TYPE_UK],'')+isnull(o.[REQ_SRC_TYPE_UK],'')
+isnull(o.[SRC_TYPE_UK],'')+isnull(o.[ASGN_TYPE_UK],'')+isnull(o.[WRKR_TYPE_UK],'')
+isnull(o.[AGNCY_CONTR_TYPE],'')+isnull(o.[PREREFNT_REQ],'')+isnull(o.[PYRL_SERV],'') like '%PRE%REF%' then 'Yes' else 'No' end) as PreREF
, p2.SOL_TYPE_UKHP
, left(mj.SOC_JOB_CODE,7) as SOC
, o.SOL_TYPE_UKH
, o.SOURCE_TYPE_UKH
, wb.BUS_UNIT_UKH
,ms.SUPL_NAME_UKHP_REF
from
GAPDatabase.waStock t
inner join
GAPDatabase.mpStockStatus tss on tss.REF_STAT_UKHP = t.REF_STAT_UKHP
left join
GAPDatabase.Progress p on t.cust_REF = p.cust_REF
inner join
GAPDatabase.Progress2 p2 on p2.REF_PGM_UKHP = p.REF_PGM_UKHP
left join
GAPDatabase.Ordinary o on o.OLD_CODE = t.OLD_CODE
and o.CUST_REF = t.CUST_REF
and o.ORD_REF = t.ORD_REF
left join
GAPDatabase.OrdinaryLocation mo on mo.REF_WRK_LOC_UKHP = o.REF_WRK_LOC_UKHP
left join
GAPDatabase.OrdLocationCountry moc on moc.REF_WRK_LOC_CTRY_UKHP = REF_WRK_LOC_CTRY_UKHP
left join
GAPDatabase.waBusUnit wb on wb.OLD_CODE = o.OLD_CODE
and wb.cust_REF = o.cust_REF
and wb.FORAIGN_CODE = o.FORAIGN_CODE
left join
GAPDatabase.Refounds r on r.OLD_CODE = o.OLD_CODE
and r.CUST_REF = o.CUST_REF
and r.REQ_REF = o.REQ_REF
left join
GAPDatabase.mpRefmajos mj on mj.REF_JOB_TTLE_UKHP = r.REF_JOB_TTLE_UKHP
left join
GAPDatabase.WebSocial ws on ws.OLD_CODE = o.OLD_CODE
and ws.CUST_REF = o.CUST_REF
and ws.SUPL_REF = o.SUPL_REF
left join
GAPDatabase.mWebSocial ms on ws.REF_SUPP_NAME_UKHP = ms.REF_SUPP_NAME_UKHP
where
t.APPR_DT between '1/1/2015' and getdate()
AND NOT (t.OLD_CODE = 'PF' and p2.PGM_NAME_UKHP = '99389384')
AND NOT (p2.PGM_NAME_UKHP = '0009000'
and ((t.OLD_CODE = '2543245' and moc.WRK_LOC_REG_UKHP = 'LOCAL')
or (t.OLD_CODE = '5435' and moc.WRK_LOC_REG_UKHP <> 'LOCAL')
))
and moc.WRK_LOC_REG_UKHP is not null
and p2.PGM_NAME_UKHP is not null;
Answer :
The first thing I would check is the fnGetUSD -function. It looks like currency conversion, and you’re now calling the function 5 times for every single row. That can be a huge performance issue. At least look into changing it to a inline table valued function (the multi-statement function will not help) or adding the calculation directly to this view.
For the rest of the query, looking into “statistics io” output and actual plan is a good idea, but neither of them will show anything related to the function. To see the whole picture what happens, you’ll have to look for example into sys.dm_exec_query_stats.
From statistics io you can see what table causes the most I/O, and that’s a good place to start. If it’s a worktable, then there’s some operations that cause it to be created.
From actual plan I would check scans, key lookups (when number if rows is big) where the number of columns in output list is small and spools and other strange looking operations.
For indexing, I would check if APPR_DT is indexed (using it helps) and also indexing for the columns used to join the tables (and that data types match) and for the key lookups that if the output columns could be added as include fields to the indexes.
I would suggest to filter earlier. Your where clause has some values which can be filtered in a subquery, which will preserve a join of columns which weren’t used and thrown away at the end. This will normally speed things up.
Another point could be the table order and the type of table joins. But to get throw this I would need the execution plan.
Another thing may be your functions inside your SELECT. What do they do inside them? Maybe just multiplying? If so, you can move the formula inside the SELECT itself.
Hopefully this is helping you.
join on P kills the left join on P2
moc.WRK_LOC_REG_UKHP is not null kills 3 left joins
from
GAPDatabase.waStock t
inner join
GAPDatabase.mpStockStatus tss on tss.REF_STAT_UKHP = t.REF_STAT_UKHP
and t.APPR_DT between '1/1/2015' and getdate()
inner join
GAPDatabase.Progress p on t.cust_REF = p.cust_REF
inner join
GAPDatabase.Progress2 p2 on p2.REF_PGM_UKHP = p.REF_PGM_UKHP
AND NOT (t.OLD_CODE = 'PF' and p2.PGM_NAME_UKHP = '99389384')
AND NOT (p2.PGM_NAME_UKHP = '0009000'
and ((t.OLD_CODE = '2543245' and moc.WRK_LOC_REG_UKHP = 'LOCAL')
or (t.OLD_CODE = '5435' and moc.WRK_LOC_REG_UKHP <> 'LOCAL')
))
inner join
GAPDatabase.Ordinary o on o.OLD_CODE = t.OLD_CODE
and o.CUST_REF = t.CUST_REF
and o.ORD_REF = t.ORD_REF
inner join
GAPDatabase.OrdinaryLocation mo on mo.REF_WRK_LOC_UKHP = o.REF_WRK_LOC_UKHP
inner join
GAPDatabase.OrdLocationCountry moc on moc.REF_WRK_LOC_CTRY_UKHP = REF_WRK_LOC_CTRY_UKHP
and moc.WRK_LOC_REG_UKHP is not null
If you dont mind about dirty read then you might either use NOLOCK hint or READUNCOMMITTED TRANSACTION ISOLATION. This could improve your query’s performance in case this query is continuously blocked by other session.
Otherwise your could look for what is the Max Degree of Parallelism is set for your server. If it is set to 1 then you could increase it.
Look for the Wait Types, if you know about wait types.
Check the Number of Data and Log file assigned for Temp DB.
Check the free Memory available.
Look for if anyone is blocking or your session
If all of the above is right, then look for values of the Performance Counters.
If that query runs for five hours, then i suspect there could be an improper join between tables which could cause duplicated records. Check your Conditions you used to make a join.