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
