News for hackers. Stuff that matters.
Re:ORMs don’t kill databases. Developers do. by clarkevans on Tuesday August 30, 2011 @09:05AM (#2941233)
What seems to be suspicious SQL (the CASE/WHEN/ELSE) is probably not the problem. This is classic boilerplate SQL "cast" code to make a boolean value for databases that don't support booleans. Distracting, perhaps, but not a smoking gun. Likely the problem with something like this is that there's a full-table scan on the Fruits table, and that the function doing the scanning is called frequently without caching.
Re:ORMs don’t kill databases. Developers do. by dmethvin on Tuesday August 30, 2011 @10:33AM (#2941588)
> What seems to be suspicious SQL (the CASE/WHEN/ELSE) is probably not the problem.

It certainly could be the problem. Like you say, a CASE becomes a computed field and requires a table scan. A simple check for IsAlsoAFruit can be done with an index scan, assuming it has that index. This can be a huge difference in data scanning and memory load. Of course, there are also plenty of times when SQL will spurn a boolean index because it's not selective enough.

Re:ORMs don’t kill databases. Developers do. by jeffdavis on Tuesday August 30, 2011 @10:59AM (#2941718)
"Likely the problem with something like this is that there's a full-table scan on the Fruits table"

What do you think the DBMS should do for a table with two tuples?

Regardless, it should be easier for the DBMS to use alternate plans if simple expressions aren't hidden behind a bunch of useless CASE statements.