Hacker News Reader

Comment by preseinger | Hacker News Reader

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

View on HN

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

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

    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.

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?

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.

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?

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.

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?

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.

Not, if you consider inserts, updates and deletes in addition to selects.

Not, if you want to spread the queries over multiple transactions.

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-...

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-procedure...

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

That'll happen when you're publicly and confidently wrong. If you scroll up, you'll find any number of posts where folks pointed out your misconceptions in a more kindly fashion. When we factor in the fact that you're wrong (as opposed to my post which is correct, informative, and cites examples) I think many would say your incorrect assertions are a lot ruder and less HN-worthy.

Sometimes folks know more about a given thing than you do. That is okay. The goal is to learn. I am sure you know more than I do about zillions of things. In fact, that is why I come here. People here know things.

haha, man, the absolute _chutzpah_ you need to make (incorrect) accusations like this, even as an anonymous person on the internet, is really breathtaking

i hope you reflect on this interaction at some point

You seem to be firm in your objectively wrong belief that:

    a stored proc is just a query saved on the db server, nothing more

Absolutely not. They can contain procedural logic as well. You can do a wide range of things in a stored proc that are far beyond what can be done with a query. Again.... I provided some links with examples. You don't need to believe me.

    i hope you reflect on this interaction at some point

Wow.

Reply on news.ycombinator.com