3 ms·

an application (client) should be free to query a database (server) directly a stored procedure is an implicit dependency between client and server, fine as an

an application (client) should be free to query a database (server) directly a stored procedure is an implicit dependency between client and server, fine as an optimization, but definitely not what you want to do by default

grugagag 3y ago
For CRUD stuff if you don’t care about execution plan cache and other optimizations it could save you some time sure to query directly. For chunks of code that transactions/procesing large data directly on the server I’d reach out for stored procedures without thinking too much
- preseinger 3y ago
  it's not like stored procedures are inherently faster than normal queries, right? as long as you're not doing anything dumb like connection-per-request, query caching should work the same
  - JohnBooty 3y ago
    it's not like stored procedures are inherently faster than normal queries, right? They're doing the same amount of "work" with regards to finding/creating/updating/deleting rows. But you (potentially) avoid shuffling all of that data back and forth between your DB server and your app. This can be an orders-of-magnitude benefit if we are describing a multistep process that would involve lots of round trips, and/or involves a lot of rows that would have to be piped over the network from the DB server to the app. Suppose that when I create an order, I want to perform some other actions. I want to update inventory levels, calculate the user's new rewards balance, blah blah blah. I could do all of that in a single stored procedure without bouncing all of that data back and forth between DB and client in a multistep process. That could matter a lot in terms of scalability, because now maybe I only have to hold that transaction lock for 20ms instead of 200ms while I make all of those round trips. There are a lot of obvious downsides to using stored procedures, but they can be very effective as well. I would not use them as a default choice for most things but they can be a valuable optimization.
    - preseinger 3y ago
      huh? a single request returns a single result set to the client, whether it's a stored procedure or a direct query and any stored procedure can be equivalently expressed as a direct query, right?
      - grugagag 3y ago
        For example you can look up millions of rows then manipulate some data, aggregate some other data and in the end return a result set without shuffling back and forth client/server.
        preseinger 3y ago
        you can do that equally well in a stored procedure and a single query like, you can write a query which does all of these transforms in sequence, and returns the final result set the data that goes between client and server is only that final result set, it's not like the client receives each intermediate step's results and sends them back again?
        setr 3y ago
        If you’re going to mix multiple queries with procedural logic — eg running query A vs B depending on whatever conditions based on query C, then a stored proc saves you the round trips versus doing it in your app code. That’s all he’s saying.
        preseinger 3y ago
        it doesn't! whatever code you put into the stored proc you can equally well put into a query, and the round-trip costs would be equivalent a stored proc is just a query saved on the db server, nothing more if you destructure a stored proc to multiple individual queries, ok, sure, but who would do that?
        3y ago

grugagag 3y ago

For CRUD stuff if you don’t care about execution plan cache and other optimizations it could save you some time sure to query directly. For chunks of code that transactions/procesing large data directly on the server I’d reach out for stored procedures without thinking too much

preseinger 3y ago
it's not like stored procedures are inherently faster than normal queries, right? as long as you're not doing anything dumb like connection-per-request, query caching should work the same
- JohnBooty 3y ago
  it's not like stored procedures are inherently faster than normal queries, right? They're doing the same amount of "work" with regards to finding/creating/updating/deleting rows. But you (potentially) avoid shuffling all of that data back and forth between your DB server and your app. This can be an orders-of-magnitude benefit if we are describing a multistep process that would involve lots of round trips, and/or involves a lot of rows that would have to be piped over the network from the DB server to the app. Suppose that when I create an order, I want to perform some other actions. I want to update inventory levels, calculate the user's new rewards balance, blah blah blah. I could do all of that in a single stored procedure without bouncing all of that data back and forth between DB and client in a multistep process. That could matter a lot in terms of scalability, because now maybe I only have to hold that transaction lock for 20ms instead of 200ms while I make all of those round trips. There are a lot of obvious downsides to using stored procedures, but they can be very effective as well. I would not use them as a default choice for most things but they can be a valuable optimization.
  - preseinger 3y ago
    huh? a single request returns a single result set to the client, whether it's a stored procedure or a direct query and any stored procedure can be equivalently expressed as a direct query, right?
    - grugagag 3y ago
      For example you can look up millions of rows then manipulate some data, aggregate some other data and in the end return a result set without shuffling back and forth client/server.
      - preseinger 3y ago
        you can do that equally well in a stored procedure and a single query like, you can write a query which does all of these transforms in sequence, and returns the final result set the data that goes between client and server is only that final result set, it's not like the client receives each intermediate step's results and sends them back again?
        setr 3y ago
        If you’re going to mix multiple queries with procedural logic — eg running query A vs B depending on whatever conditions based on query C, then a stored proc saves you the round trips versus doing it in your app code. That’s all he’s saying.
        preseinger 3y ago
        it doesn't! whatever code you put into the stored proc you can equally well put into a query, and the round-trip costs would be equivalent a stored proc is just a query saved on the db server, nothing more if you destructure a stored proc to multiple individual queries, ok, sure, but who would do that?
        3y ago