3 ms·

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 equivalentl

• 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?
        • bandrami 3y ago
          If you're just writing a single access app for a DB I agree it doesn't really matter much. Where SPs really help is when you're maintaining a DB that multiple projects from multiple teams access and you can present them a single high-level API to use, so that Accounting and Fulfillment aren't reinventing the same wheels over and over. So it's more about stack management than anything else.
        • funcDropShadow 3y ago
          Not, if you consider inserts, updates and deletes in addition to selects. Not, if you want to spread the queries over multiple transactions.
        • JohnBooty 3y ago
          It seems you don't have a lot of experience or understanding regarding stored procedures. Obviously if you just take a single query and turn it into a stored procedure then yes, the round-trip cost is the same. This seems to be where your knowledge ends. Perhaps we can expand that a bit. Let's look at a more involved example with procedural logic. This would be many, many round trips. https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/using-stored-procedures-to-provide-an-applications-business-logic-layer/ https://www.red-gate.com/simple-talk/databases/sql-server/t-... I'm not exactly endorsing that example. Personally, I would almost never choose to put so much of my application logic into a stored procedure, at least not as a first choice. This is just an example of what's possible and not something I am endorsing as a general purpose best practice. With that caveat in mind, what's shown there is going to be pretty performant compared to a bunch of round trips. Especially if you consider something like that might need to be wrapped in a transaction that is going to block other operations. Also, while you may be balking at that primitive T-SQL, remember that you can write stored procedures in modern languages like Python. https://docs.snowflake.com/en/sql-reference/stored-procedures-python https://docs.snowflake.com/en/sql-reference/stored-procedure...
          • preseinger 3y ago
            good lord man, the condescension is so thick and rich, it's like i'm reading an eclair, and the eclair is insulting me based on its own misunderstanding of the topic of conversation powerful stuff