Use of Prepared Statement and Stored Procedure

What is the use of Prepared Statement or Stored Procedure? When should we use simple statement, prepared statement or stored procedure?

To understand the answer for above questions, we first need to understand that how database systems manage and execute the query. When we ask database system to execute any query, there are few steps in execution. Major steps are

  • Parsing of query
  • Compilation
  • Execution
Database system parses the supplied query; hence break it to the tokens to understand what is being supplied. Compilation step process the parsed tokens and create the execution structure which can be database specific. Mostly these are the expressions which evaluate the query tokens during execution. For example:

Query: Select * from states where ‘statename’ = ‘abc’

Parsing step will break it to the tokens and will create a structure which compiler can understand. It can be as simple as list of words in query or different. The implementation is specific to database server implementation.

Compilation will further take these tokens as input and will create the execution structure for the query. For above query, it can be like
  • Select Expression
    • Select Table Expression (states)
    • Select Column Expression (*)
    • Select Filter Expression (statename, and ‘abc’)

This is the structure which will be executed while fetching the result for a query from database. This finally created structure executes the logic to fetch the desired result from database storage system.

Note: There can be many more steps in-between, however above mentioned steps are at high level steps.

Here you see that there is a remarkable time, which must be spent on these steps before actual execution of the query.

This is the point where prepared statement and stored procedures help us. Both of these performs the above mentioned step on query and store it for execution later. Now if later, we are changing the parameters or executing the same query again and again, database system does not need to parse or compile the structure again because it is already created and stored. Database system simply uses the existing stored structure and uses it for fetching the data. This is the magic which saves us a lot of time, especially when

  • One query needs to be executed repeatedly
  • Query needs to be executed repeatedly but with changed parameters

Above is the common use of Prepared Statement and Stored Procedure which is the faster execution of the query and speedy data retrieval.

Other benefits of Stored Procedure over these common benefits are:

  • Stored procedures store the structure at server and also whole logic executes on the server. It results in round trips over the wire as whole logic is executing at the server.
  • Stored procedures can contain business logic which is dependent on multiple database interaction. Like fetch some data from database and then based on the fetched state, retrieve some more data from other tables. In general, we shall fire first query, perform some business logic locally in code, and then fire second query to fetch the final result. However in case of stored procedures, we can put all of this logic in it and whole logic will be executed at server side
  • Stored procedure can return more than one output parameters.
  • Stored procedures can take more than one input parameters
  • Stored procedures are supported by very powerful PL/SQL and hence most of the required business logic can be implemented here.

However please ensure that stored procedure should contain only logic related to database. It should not be built for general business logic, as that will make the code unmanageable later. 

People who read this post also read :


Amit Gupta said...

My few cents:

* Usually when ever we talk about prepared statement and stored procedures, there intention is to reduce execution time and improve response time. As clearly mentioned in above article how they improve over all execution time by reducing n/w traffic, saving parsing and time taken in creation of select plan etc.

* There is a very import part as far as database maintenance is concerned. That is purging. This is one technique that can save every one's life starting from customer all the way till DBA.

* We always have a habit to say that we need all the data all the time which may not be true. Talk to customer clear about their requirements and educate them about the cost of keeping every thing in data base tables.

* Purging plans should be created by developer who is owner of table and reviewed by architect.

* Purging can happen on daily/monthly/quarterly bases. We should be very careful in creating proper purging plan for tables that grow very fast. They have potential to reduce server response to a great extend even though ever thing else is fine.

* I see it as a low hanging fruit which we should taste first before going for many other complex techniques

These points may be very little related (with above article) still it may help to get that additional advantage which can create WOW for customer as far as performance is concerned.


Mohit Gupta said...

This is an important aspect of database maintenance. Thanks!

Post a Comment