We use selects like this in embedded our programs:

select * from table where (field = :field or :field = ' ')

:field depends on a filter condition and we don't know if it's going to have a value in it or not. If :field has a value in it, we want to only show the rows with field = :field. If :field is blanks (' ') we want to show all rows.

It seems that to put several conditions like this in a query results in bad peformance. Is there a better, performance-friendly way to write this?

We're on DB2 for iSeries

Thanks in advance

I would try this:

set fieldb to blanks

select col1, col2 from table where field in (:fielda, :fieldb)

I hope that you don't use "select *" in embeded SQL and that was only an example.

such a construct will most likely lead to a tablespace-scan.
Using the BIND parameter REOPT(VARS) might increase performance

consider using dynamic sql for that issue.

I think this won't give the desired result as it won't select all rows when host variable :fielda = ' ', it will only select rows having field = ' ' ! By the other hand I don't think field in (:fielda, :fieldb) is better than field = :fielda or field = fieldb

Don't worry we do not use "select *", it was only an example.

You'd better run EXPLAIN with the different options to have all the info. Then choose the best one.