Someone I've been working with wrote what I at first thought was a very redundant query. I was considering removing the redundancies but then it occurred to me that it may actually be less efficient if I did that.
Normally, for optional filter arguments I would write something like this:
SELECT stuff
FROM data
WHERE (@arg1 IS NULL OR Field1 = @arg1)
AND (@arg2 IS NULL OR Field2 = @arg2)
...
The way he wrote his query was more like this:
IF @arg1 IS NOT NULL AND @arg2 IS NOT NULL
BEGIN
SELECT stuff
FROM data
WHERE Field1 = @arg1
AND Field2 = @arg2
END
IF @arg1 IS NULL AND @arg2 IS NOT NULL
BEGIN
SELECT stuff
FROM data
WHERE Field2 = @arg2
END
IF @arg1 IS NOT NULL AND @arg2 IS NULL
BEGIN
SELECT stuff
FROM data
WHERE Field1 = @arg1
END
On the one hand, the redundancy seems terrible (especially when the real thing had 12 blocks, 5 arguments, 8 fields, and 2 tables with a join). But on the other hand... this does pull a lot of comparisons out of the iteration. Only exactly the comparisons that need to be made are made in the filter, and repetitive unnecessary comparison against NULL is removed to a single place outside of the actual query. (Basically 'constant' or 'fixed' information is pulled out of the query 'loop'.)
So... is the second version here actually "better" from a performance point of view? I know, premature optimization and all that, and readability is more important, and DRY, etc., but still I wonder, is it more efficient performance-wise? (What I would hope is that these are basically equivalent, and that the DB engine should already be doing something like this already.)
