Readit NewsThe comment you’re responding to mentioned pulling code into a function. As an example, if there’s a clever algorithm or technique that optimizes a particular calculation, it’s fine to write code more for the machine to be fast than the human to read as long as it’s tidy in a function that a dev using a debugger can just step over or out of.
The received wisdom is, of course, to lean on the DB as much as possible, put all the business logic in SQL because of course the DB is much more efficient. I myself have always been a big proponent of it.
But, as you rightly point out, you're using up one of your infrastructure's most scarce and hard-to-scale resources - the DB's CPU.
That said, I don’t think I’ve ever had occasion to write a query quite like that. I’ve written
select * from blah where id in (1,2,3…) and condition
select * from blah where condition1 and condition2
Given that most queries don't look like that, I think my criticism is reasonable. For most use cases, this query will have performance downsides, even if it doesn't for some very narrow use-cases.
SELECT \* FROM users
WHERE id = $1
AND ($2 IS NULL OR username = $2)
AND ($3 IS NULL OR age > $3)
AND ($4 IS NULL OR age < $4)