September 20, 2013 at 5:39 am
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
September 20, 2013 at 5:45 am
use temporary table.
create index if necessary.
Be cautious for using function [ sum(value(parameter)) ] with large number of data
September 20, 2013 at 5:47 am
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
September 20, 2013 at 9:41 am
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