Hi Adam

Thanks for your suggestions. Please note my second email where I note
that the Character T / F seems to massively impact the performance.

Here is the real SQL

SELECT *
FROM SupplyLine sl
JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef
WHERE so.EntityRef = 4
AND sl.SupTranRef IS NULL
AND sl.PackSlpRef IS NULL
AND sl.CancelFlag <> 'T'


PLAN JOIN (SO INDEX (SUPPLYORDER_ENTITYREF),SL INDEX
(SUPPLYLINE_SUPTRANREF,SUPPLYLINE_PACKSLPREF,SUPPLYLINE_SUPORDRREF))

Returns 208 results in 1.359s

If I change the last line to

AND sl.CancelFlag <> 'F'

The plan remains the same and the result is 334 records in 0.078s

Seems like something else besides an optimization problem is going on
here. Also note that removing

AND sl.SupTranRef IS NULL
AND sl.PackSlpRef IS NULL

Changes the plan but not the performance in either case. This is weird !


Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd



Adam wrote:

>Rob,
>
>This is probably a weakness with the optimiser in FB1.5. I believe it
>is addressed in FB2, but you will probably find the following works
>better.
>
>SELECT a,b,c
>FROM Table1 t1
>JOIN Table2 t2 ON t2.pk = t1.FK and t1.PK = 4
>where t2.CancelFlag <> 'T'
>
>If not, you could force the join order using for example a left join,
>or you could dictate the plan you want it to use using the plan
>directive. In this case, left join will be equivalent to join because
>you are joining to a primary key (which can not be null).
>
>SELECT a,b,c
>FROM Table1 t1
>left JOIN Table2 t2 ON t2.pk = t1.FK
>where t1.PK = 4
>and t2.CancelFlag <> 'T'
>
>In fact there are a number of other options. You could create a
>compound index on (t2.cancelflag, t2.pk) which would have very good
>selectivity and would help in this case
>
>It's a tricky one. I don't like moving what is essentially a where
>condition into a join because I believe it is important for
>maintainability to separate the join conditions from the where clause.
>Where we have done this sort of thing, we have noted it down so that
>when the optimiser issue is addressed we can adjust the necessary
>queries.
>
>Are you using any sort of plan analysis tool because if so the reason
>for the slowness should be clear (even if the optimisers logic for that
>choice of path may not be so clear).
>
>Adam
>
>
>
>