Sql-server – TSQL insert table using view — performance issue

join;performancesql server

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;

Best 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.

Related Solutions

MySQL – Get Column from Multiple Tables

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

MySQL optimization – year column grouping – using temporary table, filesort

I don't see a lot of opportunity for improvement.

The index you added was probably a big help, because it's being used for the range matching on the WHERE clause (type => range, key => tran_date), and it's being used as a covering index (extra => using index), avoiding the need to seek into the table to fetch the row data.

But since you're using functions to construct the financial_year value for the group by, both the "using filesort" and "using temporary" can't be avoided. But, those aren't the real problem. The real problem is that you're evaluating MONTH(tran_date) 346,485 times and YEAR(tran_date) at least that many times... ~700,000 function calls in one second doesn't seem too bad.

Plan B: I am definitely not a fan of storing redundant data, and I'm dead-set against making the application responsible for maintaining it... but one option I might be tempted to try would be to create a dashboard_stats_by_financial_year table, and use after-insert/update/delete triggers on the transactions1 table to manage keeping those stats current.

That option has a cost, of course -- adding to the amount of time it takes to update/insert/delete a transaction... but, waiting > 1200 milliseconds for stats for your dashboard is a cost, too. So it may come down to whether you want to pay for it now or pay for it later.

Best Answer

Related Solutions

MySQL – Get Column from Multiple Tables

MySQL optimization – year column grouping – using temporary table, filesort

Related Question