Why we use stored procedure instead of query?

In our previous two posts, We have explained the concept of the stored procedure, we have understood how to create stored procedures. If you haven’t read these previous posts, I would strongly assist you to do so before reading this post.

• How to Create stored procedure with input parameters
• Create Stored Procedure with return values

In the article, we will discuss the below points

• Advantages and disadvantages of stored procedure
• Difference between inline Sql query and stored procedure
• Drawbacks, the reasons for not to using stored procedures

Disadvantages of stored procedures in Microsoft SQL?

• If you want your SQL code to be portable, then I will suggest you don’t use stored procedures. Because at a certain point if you want to port the database for example from MySQL to PostgreSQL or Oracle then you will have to port all stored procedures you have written in the database.
• Debugging and testing are not an easy task in case of store procedure.
• Stored procedure code is not as robust as backend app code, Let’s assume that we have a function in which we performing nested looping the In the procedure it can become more complex.
• Creating stored procedures you need a DBA specialist, especially as they get more complex.

Advantages of using SQL stored procedures

Stored procedures provide better performance and they can also reduce Network traffic load.Let’s understand the advantage of using Stored procedures one by one.

• Stored procedures are Capable of reusing the execution plan

The biggest benefit of using stored procedures is that they’re capable of reusing the execution plan.
what do we mean by an execution plan? whenever we execute a query to SQL Server then it follows the three-step as given below.

1. Verifies the syntax of the query
2. Compiles SQL query
3. Creates an execution plan

So when we issue the same query again since that execution plan is already generated and cached by the SQL server.so it will reuse that execution plan.

Stored procedures are capable of retaining the execution plan, caching that execution plan, and reusing that.

Nowadays in the latest version of SQL Server SQL queries are also able to reuse the execution plan. But slightly change in the SQL query can prevent the execution plan from reuse.

Now Let’s understand with an example

I have a table called TblOrganizationEmployee. which is got, the Id, EmployeeName, EmployeeSalary, Country, Adress, and DepartmentId columns.

Table SQL script

 

/****** Object: Table [dbo].[TblOrganizationEmployee] Script Date: 10/06/2020 7:12:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblOrganizationEmployee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeName] [nvarchar](max) NULL,
[EmployeeSalary] [float] NULL,
[Country] [nvarchar](max) NULL,
[Adress] [nvarchar](max) NULL,
[DepartmentId] [int] NULL,
CONSTRAINT [PK_TblEmployeeSalary] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
GO
SET IDENTITY_INSERT [dbo].[TblOrganizationEmployee] ON
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (1, 'Hunter', 80000, 'UK', 'Osaka', 1)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (2, 'Connor', 70000, 'India', 'Kyoto', 1)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (3, 'Ezra', 60000, 'USA', 'Vienna, Austria', 2)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (4, 'Aaron', 55000, 'USA', 'Tokyo', 3)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (5, 'Adrian', 85000, 'India', 'Amsterdam, Netherlands', 3)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (6, 'Easton', 72000, 'India', 'Barcelona, Spain', 1)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (7, 'Colton', 85000, 'Australia', 'Singapore', 1)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (8, 'Angel', 42000, 'Australia', 'Monte Carlo, Monaco', 3)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (9, 'Jaxson', 32000, 'USA', 'Cologne, Germany', 2)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (10, 'Greyson', 45000, 'Australia', 'Amsterdam, Netherlands', 1)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (11, 'Brayden', 78000, 'UK', 'Tokyo', 1)
GO
SET IDENTITY_INSERT [dbo].[TblOrganizationEmployee] OFF
GO

Now let’s say we want all employees from the table whose Salary is greater than 50000.

select * from TblOrganizationEmployee where EmployeeSalary>50000

when I execute the above SQL query what’s going to happen behind the scene?

The first time when we execute the query, the syntax is checked, the query is compiled and an execution plan is created for the query. And the database engine executes the above query returns the data back to us.

Now when we execute you above SQL query again, then SQL server reuses the execution plan and it will return data quickly.

But if we change the where clause parameter, if I pass EmployeeSalary is equal to 40000 then the SQL server is not going to reuse the execution plan.

It will create a new plan for this query.

select Name from [dbo].[Users] where Id>40000

But in the case of the stored procedure, it reuses the execution plan. Look at below, I have the stored procedure.

This is a simple store procedure that taking @EmployeeSalary as a parameter.

Create Procedure [dbo].[spGetOrganizationEmployee]
@EmployeeSalary int
as
Begin
select * from TblOrganizationEmployee where EmployeeSalary>@EmployeeSalary
End

so we pass the parameter to the stored procedure it returns all employees with salary greater then pass in value.So for executing the stored procedure we use below syntax

spGetOrganizationEmployee 4000

So when executing the above query, the identical thing happens, the procedure is compiled, and then an execution plan is created. when we execute this again, the execution plan is reused.

And if we change the @EmployeeSalary parameter to 50000 the same plan will be reused in case of stored procedures but not the case with SQL query.

• Reduce the Network traffic

Another great advantage of the stored procedure is that they can reduce Network traffic load.
Let’s understand that with an example.

Create Procedure [dbo].[spGetOrganizationEmployee]
@EmployeeSalary int
as
Begin
select * from TblOrganizationEmployee where EmployeeSalary>@EmployeeSalary
End

if you look at below store procedure, it is a simple 1 line store procedure .

As we know that, the body of the stored procedure between Begin and End Block, contains all logic.
But in reality, stored procedures can have 5000 lines of code within them lots of logic written in the body.

Now when we have such a type of large stored procedure and we want to execute that stored procedure.

we have to do is from the client-side application and can be a Php,.Net application, Java application. We only need to specify the name of the procedure and the parameter values and then we can execute that procedure.

spGetOrganizationEmployee 4000

So over the network, only two words get transmitted just

spGetOrganizationEmployee 4000

So in case, we don’t have a stored procedure to that then we need to write an SQL query. and then we send it from the client tools.

we have to send all those 5000 lines of code, which is going to have an impact on network traffic.

• Reusability and better maintainability

As we know that store procedure resides on the server, and many applications can re-use that store procedure.

For example, multiple applications using our database and they want a similar kind of data.so in this case we can create a store procedure and they can simply call the stored procedure.

The advantage of creating a store procedure is that you can change the logic in the procedure without needing to update App1, App2, and App3.

So that you only have to change the code in place and irrespective of that being used by multiple applications.so the same part of code is used again and again so, it results in higher Performance.

• Security

Stored procedures are developed, keeping in mind about Authorization also i.e you can set permission to the store procedure who can access your store procedure.

So we can grant permissions for a specific user, for other users you can restrict the permission.

Let’s look at an example of that.

lets us have below store procedure in our database and this database is used by the multiple application.

Create Procedure [dbo].[spGetOrganizationEmployee]
@EmployeeSalary int
as
Begin
select * from TblOrganizationEmployee where EmployeeSalary>@EmployeeSalary
End

As we know that salary is a confidential part of any organization ,I want to provide allow all users to access the above store procedure.
In that case, if I denied access to the specified user so that he can not able to access the above store procedure

The post Why we use stored procedure instead of query? appeared first on Software Development | Programming Tutorials.