""randmonroe""> SELECT A9.CERT, A9.STATUS, A10.ADMINISTRATOR,
A9.PROCESS_DATE,

> A9.CANCEL_DATE,
> A10.FINANCE_COMPANY, A2.Dealer_id
> FROM ALLCANCEL a9 join ALLSALES a10 on (A9.CERT = A10.CERT)
> join DEALERS a2 on (a10.dealer_id = a2.dealer_id)
> and a9.status = 'Cancelled'
> and a10.administrator = 'SZO'
> and a9.process_date >= '5/17/02 00:00:00'
>
> ***the above code runs in 1.2 seconds
>
> but when I add...
>
> and a9.process_date <= '5/17/02 23:59:59'
>
> then the process runs
>
> 56.630 sec
>
> Thank you,

Randy,

Classic poor plan selectionon FB's behalf (a query should never run slower
when you refine the resultset on simple field operator value), it's taking
its time merging the index (1 or more) that it thinks it should include when
you add the last criteria. Either write the plan for it (not recomended by
me) or
a9.process_date+0 <= '5/17/02 23:59:59'
Notice the +0 means that FB thinks it can no longer use an index. Basically
build up your queries incrementally and when you find that performance (or
the plan) sucks, then look at how to remove the indexes from the plan that
appear to be hurting the performance.

Hope it helps.

JAC