4 ms·

I think there are two different concerns here though: The article recommends something that may lead to using the wrong query plans. In the "right" conditions,

by dagss 2y ago

I think there are two different concerns here though: The article recommends something that may lead to using the wrong query plans. In the "right" conditions, you will do full table scans of all your data for every query. This is making the DB waste a lot of CPU (and IO). Wasting resources like that is different from just where to do work that has to be done anyway! I am a proponent of shifting logic toward the DB, because likely it ends up there anyway and usually you reduce the resource consumption also for the DB to have as much logic as possible in the DB. The extreme example is you want to sum(numbers) -- it is so much faster to sum it in one roundtrip to the DB, than to do a thousand roundtrips to the DB to fetch the numbers to sum them on the client. The latter is so much more effort also for the DB server's resources. My point is: Usually it is impossible to meaningfully shift CPU work to the client of the DB, because the client needs the data, so it will ask for it, and looking up the data is the most costly operation in the DB.

cogman10 2y ago
The answer is "it depends". Sum is a good thing to do in the Db because it's low cost to the db and reduces io between the db and app. Sort can be (depending on indexes) a bad thing for a db because that's CPU time that needs to be burned. Conditional logic is also (often) terrible for the db because it can break the optimizer in weird ways and is just as easily performed outside the db. The right action to take is whatever optimizes db resources in the long run. That can sometimes mean shifting to the db, and sometimes it means shifting out of the db.
- crazygringo 2y ago
  It's hard to think of situations where you don't want to do the sorting on the DB. If you're sorting small numbers of rows it's cheap enough that it doesn't matter, and if you're sorting large numbers of rows you should be using an index which makes it vastly more efficient than it could be in your app. And if your conditional logic is breaking the optimizer then the solution is usually to write the query more correctly. I can't think of a single instance where I've ever found moving conditional logic out of a query to be meaningfully more performant. But maybe there's a specific example you have in mind?
  - cogman10 2y ago
    > if you're sorting large numbers of rows you should be using an index Perhaps, depends on what the table is doing and needs to be optimized for. Indexes are not free, they have a write penalty as they need to be updated every time the data in the index is updated. > I can't think of a single instance where I've ever found moving conditional logic out of a query to be meaningfully more performant. But maybe there's a specific example you have in mind? Certainly. In one of our applications we effectively represent the types as subtables with a root table for the parent type. There were roughly 10 different types with different columns per type. One way the queries were written, which is slow, was that on insertion the client app would send in (effectively) a block of data with all columns for these types to insert. In the database, the conditional logic would pull out the type id from the input and make the decision on that type information for which subtable would be inserted. There's really no way to make this something the SQL optimizer can well consume. The right solution was to instead break this up in the application and per type do the insertions directly into the table type in question. It simplified both sides of the code and ran faster.
    - dagss 2y ago
      I agree that for a) inserts and b) single entity access -- in both these cases the backend can do a lot of the preparation. And your example is both a) and b). We are then in O(1) optimization territory. If you are only processing a single entity -- the backend should tell the DB exactly what to do. And one shouldn't have if-statements in SQL of course that is "doing it wrong". But if you have a chunk of 10000 entities like that in your example, all of different types, then you will have to insert some subset of data into all those tables (1000 in one tables, 500 another table, and so on). That logic is well suited for where conditions without much overhead. But yes for inserts most of the logic can usually be shifted to the DB client as that is where the data resides already. The problem I was talking about was meaningfully shifting for to the client for queries, where the client has no data to work with and must fetch it from the DB. Let us take your example and turn it into "fetch 10000 such objects". Fetching the right rows for all of them at once using joins and where conditions (+temporary tables and multiple return sets in the same query roundtrip) is going to be more efficient for the DB than the backend first fetching the type, then branching on type, then fetching from another table and so on.

by dagss 2y ago

cogman10 2y ago

The answer is "it depends". Sum is a good thing to do in the Db because it's low cost to the db and reduces io between the db and app. Sort can be (depending on indexes) a bad thing for a db because that's CPU time that needs to be burned. Conditional logic is also (often) terrible for the db because it can break the optimizer in weird ways and is just as easily performed outside the db. The right action to take is whatever optimizes db resources in the long run. That can sometimes mean shifting to the db, and sometimes it means shifting out of the db.

crazygringo 2y ago
It's hard to think of situations where you don't want to do the sorting on the DB. If you're sorting small numbers of rows it's cheap enough that it doesn't matter, and if you're sorting large numbers of rows you should be using an index which makes it vastly more efficient than it could be in your app. And if your conditional logic is breaking the optimizer then the solution is usually to write the query more correctly. I can't think of a single instance where I've ever found moving conditional logic out of a query to be meaningfully more performant. But maybe there's a specific example you have in mind?
- cogman10 2y ago
  > if you're sorting large numbers of rows you should be using an index Perhaps, depends on what the table is doing and needs to be optimized for. Indexes are not free, they have a write penalty as they need to be updated every time the data in the index is updated. > I can't think of a single instance where I've ever found moving conditional logic out of a query to be meaningfully more performant. But maybe there's a specific example you have in mind? Certainly. In one of our applications we effectively represent the types as subtables with a root table for the parent type. There were roughly 10 different types with different columns per type. One way the queries were written, which is slow, was that on insertion the client app would send in (effectively) a block of data with all columns for these types to insert. In the database, the conditional logic would pull out the type id from the input and make the decision on that type information for which subtable would be inserted. There's really no way to make this something the SQL optimizer can well consume. The right solution was to instead break this up in the application and per type do the insertions directly into the table type in question. It simplified both sides of the code and ran faster.
  - dagss 2y ago
    I agree that for a) inserts and b) single entity access -- in both these cases the backend can do a lot of the preparation. And your example is both a) and b). We are then in O(1) optimization territory. If you are only processing a single entity -- the backend should tell the DB exactly what to do. And one shouldn't have if-statements in SQL of course that is "doing it wrong". But if you have a chunk of 10000 entities like that in your example, all of different types, then you will have to insert some subset of data into all those tables (1000 in one tables, 500 another table, and so on). That logic is well suited for where conditions without much overhead. But yes for inserts most of the logic can usually be shifted to the DB client as that is where the data resides already. The problem I was talking about was meaningfully shifting for to the client for queries, where the client has no data to work with and must fetch it from the DB. Let us take your example and turn it into "fetch 10000 such objects". Fetching the right rows for all of them at once using joins and where conditions (+temporary tables and multiple return sets in the same query roundtrip) is going to be more efficient for the DB than the backend first fetching the type, then branching on type, then fetching from another table and so on.