Re: improving speed of query that uses a multi-column "filter" ?

On 9/30/2014 4:50 PM, Jonathan Vanasco wrote:
> 	WHERE (col_1 IS NULL ) AND (col_2 IS NULL) AND ((col_3 IS NULL) OR (col_3 = col_1))

if col_1 IS NULL,   then that OR condition doesn't make much sense.     
just saying...

these 4 columns are all nullable booleans, so they can be TRUE, FALSE, 
or NULL ?  with 4 columns, there's 3^4 = 81 possible combinations of 
these values...    you might get better speeds encoding this as a single 
SHORT INTEGER, and enumerating those 81 states, then just do equals or 
IN (set of values) conditions...   of course, this might make a lot of 
OTHER code more complicated.   It might be easier to make each col_X 2 
bits of this integer, such that one bit indicates the value was 'NULL', 
and the other bit is the true/false state if that first bit isn't set, 
this would make testing individual bits somewhat better.

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast