6 ms·

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

• matwood 11y 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 11y ago
    Those aren't the only two choices.
• copperx 11y ago
  Why is that?
  • golergka 11y ago
    Maintenance.
    • UK-AL 11y ago
      Companies really like people doing it as well, as it locks you into their platform.
• 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 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.
    • jhugg 11y ago
      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.
      • brianwawok 11y ago
        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.
    • qaq 11y ago
      And parsing the query and generating a query plan for those 10 queries is obviously free.
      • marcosdumay 11y ago
        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.
      • ajmurmann 11y ago
• batterseapower 11y ago
  By "stored procedures" the author seems to mean what is more usually described as "prepared statements": https://docs.voltdb.com/UsingVoltDB/DesignProcAnatomy.php#DesignProcStructure https://docs.voltdb.com/UsingVoltDB/DesignProcAnatomy.php#De...
  • jhugg 11y 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.
• acdha 11y 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.
  • merb 11y 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
    • acdha 11y ago
      I wouldn't describe something which is a minor performance delta as a “really really bad idea” — save that for things which have reliability risks, security implications, etc. This is a tool which people should know about and consider when they have the right situation. I'm also not sure what you're basing the claim that “Your database or the roundtrips are mostly not the performance factor nr. 1” on, either – poor database usage is a notoriously common area for performance issues. Roundtrips are less common since things like unindexed or poor queries usually dominate first but it's certainly not hard to find examples of someone missing the problem when testing their app using a local database and then being reminded that latency matters the first time they try it using production-scale data on a real server.
• jhugg 11y 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.
  • mdellabitta 11y ago
    You say JDBC is slow, but what if you're using PreparedStatements and PreparedStatement caching? Isn't that similar?
    • jhugg 11y ago
      JDBC doesn’t have to be slow. VoltDB actually caches ad-hoc SQL plans so even they run pretty fast these days. You can use JDBC and get good performance. The biggest thing you lose with JDBC over the native VoltDB clients is better cluster awareness. In our native client you can get callbacks when servers fail, when backpressure is triggered, etc… Now Hibernate over JDBC is another issue. Edit: As merb points out JDBC is synchronous. This is true; if you have one client and one thread, this will be limiting fast. It's not a server-side issue though. If you have enough clients and/or enough threads, then you can still get very high throughput.
      • 11y ago
• mangeletti 11y 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.
  • matwood 11y 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.
    • mangeletti 11y ago
      Most developers I know aren't writing any SQL, and if they are, you might (not necessarily) have an even bigger problem[1]. ORMs are a valuable abstraction. It's no good to go about your coding life without a care in the world for SQL, just because ORMs exist, but it's even worse to write an app littered with SQL, which is a language most likely entirely unrelated to your app. 1. I audited a code base for a friend a few weekends ago, and it was littered with SQL injection vulnerabilities, because the developer didn't like ORMs.
      • 7sigma 11y ago
        You don't necessarily need ORMs. If the developer had used prepared statements (e.g. PDO in php), then there would probably have been a lot less vulnerabilities.
      • matwood 11y ago
        Hand concatenating SQL is also not a good thing. Prepared statements and/or something like JOOQ (which is my goto in Java nowadays) would have prevented most if not all of the injections. Sprocs also go a long ways towards preventing SQL injection. One could still build sql statements through concatenation inside the sproc I guess. /shudder.
  • jhugg 11y 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.
• jerf 11y 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.
  • jhugg 11y 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.