LogFAQs > #967838716

that said, I would still wonder if he knew something I didn't.

recently I was given a query by a company (in past I'm used to dealing with a company that does not know how to program, whereas lately we've been dealing with a significantly more professional and competent company), and the query was extremely redundant. I thought I would be super-smart and refactor it down to remove the duplicated logic.

but this was SQL, and it's not always the same as "normal" programming. it was basically the same query duplicated 12+ times, each with a different set of arguments in the WHERE clauses. I hated the duplication, but then wondered about the WHERE clauses.

normally I would write something like this, for example:

WHERE (@somethingID IS NULL OR somethings.ID = @somethingID)
AND (@somethingElseID IS NULL OR somethingelses.ID = @somethingelseID)
-- etc

you can then pass NULL to get all of something, or an ID to filter to that particular category, department, whatever.

their query was like this (syntax might be slightly off):

IF @somethingID IS NULL AND @somethingElseID IS NULL THEN
BEGIN
SELECT ...
-- all
END

ELSE IF @somethingID IS NOT NULL AND @somethingElseID IS NULL THEN
BEGIN
SELECT ...
WHERE somethings.ID = @somethingID
END

ELSE IF ... --etc

that is horrendously redundant code (there were more like 4+ parameters to check and 12 or more blocks, and there were bugs in it specifically caused by the duplication (they hadn't updated all of the parameter names the right way)

but running some tests, this IS actually (unfortunately) faster than the much more complex WHERE clause. the WHERE clause is iterative, while the IF is not, so moving repeated logic from the WHERE to the IF makes it a lot more efficient. (I would have expected the DB engine (SQL Server) to take care of things like that and not recalculate @somethingID IS NULL more than once, but apparently it does not do that and does repeatedly recalculate it (or I guess at least still compares the key even though TRUE AND ... should short-circuit to true, removing the need to compare each row).)