4 ms·

No, that's a perfectly practical approach. You're much less likely to make mistakes on procedure names vs. full queries. The trade off is managing procedure ver

No, that's a perfectly practical approach. You're much less likely to make mistakes on procedure names vs. full queries. The trade off is managing procedure versioning and deployments of procedures. Ultimately it's a style preference but I've seen larger projects with thousands of lines of SQL in source with some of those queries spanning 6-7 joins with dozens of where clauses (plus string manipulation functions, ugh), so that is why I'm pretty fiercely against SQL in strings.

jandrese 9y ago
Sometimes I see monster queries like that and think that they could have saved themselves several lines of code if they were willing to export the data and run some of the logic in the native language. Sometimes it seems like people get hung up on doing everything in one query and end up making a Frankenstein's monster that nobody will want to touch when it inevitably breaks on the next database upgrade. Although sometimes the system forces this behavior by ingesting the output of the query automatically.
- Amezarak 9y ago
  Well, it depends. At least in my experience, assuming those queries are written "correctly" (that is, in a set-manner than a C-in-SQL manner), leaving it in the database means is usually vastly more performant and easier to verify it's correct. I've written a lot of many-hundred-line monsters with joins, CTEs, table-valued functions, and a whole lot of other stuff piled into them that were both more clear and much faster than the arcane application logic they once consisted of. Usually if it's used in the application, some management type will also want some kind of report based on pretty much the same query, too. And having it as SQL also makes it much quicker to compare the before-and-after if any changes are made. Of course, YMMV and what I said isn't true all the time, and would certainly also depend on your database server - if queries did break between upgrades I'd be much more hesitant to rely on the db server. But either way, I wouldn't dump a monster like that right in source code - that's crazy!
  - cagmz 9y ago
    What do you mean by "set-manner than a C-in-SQL manner"?
    - jessaustin 9y ago
      (Not OP, but I feel like I've seen this phenomenon before.) In C, the natural idiom would be to read in a giant file line by line, doing something with each line and eventually doing something else based on some summary of everything read so far. Lots of dumb SQL code also does something like this, e.g. by generating some ginormous join and then whittling it down to something usable. Better SQL code filters before the join, and often uses indexes to do so.
    - Amezarak 9y ago
      There are people in this world who do things like use cursors in place of joins, for example.
- takeda 9y ago
  The latency of the network is comparatively very high. This is why the N+1 is such a big deal and you can get results magnitude faster of you query through the database.

jandrese 9y ago

Sometimes I see monster queries like that and think that they could have saved themselves several lines of code if they were willing to export the data and run some of the logic in the native language. Sometimes it seems like people get hung up on doing everything in one query and end up making a Frankenstein's monster that nobody will want to touch when it inevitably breaks on the next database upgrade. Although sometimes the system forces this behavior by ingesting the output of the query automatically.

Amezarak 9y ago
Well, it depends. At least in my experience, assuming those queries are written "correctly" (that is, in a set-manner than a C-in-SQL manner), leaving it in the database means is usually vastly more performant and easier to verify it's correct. I've written a lot of many-hundred-line monsters with joins, CTEs, table-valued functions, and a whole lot of other stuff piled into them that were both more clear and much faster than the arcane application logic they once consisted of. Usually if it's used in the application, some management type will also want some kind of report based on pretty much the same query, too. And having it as SQL also makes it much quicker to compare the before-and-after if any changes are made. Of course, YMMV and what I said isn't true all the time, and would certainly also depend on your database server - if queries did break between upgrades I'd be much more hesitant to rely on the db server. But either way, I wouldn't dump a monster like that right in source code - that's crazy!
- cagmz 9y ago
  What do you mean by "set-manner than a C-in-SQL manner"?
  - jessaustin 9y ago
    (Not OP, but I feel like I've seen this phenomenon before.) In C, the natural idiom would be to read in a giant file line by line, doing something with each line and eventually doing something else based on some summary of everything read so far. Lots of dumb SQL code also does something like this, e.g. by generating some ginormous join and then whittling it down to something usable. Better SQL code filters before the join, and often uses indexes to do so.
  - Amezarak 9y ago
    There are people in this world who do things like use cursors in place of joins, for example.
takeda 9y ago
The latency of the network is comparatively very high. This is why the N+1 is such a big deal and you can get results magnitude faster of you query through the database.