Please optimize this query-----

Question

Please optimize this query-----

Venkataraman-408293

SSChasing Mays

Points: 650
More actions
November 29, 2007 at 1:13 pm

#63123

Hi experts
The below query took more time to complete the process
SELECT
SCC ->table Fields Value,
SRD ->table Fields Value
SCE ->table Fields Value,
SRH ->table Fields Value,
SVC ->table Fields Value
FROM
SCS_CONTRACTS SCC
INNER JOIN SRD -- Table 1
ON SCC.icontract_id = SRD.icontract_id
INNER JOIN SCE -- Table 2
ON SCC.icontract_id = SCE.icontract_id
inner JOIN
(
select scc.icontract_id, coalesce(sum(svc.camount),0) AS camount
from SCC
LEFT JOIN SVC
ON SCC.icontract_id = SVC.icontract_id
where scc.dtupdate_last >= '2007-09-01 00:00:00.000' and scc.dtupdate_last <='2007-09-28 23:59:59.997'
and scc.sstatus in ('A','C','E')
Group by SCC.icontract_id
) SVC
ON SCC.icontract_id = SVC.icontract_id
---------------------*********
-- The below mentioned sub query is problem
---------------------*********
-- We have to compare the SRH table with contract_id and from the SRH table we have to take the field values which is lastly updated.. For example for single contract id say 2001 we may have 4 or 5 records in SRH table. We have to take thw value which is lastly updated
---------------------*********
INNER JOIN
(
select SRH.icontract_id,
SRH.cadj_prem_rate_1 , SRH.cadj_prem_rate_2 , SRH.cadj_prem_rate_3 , SRH.cadj_prem_rate_4 ,
SRH.cadj_admin_rate_1 , SRH.cadj_admin_rate_2 , SRH.cadj_admin_rate_3 , SRH.cadj_admin_rate_4 ,
SRH.cadj_admin_rate_5 , SRH.cadj_admin_rate_6 , SRH.cadj_admin_rate_7 , SRH.cadj_admin_rate_8 ,
SRH.cadj_admin_rate_9 , SRH.cadj_admin_rate_10
from scc
left join
(
select srh.* from SRH,
(select srh.icontract_id,
max(srh.dtupdate_last)as dtupdate_last
from Srh
where srh.dtupdate_last >= '2007-09-01 00:00:00.000' and srh.dtupdate_last <='2007-09-28 23:59:59.997'
group by srh.icontract_id ) sk1
where
(SRH.icontract_id = sk1.icontract_id and SRH.dtupdate_last = sk1.dtupdate_last)
)
SRH
on Scc.icontract_id = SRH.icontract_id
where (scc.dtupdate_last >= '2007-09-01 00:00:00.000' and scc.dtupdate_last <='2007-09-28 23:59:59.997'
and scc.sstatus IN ('A','C','E'))
)SRH
ON SRD.icontract_id = SRH.icontract_id
where scc.dtupdate_last >= '2007-09-01 00:00:00.000' and scc.dtupdate_last <='2007-09-28 23:59:59.997'
and scc.sstatus IN ('A','C','E')
and sce.iearn_set_id = 1
Please optimize the query.....
thanks in advance
s.v
:):):)

Viewing 2 posts - 1 through 1 (of 1 total)

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

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

We have to compare the SRH table with contract_id and from the SRH table we have to take the field values which is lastly updated.. For example for single contract id say 2001 we may have 4 or 5 records in SRH table. We have to take thw value which is lastly updated

Does it work?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)