TSQL insert table using view -- performance issue

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;

See the execution plan to find out expensive parts of your query technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx — sqluser, Commented Jun 19, 2015 at 4:11
It is better to update your previous question stackoverflow.com/questions/30928542/… — sqluser, Commented Jun 19, 2015 at 4:13
at first sight: you use LEFT JOIN GAPDatabase.OrdLocationCountry mocand in your where clause and moc.WRK_LOC_REG_UKHP is not null --> so this will act like an inner join! So change to inner join, this performs better than left join. — CeOnSql, Commented Jun 19, 2015 at 6:39
One that join on P2 kill the left join on P. Pull the conditions in the where up into the join — paparazzo, Commented Jun 19, 2015 at 10:03

James Z · Accepted Answer · 2015-06-19 05:04:54Z

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.

If your function is strictly a calculation function you might get better performance by changing the function to add schemabinding. — Kenneth Fisher, Commented Jun 22, 2015 at 14:23

Ionic · Accepted Answer · 2015-06-19 04:57:32Z

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.

paparazzo · Accepted Answer · 2015-06-19 10:10:52Z

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

Mark Sinkinson · Accepted Answer · 2015-06-22 14:10:55Z

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.

Ananda Kumar Jayaraman · Accepted Answer · 2015-06-19 11:42:37Z

-2

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.

answered Jun 19, 2015 at 11:42

Ananda Kumar Jayaraman

1011 bronze badge

Add a comment |

Stack Exchange Network

TSQL insert table using view -- performance issue

5 Answers 5

Your Answer

Hot Network Questions

TSQL insert table using view -- performance issue

5 Answers 5

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions