At 11:52 AM 18/04/2007, you wrote:

>Hi
>
>I am writing a SP that takes a number of parameters, two of these are
>aItemRef and aEntityRef. Each of these can contain a value or be NULL.
>
>if aitemRef is NULL the SP should return all items otherwise it should
>filter to just the selected item. aEntityRef works in a similar way. I
>have this working in the following SQL (last two lines)
>
> SELECT co.DelAddress, co.EntityRef, cs.CliStkRef,
>co.CEntityRef, cs.ItemRef, co.ClOrderRef,
> co.Supply, co.OrderNo, co.FREIGHTINC, co.Quote,
>cs.Quantity, co.CliOrderNo, co.ReqDate,
> co.OrderDate, co.IfLate, cs.ORegionRef,
>cs.UnitAmount, cs.TaxAmount, c.ClientName,
> itn.Name, co.TrustRef,
> (SELECT Sum(sl2.ShelfQty) FROM StockLevel sl2
>WHERE sl2.ItemRef = cs.ItemRef),
> (SELECT Sum(sl3.CustOrdQty) FROM StockLevel sl3
>WHERE sl3.ItemRef = cs.ItemRef),
> (SELECT Sum(sl4.SupOrdrQty) FROM StockLevel sl4
>WHERE sl4.ItemRef = cs.ItemRef)
> FROM ClientOrders co
> JOIN ClientStock cs ON co.ClOrderRef = cs.ClOrderRef
> JOIN Client c ON c.EntityRef = co.EntityRef
> JOIN ItemName itn ON itn.ItemRef = cs.ItemRef
> WHERE co.Completed <> 'T'
> AND cs.PSlipRef IS NULL
> AND cs.TransRef IS NULL
> AND (cs.ActionType IS NULL OR (cs.ActionType <> 'H' AND
>cs.ActionType <> 'C' AND cs.ActionType <> 'P'))
> AND (:aEntityRef IS NULL OR co.EntityRef = :aEntityRef)
> AND (:aItemRef IS NULL OR cs.ItemRef = :aItemRef)
>
>
>However the performance is poor, it takes a few seconds to return
>results. This is replacing an code based system that built the SQL
>manually. In that system it added the last lines only if they were
>required, as
>
> AND (co.EntityRef = :aEntityRef)
> AND (cs.ItemRef = :aItemRef)
>
>This was fast.
>
>I think adding the 'xxxx IS NULL OR' part to the SQL is stopping the use
>of Indexes. Is there a better way of doing this?
>
>p.s I cant do this as 3 SQLs in an IF END ELSE type construct because of
>the structure of the rest of the SP (I know thats vague, I really just
>don't want to because I would have to do it in 4 places so it would be
>12 times. Terrible to read, debug and maintain).

I have an instinctive aversion to using non-data-related value as an
operand in a search, unless the explicit intention is to prevent the
use of an index (as may be needed sometimes to avoid using a poor FK
index). Render unto Caesar that which is Caesar's: search data by
testing data.

Decide the logical specifics (which search is wanted??) in your
top-level procedure and make an EXECUTE PROCEDURE call to a
lower-level procedure to execute the appropriate SELECT
statement. Return the values for your outputs by passing back
RETURNING_VALUES() to your top-level RETURNS variables. At worst you
might have three possible lower-level procedure calls for your 12
possible sets of conditions, each of which is independently tweakable
if you have some suspect indexing that has different effects on
different searches.

One of the data-related search predicates is an index-blocker
too. Can you turn around those '<>' operators and test for presence
rather than absence? viz.

change
AND (cs.ActionType IS NULL OR (cs.ActionType <> 'H' AND
cs.ActionType <> 'C' AND cs.ActionType <> 'P'))
(which can't use an index on cs.ActionType)

to

AND (cs.ActionType IS NULL OR cs.ActionType in ('Q','R','S', 'T'))
(which can)

cheers,
Helen