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 :