On 11/10/2013 12:51, Kjell Rilbe wrote:

 

Den 2013-10-11 13:40 skrev Tim Ward såhär:
>
> 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
>

I did a little test, and I think this might work:

FOR SELECT <stuff> FROM <stuff>
WHERE <stuff> ...
AND ( (WIDGET starting with coalesce(:param, '') )
ORDER BY <stuff>
INTO <stuff>
DO
BEGIN
<stuff>
END

But I guess it works only if WIDGET is a char/varchar. Or might work on
other types as well if you add a computed index casting it to varchar...?

It's an integer

OK, so add a computed index with expression like this:
cast(WIDGET as vacrhar(10)) || '.'

and use this SQL:

FOR SELECT <stuff> FROM <stuff>
WHERE <stuff> ...
AND ( (cast(WIDGET as vacrhar(10)) || '.' starting with coalesce(:param, '') || '.' )
ORDER BY <stuff>
INTO <stuff>
DO
BEGIN
<stuff>
END

Untested, but I think it should work.

Kjell

-- 
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64