Yes I know you can't optimise OR, so I'm looking for an alternative
approach.

I've come across a query in a stored procedure which goes like this:

FOR SELECT <stuff> FROM <stuff>
WHERE <stuff> ...
AND ( (WIDGET= :param ) OR ( :param is null ) )
ORDER BY <stuff>
INTO <stuff>
DO
BEGIN
<stuff>
END

There are two ways in which this procedure is called: either a number is
passed in param, in which case the loop should process the records just
for that numbered widget, around three records, or null is passed in
param, in which case the loop should process the records for all the
widgets, around 300,000 records.

In the former case it would be nice if the SELECT used the index on
WIDGET and thus ran in a handful of milliseconds rather than the tens of
seconds it takes for the "param is null" case.

But when the procedure is compiled the query optimiser doesn't know
whether param is going to be null or not, so can't do anything clever,
so follows its normal rule of simply giving up when it sees an OR, and
thus doesn't use the index on WIDGET (have I got that right?).

So my question is how to get the non-null param case to go fast?

One option is to duplicate the procedure, in one copy don't have the
second branch of the OR, change the calls to call the appropriate one,
and hope that anyone who changes one copy in future remembers to change
the other one. This approach does not sound overly attractive.

Alternatively I guess I can use EXECUTE STATEMENT, and simply leave out
the WHERE on WIDGET in the case it isn't wanted, but that'll result in a
compile of the SELECT every time the procedure is run. Which doesn't
matter in the slow (null) case but might matter in the fast (single
WIDGET) case (I haven't tried this yet).

So, I'll give EXECUTE STATEMENT a go, to see if the cost of compilation
is acceptable, but does anyone have any cleverer ideas?

--
Tim Ward