Hacker News Reader

Comment by keithnoizu | Hacker News Reader

On high volume applications I would avoid it since it's usually easier to horizontally scale web servers than sql servers, and it makes cache strategies more difficult depending on how and what you're querying.

View on HN

It is a common misconception that more data processing in SQL puts a higher load on the database. A typical database spends 96% of its CPU time on logging, locking, latching and marshalling [1][2] rather than processing SQL. By sending less data to the middle tier and performing fewer round trips, the use of stored procedures means that the database can actually do more real work.

[1] https://dzone.com/articles/mit-prof-stonebraker-%E2%80%9C

[2] https://drive.google.com/file/d/0B7jyeB8kxFPjU0VySkF3UHhoVnM...

It depends. Regardless there is limited CPU, and so any scenario in which a stored procedure uses more CPU than a simple query will cause you to hit that saturation point sooner.

I generally have layers of caching on top of the sql server so the majority of queries will be integer equivalency or range checks, if not get by primary key queries. So I am not generally operating in a scenario where a stored procedure would reduce record scans, etc.

I generally also don't run on transactions out side of limited scenarios, since throughput is usually more important to me than data consistency.

sorry, I don't get this at all. How is using stored procs instead of an ORM going to adversely affect your cache strategy?

Imagine for example that you have per record caches. You can run a query to get ids with out joining to the table and then simply fill in any gaps in your cache with a follow up query.

Reply on news.ycombinator.com