When to use PrepareStatement or Stored Procedure ?

Posted: November 1, 2012 by Narendra Shah in Java Box
Tags: , ,

In JDBC application, one and foremost question either to use stored procedure or java Preparedstatement. To search this it took around 2 days and finally understood the concept.

In JDBC when ever we use PreparedStatement  it would be compiled for that particular connection, so once you close the connection and open new connection PrepareStatement would be recompiled. So here if you want to leverage preparestatement then you it should be on single connection, or else if we are performing multiple connection object and using preparedstatement will reduce performance for that statement/query because there will be precompilation process happens at each connection level.  I hope this will be much clear in terms of using PreparedStatement

Using stored procedure is being standard procedure for join or big queries, it is definitely good idea, but it will restrict database portability in your application, once you decide to migrate  the database to another RDBMS system, you may need to rewrite the stored procedure again. In regards to performance benefit of stored procedure, it is giving performance benefit because database engine need not to recompile the sql statement again and again. This will affect microsecond  level, but if  some query is perform frequently or used widely, we should convert it to stored procedure.

  1. hmpandya says:

    gr8, simple concept that every developer should know before going into field!! 🙂

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s