DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Securing Your Software Supply Chain with JFrog and Azure
Register Today

Trending

  • Building a Flask Web Application With Docker: A Step-by-Step Guide
  • Implementing a Serverless DevOps Pipeline With AWS Lambda and CodePipeline
  • Using Render Log Streams to Log to Papertrail
  • Cucumber Selenium Tutorial: A Comprehensive Guide With Examples and Best Practices

Trending

  • Building a Flask Web Application With Docker: A Step-by-Step Guide
  • Implementing a Serverless DevOps Pipeline With AWS Lambda and CodePipeline
  • Using Render Log Streams to Log to Papertrail
  • Cucumber Selenium Tutorial: A Comprehensive Guide With Examples and Best Practices
  1. DZone
  2. Data Engineering
  3. Databases
  4. Can Prepared Statements Improve Your Scalability?

Can Prepared Statements Improve Your Scalability?

Liran Zelkha user avatar by
Liran Zelkha
·
May. 26, 11 · News
Like (0)
Save
Tweet
Share
14.03K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Database Scalability MySQL

Published at DZone with permission of Liran Zelkha. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Building a Flask Web Application With Docker: A Step-by-Step Guide
  • Implementing a Serverless DevOps Pipeline With AWS Lambda and CodePipeline
  • Using Render Log Streams to Log to Papertrail
  • Cucumber Selenium Tutorial: A Comprehensive Guide With Examples and Best Practices

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: