Optimizing a query

mrkeivan

Hi, please help me optimize this query

SELECT DISTINCT CC_STRINGENTEREXIT(ems.passdatetime,
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
) as DetailEnterExit,
CC_DURATIONENTEREXIT(
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
, ems.passdatetime) as DurationInZone
FROM EMSINFO ems
where
trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS')) AND
CC_GETNEXTENTER(ems.EMSINFOID,
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
, ems.PASSDATETIME) <> 0 and
CC_CheckForExit(ems.PASSDATETIME,
(
select ems2.PASSDATETIME from EMSINFO ems2
where ems2.EMSINFOID = CC_GETNEXTENTER(ems.EMSINFOID,
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
, ems.PASSDATETIME)
),
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
) <> 0
and ems.masterplatenumber = '150444833' and rownum <100 ;

you see that 1-

Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid

has been repeated multiple time, how do I declare it once and use it as many times as I want. 2-

CC_GETNEXTENTER()

has also been repeated, and in the functions I have a similar Issue, how can I get the value of the function once and use it as many times as I want through the query? Thanx in advance

Jorgen Andersson

1. You use a join. Something like this: Untested!

SELECT DISTINCT
CC_STRINGENTEREXIT(ems.passdatetime,emsqi.PLATENUMBER) as DetailEnterExit,
CC_DURATIONENTEREXIT(emsqi.PLATENUMBER, ems.passdatetime) as DurationInZone
FROM EMSINFO ems JOIN EMSQUIRKINFO emsqi
ON emsqi.emsinfoid = ems.emsinfoid
WHERE trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS'))
AND CC_GETNEXTENTER(ems.EMSINFOID, emsqi.PLATENUMBER, ems.PASSDATETIME) <> 0
AND CC_CheckForExit(ems.PASSDATETIME, (
SELECT ems2.PASSDATETIME from EMSINFO ems2
WHERE ems2.EMSINFOID = CC_GETNEXTENTER(ems.EMSINFOID,emsqi.PLATENUMBER, ems.PASSDATETIME)
),emsqi.PLATENUMBER) <> 0
AND ems.masterplatenumber = '150444833' and rownum <100 ;

2. Use a CTE, Something like this: Still untested!

WITH bla AS (
SELECT CC_STRINGENTEREXIT(ems.passdatetime,emsqi.PLATENUMBER) as DetailEnterExit,
CC_DURATIONENTEREXIT(emsqi.PLATENUMBER, ems.passdatetime) as DurationInZone
CC_GETNEXTENTER(ems.EMSINFOID,emsqi.PLATENUMBER, ems.PASSDATETIME) GetNextEnter
FROM EMSINFO ems JOIN EMSQUIRKINFO emsqi
ON emsqi.emsinfoid = ems.emsinfoid
WHERE trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS'))
AND ems.masterplatenumber = '150444833'
)
SELECT DISTINCT
DetailEnterExit,
DurationInZone
WHERE GetNextEnter <> 0
AND CC_CheckForExit(
ems.PASSDATETIME,
(
SELECT ems2.PASSDATETIME from EMSINFO ems2
WHERE ems2.EMSINFOID = GetNextEnter
)
,emsqi.PLATENUMBER) <> 0
AND rownum <100
;

Now, keep in mind that I don't have a clue what your functions do, so my suggestions is only to show you the methodology, It's up to you to see if it gives the right result. But you want as much of the filtering in the CTE as possible to keep the amount of data down.

Wrong is evil and must be defeated. - Jeff Ello[^]