June 21, 2002 at 6:40 am
Hello all,
Here is a select statement that I am trying to optimize. It is part of a stored procedure that is run as part of a set of 'process' stored procedures. This particular one is from a section that creates warnings for the user. Any ideas on how this can be optimized? I can't restructure the db so all the joins have to stay. Many of the warnings contain all the same date checks so I am also trying to find a way to not have to repeat code.
Thanks!
Eddie
insert into ra.warning(
cpspriorid,
error_i)
select tcpsp.cpspriorid,
1835
from ra.tcpsptab as tcpsp
where tcpsp.status_cln = 'RT'
and tcpsp.userid = @userid
and(not exists (select 1
from ra.transm as tm,
ra.cpspont as cps,
ra.psmoney as ps,
ra.linkq as lr,
ra.rates as rs,
ra.rates as rs1,
ra.rates as rates,
ra.instaff as sc,
ra.foundat as fs
where tcpsp.transmasterid_cln = tm.transmasterid_cln
and tm.staffmasterid_cln = sc.staffmasterid_cln
and tm.foundationid_cln = lr.foundationid_cln
and tm.foundationid_cln = fs.foundationid_cln
and lr.ratescheduleid_cln = rs.uniqueid_cln
and tcpsp.cpsplanid_cln = cps.uniqueid_cln
and cps.psplanmasterid_cln = ps.uniqueid_cln
and ps.ratescheduleid_cln = rs.parentid_cln
and rates.ratescheduleid_cln = rs.uniqueid_cln
and rs.parentid_cln = rs1.uniqueid_cln
and (tm.acdate >= cps.sdate or cps.sdate is null)
and (tm.acdate <= cps.edate or cps.edate is null)
and (tm.acdate >= ps.sdate or ps.sdate is null)
and (tm.acdate <= ps.edate or ps.edate is null)
and (tm.acdate >= rs.sdate or rs.sdate is null)
and (tm.acdate <= rs.edate or rs.edate is null)
and (tm.acdate >= rs1.sdate or rs1.sdate is null)
and (tm.acdate <= rs1.edate or rs1.edate is null)
and (tm.acdate >= sc.sdate or sc.sdate is null)
and (tm.acdate <= sc.edate or sc.edate is null)
and (tm.acdate >= rates.sdate or rates.sdate is null)
and (tm.acdate <= rates.edate or rates.edate is null)
and ( (tm.calcdurc_n >= rates.sessionmin_n and tm.calcdurc_n <= rates.sessionmax_n and
(rs.minunits_cln) = 'MINUTES') or
((tm.calcdurc_n * rs.unitshour_n) >= rates.sessionmin_n and
(tm.calcdurc_n * rs.unitshour_n) <= rates.sessionmax_n and
(rs.minunits_cln) = 'UNITS' and (fs.minunits_cln) = 'MINUTES') or
(tm.clientduration_n >= rates.sessionmin_n and tm.clientduration_n <= rates.sessionmax_n and
(rs.minunits_cln) = 'UNITS' and (fs.minunits_cln) = 'UNITS')
)
and (((sc.credential_cln = rates.degree_cln and sc.credtype_cln = 'D') or (rates.degree_cln is null))
or ((sc.credential_cln = rates.license_cln and sc.credtype_cln = 'L') or (rates.license_cln is null))
or ((sc.credential_cln = rates.certification_cln and sc.credtype_cln = 'C') or (rates.certification_cln is null)))))
June 22, 2002 at 6:45 am
i would suggest you to make joins by using join and on keyword instead of where coz AFAIK when you use where first a cartesian product is made and then a filter is applied based on the conditions where as when you use Joins...on only those records which match the joins are procured then the where conditions are applied thereby making it more efficient.
hope i make myself clear there.
Correct me if am wrong.
Another thing, you can create appropriate indexes on your tables to speed up the process.
HTH
Edited by - Nazim on 06/22/2002 06:46:31 AM
June 22, 2002 at 12:35 pm
Using the INNER JOIN keyword to make your join like so will help the query engine make faster join analisys as oppossed to the old WHERE clause way as well.
...
(select 1
from
ra.transm as tm
INNER JOIN
ra.linkq as lr
INNER JOIN
ra.rates as rs
INNER JOIN
ra.rates as rs1
ON
rs.parentid_cln = rs1.uniqueid_cln
INNER JOIN
ra.rates as rates
ON
rates.ratescheduleid_cln = rs.uniqueid_cln
INNER JOIN
ra.psmoney as ps
INNER JOIN
ra.cpspont as cps
ON
cps.psplanmasterid_cln = ps.uniqueid_cln
ON
ps.ratescheduleid_cln = rs.parentid_cln
ON
lr.ratescheduleid_cln = rs.uniqueid_cln
ON
tm.foundationid_cln = lr.foundationid_cln
INNER JOIN
ra.instaff as sc
ON
tm.staffmasterid_cln = sc.staffmasterid_cln
INNER JOIN
ra.foundat as fs
ON
tm.foundationid_cln = fs.foundationid_cln
where tcpsp.transmasterid_cln = tm.transmasterid_cln
and tcpsp.cpsplanid_cln = cps.uniqueid_cln
and ...
Also look at your query execution plan and look for table scans as these are places where indexes can help with speed.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply