Why to use Stored Procedure or Prepared Statement?

In web development, we do face the issue of running a query few thousands to million times everyday. If your website traffic is small then the impact of such queries is not noticed. but if your site attracts lots of users and the query requires to run a good number of time, it might hurt your database performance. every query we run goes through a rigorous cycle to produce the desire result. it goes from parsing, optimizing, executing and returning the result. once a query is written and ran correctly for the first time then the steps of parsing and optimization is not necessarily requires as it is already been parsed and optimized. for the same query it is kind of redundant efforts going on. but what if we can just execute the query and get the result and bypass few of the early steps? well it’s possible and that is where the concept of Stored Procedure and Prepared statements come from. now let’s look at the detail of it

 Prepared statement:

Prepared statements are the ability to set up a statement once, and then execute it many times with different parameters. They are designed to replace building ad hoc query strings, and do so in a more secure and efficient manner. A typical prepared statement would look something like:

SELECT * FROM table WHERE column = ?

The ? is what is a called a placeholder. When you execute the above query, you would need to supply the value for it, which would replace the ? in the query above.

benefits:

as from Harrison Fisk article on prepared statement

Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack. Normally when you are dealing with an ad hoc query, you need to be very careful when handling the data that you received from the user. This entails using functions that escape all of the necessary trouble characters, such as the single quote, double quote, and backslash characters. This is unnecessary when dealing with prepared statements. The separation of the data allows MySQL to automatically take into account these characters and they do not need to be escaped using any special function.

The increase in performance in prepared statements can come from a few different features. First is the need to only parse the query a single time. When you initially prepare the statement, MySQL will parse the statement to check the syntax and set up the query to be run. Then if you execute the query many times, it will no longer have that overhead. This pre-parsing can lead to a speed increase if you need to run the same query many times, such as when doing many INSERT statements.

following image will illustrate the fact

storproc.jpg

Stored procedure:
A stored procedure is a precompiled executable object that contains one or more SQL statements. Hence you can replace your complex SQL statements with a single stored procedure. Since, stored procedures are precompiled objects they execute faster at the database server. For the consecutive run it will run from the compiled stage and hence boost performance.

Note: You have to choose the when to use to use what. certainly not every query should be transformed to prepared statement  or stored procedures.  🙂

Advertisements

9 thoughts on “Why to use Stored Procedure or Prepared Statement?

  1. salish says:

    hai
    nice explained .Will u pls explain me how the time should display on the websit means i had a textbox (start time)another (end time)so popup the time which i can choose and on clicking that that time will be take to textbox then to database (the time also be corrected means to time start time and end time may not overcome)and the code is to be in jav ,javascriptor even that should come as tool for the jsp page like calender .Thank u in advance

  2. Nandakumar says:

    Good post.

    I feel stored procedures can be implemented if you are working with large projects with big database and frequent updations are required. |For small work , I feel stored procedure will not be efficient , simple query will be fine.

    Thanks

    Nanda

  3. Storedprocs says:

    I’m very surprised you did not mention the security aspect of stored procedures, when you did for prepared statements.

    With stored procedures you can limit granted privileges to just the statements you wish the user to execute, that way limiting the potential damage impact if a user/password combination where to become compromised.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s