Urban Myths about SQL (2015)

http://docslide.us/technology/urban-myths-about-sql.html

by Tomte • 10 years ago

31 55 10 years ago

docslide.us

merb3• 10 years ago

"Use stored procedures instead" - in most use cases this is prolly a really really bad idea.

copperx• 10 years ago

Why is that?

golergka• 10 years ago

Maintenance.

koolba• 10 years 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.

brianwawok2• 10 years ago

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.

Kesty5• 10 years ago

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.

batterseapower• 10 years ago

By "stored procedures" the author seems to mean what is more usually described as "prepared statements": https://docs.voltdb.com/UsingVoltDB/DesignProcAnatomy.php#De...

jhugg13• 10 years ago

VoltDB supports prepared statements AND stored procedures. It also supports ad-hoc SQL and will cache auto-parameterized intermediate representations.

SQL is only slow if the system has never seen it before and needs to plan/optimize, which typically happens rarely in an OLTP system that does the same thing over and over.

acdha2• 10 years ago

“really bad idea” seems too strong for me – I don't think it's good general advice, at least in that simple a framing, but I'd focus on the actual downsides: you're writing a lot more logic in SQL and that means you need to manage it in two places while maintaining test coverage, deployments, etc.

Many places do that terribly – no version control / copy and paste from a separate system, different groups own the code and the database, etc. – but that doesn't mean that you wouldn't have a far better existence if you followed modern practice, used a good migration framework, etc.

merb3• 10 years ago

It's not too strong. I actually meant "in most use cases" which in fact is true, most people are slow anyways. Your database or the roundtrips are mostly not the performance factor nr. 1

jhugg13• 10 years ago

The big issue here is the idea of moving logic to the data, rather than data to the logic. One of these things is much bigger than the other. If you have a multi-statement transaction with intersitial logic, client-side logic is going to get absolutely smoked by a stored procedure.

There are downsides; suddenly logic in your app is split between client side and server side, which is annoying.

At VoltDB, we do our best to make the situation better than it is in more traditional systems.

- Stored procedures are Java, and can be unit-tested and debugged live.

- Stored procedures can be transactionally added, removed and upgraded in a cluster-wide operation that happens at a single-logical point in time.

- Stored procedure code (and schema) is archived with all snapshots, allowing for portability.

- You can run stored procedures on your laptop development machine, then push the same bits to a pre-production cluster, then push the same bits to production.

- Stored proceudres can share code and even use inheritance, reducing putting the same logic in many places.

One way to look at it is the set of prepared statements and procedures on your VoltDB cluster is sort of a state/processing-API. It works really well when you’re processing streams of events and you have one procedure per event type.

mdellabitta2• 10 years ago

You say JDBC is slow, but what if you're using PreparedStatements and PreparedStatement caching? Isn't that similar?

mangeletti3• 10 years ago

Not to mention, stored procedures mean having a DBA, or developer well versed in DBA, available when you want to make changes to your app, which isn't always an option.

wtbob• 10 years ago

I'm still pretty firmly in the camp which believes that a lot of logic ('business rules,' if you like that phrase) should reside in the database. IMHO DBAs are useful, in much the same way that ops folks are useful: they have a different skillset and point-of-view than do developers, and are a necessary component of any team dealing with data.

Playing fast-and-loose with one's data is as bad as playing fast-and-loose with one's processing of that data.

jhugg13• 10 years ago

Most VoltDB customers have no DBA. DBAs can sometimes be pretty hostile during POCs because with VoltDB and many other non-tradtiional systems, they often have a smaller role.

Some DBAs are great though and bring experience that can be really valueable when building an app. Depends on how open-minded they are.

matwood4• 10 years ago

Not to mention, stored procedures mean having a DBA,

Not anymore than writing SQL requires a dba. If you have developers writing SQL that cannot handle creating sprocs, then you already have a huge problem.

matwood4• 10 years ago

"Use stored procedures instead" - in most use cases this is prolly a really really bad idea.

Of course, so when I need need to do operations on large sets of data lets pull it all into an app, operate on the data and then push it back. I would not be no snarky if I had not seen it done because someone didn't like stored procedures.

zzzeek• 10 years ago

Those aren't the only two choices.

jerf• 10 years ago

The presentation is clearly focused on high-performance use cases. In that context, they make a case that be it bad idea or good idea, it's your only choice, because you can't afford a back-and-forth conversation between the program and the database for every little query.

How true that is I can't personally vouch for, having never examined the network protocols for the DBs that closely. I'm not sure what the conversation is that they are referencing when it seems like the client can send the query + all data in one shot and the server ought to be able to start streaming responses pretty quickly. But it wouldn't be the first time that either the protocols or my understanding of the situation is suboptimal. I'm really just relaying the argument and making the point that the context matters. There's a lot of DB usage in the world where none of this matters because your performance needs are frankly blown away by what even "slow" DBs can do.

jhugg13• 10 years ago

