Cloud Zone is brought to you in partnership with:

I am still a big nerd in a (not so) small body. A technology freak with years of experience on anything from PL1/mainframe to LAMP. I love to code and talk about coding, especially on state of the art technologies - but only those that make some sense. My specialty is taking good technology and turning it to a cool product. I've done this in my previous roles, and I'm doing it right now with my new company, ScaleBase. Liran has posted 21 posts at DZone. View Full User Profile

Can Prepared Statements Improve Your Scalability?

05.26.2011
| 12094 views |
  • submit to reddit

Everybody knows that using prepared statements for your database access greatly improves latency times. My point, in this blog post, is that it can also improve your database scalability. How? Just read on.

What does a database do with a statement?

Well, each statement takes a toll on the database resources, as there are many tasks to be done with each SQL statement. For example, the database needs to maintain isolation level (read more about the pain that surrounds this issue here). It needs to flush buffers to disk when a commit occurs. It needs to maintain locking. And it also needs to do a very complex task – parse and optimize the SQL statement.

This is a necessity. The database has to parse the SQL command, and string parsing is a CPU intensive action, especially for a complex language such as SQL.

Optimizing – well, optimizing is even more complex than parsing. Since optimizing involves building the best execution plan for the query, and can greatly improve the performance of a query, it has to be executed.

So, since we must parse, and we must optimize – why mention it? There is no way around it. However, let’s just be clear. Since both actions are CPU intensive they have great impact on the performance of the database and the number of concurrent actions it can perform – hence their impact on database scalability.

What is the database scalability barrier?

A good proof for this point is the wonder post by Yoshinori Matsunobu. You can read it here. It explains just how much time the database spends on SQL parsing, and shows what happens when you “cut out” the SQL parsing bit of the database and use the InnoDB engine API directly.

In the blog post Mr. Matsunobu was able to reach a state where his MySQL supported more hits than Memcache.

So I think it is safe to say that SQL parsing and statement optimization have a great effect on database scalability.

What does a database do with a prepared statement?

So what can we do? Must we remove our beloved ORM code and replace it with direct InnoDB calls? Well, for some applications that’s definitely true; but not for all, and for most applications it’s just not feasible.

Welcome to the world of prepared statements. With prepared statements, the database first gets the statement, parses and optimizes it, and from that moment on just gets parameters and executes an already parsed and optimized statement – resulting in great performance improvements for the client and a much more “relaxed” action for the database.

Note that the prepared statement is handled on a connection level – one more great reason to use connection-pooling mechanisms.

How to do it

If you’re not familiar with using prepared statements, I gathered some links that explain how to write them in your programming language.

PHP

http://php.net/manual/en/pdo.prepared-statements.php

Java

http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Note that for legacy reasons the MySQL JDBC driver only mimics prepared statements on the client side, and does not translate those to Server Side Prepared Statements – so the performance benefits are lost. To enforce Server Side Prepared Statements use the useServerPrepStmts parameter. Check here for more info.

Ruby

I’m not a Ruby expert, but it looks like ActiveRecord doesn’t support prepared statements, so that’s a big problem with Rails.

Ruby itself however, does support prepared statements, http://blog.aizatto.com/2007/05/19/connecting-to-mysql-using-ruby/

C#

For MySQL – http://dev.mysql.com/doc/refman/5.0/en/connector-net-programming-prepared.html

Summary

Prepared statements offer many attractions – in performance, security and more. I strongly urge you to use prepared statements whenever you can – you will feel the benefits immediately.

References
Published at DZone with permission of its author, Liran Zelkha. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Alex(JAlexoid) ... replied on Thu, 2011/05/26 - 3:33pm

Prepared statements can help to scale better, but being always better options is not the case.

MySQL prepared statements will definitely help out with performance, but that is MySQL property not a general property of a prepared statement.

Consider PostgreSQL. Where a prepared statement might not result in higher performance and even can degrade performance.

 

The major point of why most people are OK with SQL parsing overhead, is that it's an embarrasingly paralellizable operation. SQL parting part will scale linearly, yet the problem is usually lies with the synchronisation, locking and other operations.

Jason Marshall replied on Sun, 2011/05/29 - 3:01am

As recently as Oracle 9i (I haven't verified this in later versions) there was a limit to the number of parallel queries that could run.
We had an app that was saturating the production hardware, but the weird thing was that no single resource was above 50% utilization. Not disk, CPU, memory bandwidth, or networking. We eventually narrowed it down to the query solver in Oracle. All in-flight queries had to fit into the query plan cache to run. A misinformed engineer (now our boss, yay) had written our main DAO routine without full PS support, so we were generating hundreds of unique queries per second and it was gumming up the works.
Proper use of PS got us to dozens of unique query plans.

David Workman replied on Sun, 2011/05/29 - 5:53pm

The latest version of ActiveRecord in Rails 3.1 (currently at RC1 status) is able to use prepared statements and does automatically.

David Whitmore replied on Tue, 2011/05/31 - 4:18am

Interesting about the performance implications.  I wasn't aware of them. 

However, the main reason that I use prepared statements is for the protection they offer against SQL injection attacks.  I can't help feeling that people should use them for this reason if nothing else...

Joay Sim replied on Thu, 2013/02/14 - 9:38am

 Thank you so much for the post you do. I like your post and all you share with us is up to date and quite informative, i would like to bookmark the page so i can come here again to read you, as you have done a wonderful job.

Relationship Tips by Benpi1

Ron Sim replied on Thu, 2013/02/28 - 9:59am in response to: Alex(JAlexoid) Panzin

 This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.

        chakratantramassage.com

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.