5 ms·
> "Use stored procedures instead" - in most use cases this is prolly a really really bad idea. This is a common recommendation. The basis for it is two fold, 1
by koolba 11y ago
> "Use stored procedures instead" - in most use cases this is prolly a really really bad idea.
This is a common recommendation. The basis for it is two fold, 1) You strictly define the inputs for your transaction as the parameters for the stored proc and 2) You have a single round trip from the app to the DB.
For a complex transaction involving says 10 separate operations (insert X, query Y, update Z, ...) that's 10 network roundtrips that are saved. Add to that eliminating the transit of data from the DB to the app server.
- brianwawok 11y agoOn the other hand cloud sql is 400 micros from web servers. 10 round trips is like 10ms.. Not even visible to an end user.
- jhugg 11y agoIf you need to hold a lock for 10ms in order to ensure things are transactional, it can have a bigger impact. Also, if you get a slow delivery even N packets, where N seems large, you’ve just incresed your chances of hitting that issue 10x. This is brutal on long tail latencies. But no, not all systems need this.
- brianwawok 11y agoYa exactly. If you are writing something high performance, I don't think you want locks. You are more likely to do append only and sort it out when you do a read or such (or fancy magic down the Cassandra route). For stupid webapp, meh. Most of the time when a page load takes 10 round trips to the DB it is not for a lock, it is to read a bunch of tables to render a page.
- qaq 11y agoAnd parsing the query and generating a query plan for those 10 queries is obviously free.
- marcosdumay 11y agoThat's often done once and cached. It is worth keeping one eye at those things. They can slow your program down badly, and go unnoticed a lot of times. But most of the time they don't slow you down at all, and when they do it's often much better to solve the problem by rewriting your queries into something that batches more data. Yes, once in a long while it makes sense to create a stored procedure to deal with it. But this is the exceptional situation that deserves explaining - not the other way around.
- ajmurmann 11y agoYou can also solve this with prepared statements which for example Active Record does for you for free.
- Kesty 11y agoI'm just guessing here but this to me seems a bit too micro optimization. Great when you are a huge company with massive number of data and operations where every ms counts, but in most cases there are bigger and more important things to optimize before saving some round trips from the db.
- qaq 11y agoIf you use sps you actually have much less coupling to the structure of your DB that let's you rearrange/optimize things on DB side without affecting the client apps. Plus you can gain serious security benefits by using sps only.
- jhugg 11y agoIt’s totally true that this optimization only matters if you’re pushing your database. Of course as a VoltDB employee, we have a number of smaller customers who use VoltDB because it allows a small shop to do things that would be too expensive with a traditional system. Just because you’re small, doesn’t mean you don’t have a high-throughput problem. One example is Xacte, which tracks marathon bibs as they fly by RFID sensors in the course. The issue is when 50 people run by a sensor in one second, the de-duplification code in the sensor sometimes breaks down, and their system gets hundreds of triggers. They literally have a van full of equiptment that includes a small VoltDB cluster keeping accurate runner positioning for events like the LA Marathon.
- ajmurmann 11y agoRound trips over the network of any kind are actually the fastest way to make your application slow. So if you do optimize for performance then this is a good starting point, if you do go over the network a lot. But you are right that you should think twice and first make sure you even built the right thing.
- Kesty 11y agoYes, I agree. But, in many (if not most) situations the ms you loose by doing more round trips then necessary are not worth how much more maintainable the system is. Especially when there is probably some programming or query optimization that will save you seconds, when reducing the round trips is a matter of ms.