3 ms·
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 datab
by clarkevans 15y ago
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.
- dmethvin 15y ago> 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.
- jeffdavis 15y ago"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.