Re: Query tuning help

On May 8, 2005, at 8:32 PM, Russell Smith wrote:
>> I have run this, and while it is very fast, I'm concerned it's not
>> doing what I need.
> How fast is very fast?
>

It took 35 seconds to complete versus ~450 my old way.

>
> select distinct em.incidentid, ea.recordtext as retdata, eg.long,
> eg.lat
> FROM em JOIN ea ON (em.incidentid = ea.incidentid AND em.entrydate >=
> '2005-1-1 00:00'
> AND em.entrydate <= '2005-5-9 00:00' AND (ea.recordtext like '%RED%'
> OR ea.recordtext like '%CORVETTE%'))
> JOIN eg ON em.incidentid = eg.incidentid WHERE
> em.incidentid IN
> (select distinct em.incidentid, ea.recordtext as retdata, eg.long,
> eg.lat
> FROM em JOIN ea ON (em.incidentid = ea.incidentid AND em.entrydate >=
> '2005-1-1 00:00'
> AND em.entrydate <= '2005-5-9 00:00' AND ea.recordtext like
> '%CORVETTE%'))
> JOIN eg ON em.incidentid = eg.incidentid)  AND
> em.incidentid IN
> (select distinct em.incidentid, ea.recordtext as retdata, eg.long,
> eg.lat
> FROM em JOIN ea ON (em.incidentid = ea.incidentid AND em.entrydate >=
> '2005-1-1 00:00'
> AND em.entrydate <= '2005-5-9 00:00' AND ea.recordtext like '%RED%'))
> JOIN eg ON em.incidentid = eg.incidentid)
>

Yes, it is nasty, but so was my previous query :)  So long as this is
faster, I'm ok with that.  I'll see if i can make this work.  Thank you
very much.

-Dan