Optimize TSQL

  • Hi

    Please optimze below Query

    select

    A.SRC_STM_CD

    , C.PROD_CD

    ,c.PROD_DESC

    ,d.PROD_CLASS

    ,d.PROD_CLASS_DESC

    ,e.CURRENCY

    ,e.FX

    ,count(*)

    , sum(value(B.RECON_BAL,0)) RECON_BAL

    ,sum(value(o.OUTSTANDING_BAL,0))

    ,sum(value(n.NTNL_AMT,0))

    from

    (select RI.ri_id

    , RI.src_stm_id

    , cl_code SRC_STM_CD

    fromtrgisl.RI RI

    , trgisl.cl CL

    whereRI.src_stm_id = CL.cl_id

    ) A,

    (select R.ri_id

    , CL.CL_CODE as PROD_CD

    ,CL.dsc as PROD_DESC

    fromtrgisl.cl CL

    , trgisl.ri R

    , trgisl.ri_x_cl RX

    whereR.ri_id = RX.ri_id

    andRX.cl_id = CL.cl_id ------

    andri_x_cl_tp_id = 21167

    ) C ,

    (select R.ri_id

    , CL.CL_CODE as PROD_CLASS

    ,CL.dsc as PROD_CLASS_DESC

    fromtrgisl.cl CL

    , trgisl.ri R

    , trgisl.ri_x_cl RX

    whereR.ri_id = RX.ri_id

    andRX.cl_id = CL.cl_id

    andri_x_cl_tp_id = 21166

    ) d ,

    (selectR.ri_id

    , CL.CL_CODE CURRENCY

    ,g.FIRST_RT as FX

    fromtrgisl.cl CL

    , trgisl.ri R

    , trgisl.ri_x_cl RX

    , trgisl.gl_recon_cur g

    whereR.ri_id = RX.ri_id

    andRX.cl_id = CL.cl_id

    andri_x_cl_tp_id = 21161

    andg.cURR_CD=cL.CL_code

    andg.msr_dt = '2013-04-30'

    ) e,

    (selectR.ri_id

    , AU.unq_id_src_stm GL_ACCOUNT_ID

    fromtrgisl.ri R

    , trgisl.ri_x_au RX

    LEFT OUTER JOIN trgisl.au AU

    on RX.au_id = AU.au_id

    where R.ri_id = RX.ri_id

    and ri_x_au_tp_id = 1268

    ) G,

    (selectR.ri_id

    , OU.BR_NO TRANSIT

    fromtrgisl.ri R

    , trgisl.RI_X_IP RX

    LEFT OUTER JOIN trgisl.OU OU

    onRX.ip_id = OU.ou_ip_id

    whereR.ri_id = RX.ri_id

    andri_x_ip_tp_id = 21145

    ) T,

    (

    selectR.ri_id

    , RV.val_amt NTNL_AMT

    fromtrgisl.ri R

    , trgisl.ri_val RV

    whereR.ri_id = RV.ri_id

    andri_val_tp_id= 21175

    ) N,

    (selectR.ri_id

    , AB.pst_amt OUTSTANDING_BAL

    fromtrgisl.ri R

    , trgisl.ri_x_au RX

    , trgisl.au_bal AB

    whereR.ri_id = RX.ri_id

    andRX.au_id = AB.au_id

    andri_x_au_tp_id = 1267

    andAB.pnt_bal_tp_id=31173

    ) O,

    (selectR.ri_id

    , AB.pst_amt RECON_BAL

    fromtrgisl.ri R

    , trgisl.ri_x_au RX

    , trgisl.au_bal AB

    whereR.ri_id = RX.ri_id

    andRX.au_id = AB.au_id

    andri_x_au_tp_id = 1267

    andAB.pnt_bal_tp_id=1264

    ) B

    whereA.ri_id = C.ri_id

    andA.ri_id = B.ri_id

    andA.ri_id = G.ri_id

    andA.ri_id=d.ri_id

    andA.ri_id=e.ri_id

    andA.ri_id=T.ri_id

    andA.ri_id=n.ri_id

    andA.ri_id=O.ri_id

    ande.CURRENCY is not null

    andT.TRANSIT is not null

    andG.GL_ACCOUNT_ID is not null

    ande.CURRENCY <>''

    andT.TRANSIT <>''

    andG.GL_ACCOUNT_ID <>''

    group by A.SRC_STM_CD

    , C.PROD_CD

    ,c.PROD_DESC

    ,d.PROD_CLASS

    ,d.PROD_CLASS_DESC

    ,e.CURRENCY

    ,e.FX

  • use temporary table.

    create index if necessary.

    Be cautious for using function [ sum(value(parameter)) ] with large number of data

  • Please post table definition, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (9/20/2013)


    Please post table definition, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    +1

    How can we optimize a query without knowing the tables, indexes, views, functions, etc in play?

    Data distribution will also likely be important.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply