Hacker News Reader

Comment by koolba | Hacker News Reader

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

View on HN

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

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

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

It's very system dependent, but VoltDB is designed for systems that require up to a million tps. "A matter of ms" is not irrelevant in such systems.

Yes, ok I agree. 100% agree.

But micro optimization has always the same caveat:

Of course it is not irrelevant, it might even be very important, in very specific cases.

But if you are not one of those cases (and you should already know if you are) you shouldn't even bother with it.

I think that everything you just said categorizes VoltDB already, though. They're not advertising themselves as a general purpose database. You'll know if you have a use case for it.

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

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

On the other hand cloud sql is 400 micros from web servers. 10 round trips is like 10ms.. Not even visible to an end user.

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

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

And parsing the query and generating a query plan for those 10 queries is obviously free.

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

You can also solve this with prepared statements which for example Active Record does for you for free.

Reply on news.ycombinator.com