Consider the op, "put me in line if the line isn't full"

Query 1: Check if the line is full. Query 2: Conditionally put me in line.

First, this requires two trips. Second, the line could fill up between these two trips, cause the second line to put you in a full line. So, you hold a lock between the two statements. Now you have a third round trip to commit or abort, and the line is frozen for new additions while you talk over the network for two more round trips.

Some examples are better, some worse, but this kind of thing is way better if you bundle up the logic and send it in one round trip.

jhugg13• 10 years ago

I’m pretty sure this is Mike’s GlueCon 2010 presentation verbatim. Not sure why it says 2015.

Plenty has changed since 2010 about VoltDB. If people have any questions I’m happy to answer.

exabrial• 10 years ago

Is it still stored procedures only?

jhugg13• 10 years ago

No, you can run arbitrary SQL. You can even send it over an HTTP/JSON API.

If you want a multi-statement transaction with logic, you still need to use a procedure. 1TXN = 1RndTrp is still true.

Kesty5• 10 years ago

This seems suspiciously like a marketing presentation for VoltDB more than debunking myth about SQL.

Tl;Dr; SQL is too slow ? No, VoltDB is Fast !

jhugg13• 10 years ago

It was presented in 2010 at gluecon and was the launch deck for VoltDB. A big chuck is marketing, yes. Doesn’t make it wrong as a counterexample.

Though in 2016, there are a lot more fast SQL systems and it’s unclear this myth is still as mythical as it was?

mangeletti3• 10 years ago

I agree. This is basically marketing material from VoltDB.

I've flagged it, not because it's not interesting (some of it is), but because marketing propaganda can't really be trusted as a source of information.

BenoitP• 10 years ago

While it is clearly propaganda for VoltDB, it does a good job of adressing some of the myths of SQL-slowness that we don't see very often.

Usually a lot of people saying SQL is slow is because they don't really use it well. He doesn't talk about indexing and statistics [1] for example.

But he talks about ODBC/JDBC because, while it is usually fast enough, a lot of people overlook the fact that these interfaces hold transactions externally.

Besides, the presentations comes from Stonebreaker himself; of (Turing prize, Von Neumann Medal, ingres, postgres, cstore, vertica, hstore, SciDB, Tamr, Streambase)-fame.

[1] http://use-the-index-luke.com/ is a very good resource for this.

jasode• 10 years ago

Yes, that slide deck in isolation looks an ad so it's probably better to get some background on Michael Stonebraker's "big picture" beliefs and core premises:

++ He believes SQL is the "right" and most proven access model. (Note that SQL does not mean RDBMS like Oracle/MSSQL.)

++ He believes in ACID

++ He believes that other db engines' (NoSQL, etc) approaches to gain speed and scaling of clusters by sacrificing SQL and ACID is wrong and his proof is that those other products end up eventually bolting on SQL and ACID anyway.

I think a better presentation to flesh out these ideas is the following youtube video: https://www.youtube.com/watch?v=KRcecxdGxvQ

It's 55 minutes long so to save time, HN readers may prefer to browse it by pressing "L" key to step through it quickly. Just read the slides shown behind him and if the bullet points look interesting, rewind and listen to his presentation.

One can dwell on those SQL/ACID merits in isolation and consider separately, the question of whether VoltDB-as-a-product fulfills those ideals.

saintfiends• 10 years ago

It's not SQL that is slow, but when you try and distribute a relational database you lose Referential integrity, Transactions, Unique Indexes and other such key features which are why you actually chose a relational database in the first place.

I'm yet to see a database which does all of that and still is truly distributed.

jhugg13• 10 years ago

VoltDB lets you keep transactions, and the strong consistency lets you enforce many constraints (though some in stored procedure code).

Cluster-wide uniqueness has some limitations, but there are lots of tools we offer that are way better than nothing.

Much of what you get in a relational DB, you can keep, and you can scale.

eterm• 10 years ago

I sometimes wonder if the "cloud first" mentality has led people to distributing databases way way before the need for it. I get that "big data" is in the in thing right now, but non-distributed databases still scale to huge amounts of data. I suspect there are plenty of services running over distributed servers instead of a couple of appropriately sized principal/witness servers.

Beyond that, it's better to distribute by splitting databases across services or uses, so instead of having a mono-database running over a cluster, it's better for example to have an OLTP database and a separate OLAP database for reporting. It's easy to split those out to different servers rather than having a mono-database distributed over a cluster.

Going beyond that, data partitioning is more sane, so you partition the data so that different sets of non-interacting data are in different places. So you can for example partition by customer, then each of those partitions can be arranged to different servers where appropriate for load. There shouldn't be a need to have transactions hitting more than one partition at once when partitioned correctly.

And none of that breaks referential integrity.

jhugg13• 10 years ago

Well, even for smaller use cases, systems with peer-to-peer clustering over a lan have better high-availability profiles than primary/secondary replication systems.

Urban Myths about SQL (2015)

Related Stories