Hi all

I would like some opinions please with regards the use of NULL values
in WITH statement and how they impact performance and the handling of
indexes.

Basically, I have a lot of fairly complex stored procedures that
accept various input parameters and use those input parameters in
their select statements to determine the result set returned.

A classic example of this would be a Stock Valuation report that
accepts a Warehouse Code as an input parameter and then performs a
select as follows:

select a,b,c,d etc.
from StockTable

WHERE ((Warehousecode = :InputWarehouseCode) OR (:InputWarehouseCode
IS NULL))


In effect this stored procedure now caters for 2 scenarios, on the
one hand it is able to return a result set for a specific Warehouse
only, on the other hand it can return a result set for All warehouses
if the input param is NULL.

The reason this is so handy is because it's one block of code to
maintain. This is example is simple, but some of my actual SP's are
huge and return up to 40 or 50 fields so the less maintanence the
better.

In short, the above implementation of the NULL check is horrible and
results in the SQL interpreter discarding the index on the StockTable
completely AND essentially forces a sequential read of every record
in the table regardless of how many actually fall into the final
result set. In other words the above method is only any good if you
WANT everything, it's useless if you want the WHERE clause to narrow
the result down for you use the input param as a control value.

So far, the only solution to this problem that I have found is to
check if the Input Param is NULL at the top of the SP and wrap 2
copies of the same select statement in an IF-THEN-ELSE block which
differ only by thier WITH statments.

eg.

if (:InputParam IS NULL) then
begin
FOR
select a,b
from StockTable
into
oa,
ob
DO
suspend;

end else
begin
FOR
select a,b
from StockTable
WHERE (WarehouseCode = :InputParam)
into
oa,
ob
DO
suspend;

end


That works like a charm, but is only practical if you have ONE input
parameter that is allowing for ALL or a specific value as above. If
you have more than one param allowing this, the number of nested IF-
THEN-ELSE statements would be a joke.

Just to reiterate how important this is, I had one of my most complex
SP's reading it's core table 5.7 Million times instead of 43000 times
because of this. That equated to a difference in execution times of
that particular query of roughly 4 minutes and 28 seconds. The
orginal SP (with the bad NULL evaluation) was taking 4.5 minutes, the
new one with 2 selects inside the outer IF-THEN-ELSE now takes 2
seconds and that's all I changed !

Anyone got any ideas / suggestions / thoughts on this matter ?

Allan Strydom
Senior Developer
CK Services
South Africa