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.
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.
- Verifies the syntax of the query
- Compiles SQL query
- 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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Amount of single quotes when running dynamic SQL with variable
- SQL Server: log modifications
- can't get sum(column) with left join on row ids
- Binary Type to Actual Binary
- Adding and Naming TABS to excel reports in Microsoft Reporting Services
- JOIN on concatenated column performance
- SQL ROW_NUMBER() ... between x and y breaks Inner join
- sql server connection error
- How do I estimate the execution time of a SQL Server Update Statement?
- Is there an Alternate for Where is Null using Where = Null?
- InsertOrUpdate throwing error for identity primary column
- SQL Server : not inserting date when date is already present in table
- Do Clustered Index and the table on which it is created both contain the actual data?
- TSQL CREATE ASSEMBLY FROM varbinary breaks the class_name parameter
- Rank of an item in SQL Server
- SQL Server collation resolve
- SQL records only for 3 consecutive months
- SQL AlwaysOn - What if you don't use it as a cluster/failover?
- Check and compare column values in sql server table
- ASP.NET Web API upload image to SQL Server database
- Using table variables in stored procedures versus merely selecting from tables or a view?
- SQL Server: Calculate amortization
- error when running any query in sql server management studio: the file exists
- Multiple Rows for same identifier
- Diff b/w time in two rows in sql server
- call serverside method from html5 button onclick event
- Exclude multiple values using <>
- Making a MAX() query from a subquery with COUNT()
- How do I count occurrences by day in SQL?
- CLR Procedure Not list in processes SQL Server
- Converting Excel Formula involving *OR to TSQL
- sql server coalesce get first not null date
- SQL Server : how to parse this xml into table columns?
- ORDER BY distance to another value
- how to fetch data from database?
- Create (and select from) a table dynamically using servicestack ormlite
- SQL to SAS ODBC connection - truncation of NVARCHAR(max) but getting ERROR: The option dbtype is not a valid output data set option
- How to handle Foreign key for optional field in .NET
- How do I query previous rows?
- SQL Search column for each variable in CSV string
- Why use AppFabric when denormalized SQL Server data seems to perform as well?
- Insert into 2 tables from a single select query using TSQL
- Two update on a single statement
- Spring-data-jpa with SQL Server - Geometry Deserialization Exception
- Editing duplicate values in a database
- T_SQL Variable Problem
- Get array of values instead of array of objects when using FOR JSON
- SQL query: how to create subtotal rows when there is no aggregate function
- SSIS variable expression from SQL Execution task incorrectly set
- How to create a table based on another 2 Tables?
- Delphi XE Unicode dealing with nchar(1) fields
- Sql Server upper lower and index logic
- How do I create user-groups in SSRS?
- Any cleaner or alternate way to write sql join script
- Replace " with "e from varchar variable in sql
- Update data in the table using XML string
- SELECT ascending ID
- Update data using SQL parameter in C# with class
- Terrible performance after SHRINK on static data
- Workaround maximum key length for a nonclustered index in SQL Server
- Grails SQLServerException Microsoft Database Incorrect syntax near 'limit'
- SQL Server : Conditional Column on Select query
- SQL Getting most recent comment in a log
- How to read stored procedure output and return it as list
- Cannot ALTER 'Table' because it is being referenced by object 'predicate' - SQL Server RLS with Temporal
- SQL transform data
- SQL Server - How to select the most recent record per user?
- Laravel execute SQL Server stored procedure with output
- querying a datetime field only using the date part of the record
- Assign multiple Variables at once as result from Select Statement
- STIntersect says every point is intersecting every zone - need to confirm
- Using Expressions to determine if string starts with a letter
- Can one turn visual studio's 'smart' sql query assistant off?
- Backup complex SQL Server 2008 Data into SQL Server Compact Edition (sqlce)
- SQL Server comma delimiter for money datatype
- Getting first and last values from contiguous ranges
- EF Code First Migrations and Foreign Key Constraints
- SQL Server Simple Group by query
- Create a query to fill the gaps in a table due to bad data
- Increment "sequence" column based on alphabetical order of another column
- Default SQL Server Port
- SQL Server 2008R2 Full outer join not working
- Adding one node to SQL XML Explicit results
- How to reset `increment` value back to 1 when data changes?
- SQL: Getting Missing Date Values and Copy Data to Those New Dates
- Avoid subquery to select records from same table based on date of base record
- SQL Where Clause with CASE & NOT IN Condition
- add/assign value to row which are null with previous row value
- Cursor in Stored Procedure Performance Issues
- Store value from SELECT statement into variable on SQL Server
- Understanding the recursive CTE termination check
- SQL Server : check if table column exists and remove rows
- sqoop import from SQL Server with windows authentication
- What credentials need to be passed when accessing an SSRS report through URL?
- Using a Cache Table in SQLServer, am I crazy?
- SQL query for parsing a body of text to extract a string from a list
- Insert all values of a table into another table in SQL
- Select 'N' rows and get number of rows using Entity Framework
- SQL Full Outer Join duplicate Issue
- Setting primary key for SQL Server view linked to Access front end
- How to delete duplicate records from SQL Server?
- Exponent error message
- How do I auto increment the primary key in a Microsoft SQL Server Management Studio database table, the datatype is declared as uniqueidentifier
- Make entity framework interpret stored procedure result set column as bool instead of int?
- .net sql exception handling - how to get the sp parameter/values
- Joining/Merging records from two non related tables based on specific column
- Procedure for Altering Table and updating keeps failing with invalid column
- Select entire row with max ID
- SQL no primary or candidate keys in the referenced table
- � IN SQL Server database
- SQL deadlock on concurrent delete
- Can we convert SQL Server database file to SQLite file or .db file?
- SELECT name of table if table is empty
- SQL count with another table in SELECT clause
- Using calculation with an an aliased column in ORDER BY
- SQL - On insert, reference ColumnA value in ColumnB
- Query returning duplicate rows
- Unordered words matching in LIKE clause T-SQL
- Check if input date falls within table of start and end dates
- Querying multiple databases on SQL Server
- Caching results of a CLR UDF which invokes a web service
- Postgresql trigger to write data into SQL Server via ODBC?
- Why must QUOTED_IDENTIFIER be on for the whole db if you have an indexed view?
- How to get results in a specific XML format from a TSQL query?
- Full Text Search Help
- Searching for postcodes with different character spacing - SQL Server
- Conditional report parameters in Reporting Services
- Return records for last 6 months if no date supplied or last 6 months from date supplied
- Why CASE statement in Where clause for MS Sql server
- why doesn't ODBC support french accented words when inserting to sql server?
- subquery on emailaddress not on customerid
- SQL Server Pivot with Dynamic Fields
- Sql Server - How compare hash of two rows in merge
- How to convert date so it joins with date dimension table
- When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?
- Link server error Invalid use of schema or catalog for OLE DB provider "SQLNCLI10" for linked server "LINK71"
- ASP.NET textboxes doesn't work in Bootstrap template
- call stored procedure dynamically from another procedure while passing values
- Pivot TSQL table with dynamic columns
- select a value where it doesnt exist in another table in SQL Server?
- Is this a valid identifier for a sql table column?
- Increment integer MSSQL
- Changing char(8) to char(32) in SQL Server
- Multi Level Marketing Select and Insert Commision in one Query MS SQL
- SQL - Using a Trigger to occasionally allow row deletions
- Duplicate record count for NULL fields - is there a better way?
- Why can't I compare these hex values?
- Query works without parameters but not with parameters
- How to Substitute a String if record is NULL in T-SQL
- Count(*) in a subquery