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
from trgisl.RI RI
, trgisl.cl CL
where RI.src_stm_id = CL.cl_id
) A,
( select R.ri_id
, CL.CL_CODE as PROD_CD
,CL.dsc as PROD_DESC
from trgisl.cl CL
, trgisl.ri R
, trgisl.ri_x_cl RX
where R.ri_id = RX.ri_id
and RX.cl_id = CL.cl_id ------
and ri_x_cl_tp_id = 21167
) C ,
( select R.ri_id
, CL.CL_CODE as PROD_CLASS
,CL.dsc as PROD_CLASS_DESC
from trgisl.cl CL
, trgisl.ri R
, trgisl.ri_x_cl RX
where R.ri_id = RX.ri_id
and RX.cl_id = CL.cl_id
and ri_x_cl_tp_id = 21166
) d ,
( select R.ri_id
, CL.CL_CODE CURRENCY
,g.FIRST_RT as FX
from trgisl.cl CL
, trgisl.ri R
, trgisl.ri_x_cl RX
, trgisl.gl_recon_cur g
where R.ri_id = RX.ri_id
and RX.cl_id = CL.cl_id
and ri_x_cl_tp_id = 21161
and g.cURR_CD=cL.CL_code
and g.msr_dt = '2013-04-30'
) e,
( select R.ri_id
, AU.unq_id_src_stm GL_ACCOUNT_ID
from trgisl.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,
( select R.ri_id
, OU.BR_NO TRANSIT
from trgisl.ri R
, trgisl.RI_X_IP RX
LEFT OUTER JOIN trgisl.OU OU
on RX.ip_id = OU.ou_ip_id
where R.ri_id = RX.ri_id
and ri_x_ip_tp_id = 21145
) T,
(
select R.ri_id
, RV.val_amt NTNL_AMT
from trgisl.ri R
, trgisl.ri_val RV
where R.ri_id = RV.ri_id
and ri_val_tp_id= 21175
) N,
( select R.ri_id
, AB.pst_amt OUTSTANDING_BAL
from trgisl.ri R
, trgisl.ri_x_au RX
, trgisl.au_bal AB
where R.ri_id = RX.ri_id
and RX.au_id = AB.au_id
and ri_x_au_tp_id = 1267
and AB.pnt_bal_tp_id=31173
) O,
( select R.ri_id
, AB.pst_amt RECON_BAL
from trgisl.ri R
, trgisl.ri_x_au RX
, trgisl.au_bal AB
where R.ri_id = RX.ri_id
and RX.au_id = AB.au_id
and ri_x_au_tp_id = 1267
and AB.pnt_bal_tp_id=1264
) B
where A.ri_id = C.ri_id
and A.ri_id = B.ri_id
and A.ri_id = G.ri_id
and A.ri_id=d.ri_id
and A.ri_id=e.ri_id
and A.ri_id=T.ri_id
and A.ri_id=n.ri_id
and A.ri_id=O.ri_id
and e.CURRENCY is not null
and T.TRANSIT is not null
and G.GL_ACCOUNT_ID is not null
and e.CURRENCY <>''
and T.TRANSIT <>''
and G.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://qa.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://qa.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