Thanks Helen

Will look at that as an option, I hadn't thought about multiple
procedures. Interestingly moving to Firebird 2 makes this SP run like a
dream !

Very impressive. We

Rob Martin
Software Engineer

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

Wild Software Ltd



Helen Borrie wrote:

> 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
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>