How to Increase Performance of Stored Procedures

When we fire SQL for the first time, three things happen:

• The SQL syntax is checked for any errors.
• The best plan is selected to execute the SQL (choice to use clustered index, non-clustered etc.).
• Finally the SQL is executed.

As in one of the article Mr. Shivprasad koirala says that Stored Procedures Do Not increase performance. Performance of inline parameterized SQL is the same as that of Stored Procedures. They had also proved why Stored procedure to be used.

Differences between Stored Procedure and User Defined Function in SQL Server

Stored Procedure is a group of SQL statements that has been created once and stored in server database. It’s pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. Stored procedures will accept input parameters so that single stored procedure can be used over network by multiple clients using different input data. Stored procedures will reduce network traffic and increase the performance.

Benefits of Stored Procedures
- Precompiled execution
- Reduced client/server traffic
- Efficient reuse of code and programming abstraction
- Enhanced security controls

Difference Between SQL And PL/SQL

SQL pronounced as a sequal which is stands for Structured Query Language. With the help of SQL, you can view data using Data Definition Language (DDL) Commands as well as manipulate data using Data Manipulation Language (DML) Commands.

PL/SQL stands for Procedural Language/Structured Query Language. It is the Oracle’s procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers software-engineering features such as data encapsulation, overloading, collection types, exceptions, and information hiding. PL/SQL also supports rapid prototyping and development through tight integration with SQL and the Oracle database.