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
- SQL Server Indexes
- How to create a SQL Command with unknown number of fields?
- DATEADD and BETWEEN not working
- Conversion failed when converting the nvarchar value '%' to data type int
- how do I add just MMYY using SQL script?
- Modelling and querying multiple fact tables
- Avoiding Deadlocks within SQL transaction
- Are SQL Server encryption functions deterministic?
- Using uniqueidentifier with IN Clause in SQL server
- How To Declare Input-Output Parameters In SQL Server Stored Procedure/Function?
- How to define SQL Server colum name case insensitive but values case sensitive
- case statement in where clause condition in sql
- Get Time difference between group of records
- SQL - Convert few dates records to single date range
- Sql Server 2008 - Convert localized DateTime column to UTC
- Executing SQL troubles
- Documentation Template for SSAS Cube
- Restore SQL Server database with different name, but let server handle filenames
- Connecting and creating an android application to retrieve data from MSSQL
- Cannot insert to database through console app
- Do Foreign Key constraints get checked on an SQL update statement that doesn't update the columns with the Constraint?
- The system database file master.mdf already exists in C:\
- What are the SQL Server requirements for Team Foundation Server And Reporting Services?
- Run a sql script from a bat file and divide the result set in two csv files based on a flag
- Inserting Unicode string without prefix ' N....'
- compare two tables on SQL server with result to show which column from its associated table
- sql server 2005 connecting thru windows authentication
- using same select multiple times vs creating temp table
- Comma delimited record in a table (Split/Merge) on SQL server 2005
- Convert web-based CMS to support utf-8
- How to convert Datetime to Date and use in Where clause?
- When there is a 6 month Gap Grab everything after if there is no 6 month gap then grab everything sql
- SQL Server Pivot Table with multiple column aggregates
- SSMS Color tab based on connection string / Environment
- How to bind ISO8601 TSQL DATETIME parameter with PDO?
- IF EXISTS (SELECT 1...) vs IF EXITS (SELECT TOP 1 1...)
- Selecting count of consecutives dates before and after a specified date based on start/end
- The proper way to insert a NULL into a database with CodeIgniter
- SQL Server Agent - only email if returned row is greater than 0
- How to compare dates with different precision
- Please help me understand SQL quarter logic
- Expression to find multiple spaces in string
- What is most effective and easy way to batch INSERT query into SQL 2005/8
- What is the return value after a successful INSERT using sqlsrv_query() in PHP, MSSQL SERVER 2005?
- How optimal is this function
- t-sql unique constraints over 7 columns by sql management studio
- SQL : Get range date from multiple begin and end date
- What are the strategies to achieve performance for both reads and writes to Sql Server?
- SQL Server query performance on clustered index with composite fields
- Second level Top N SQL server
- Calculating Percentage the best way alongside a count
- Print Prime Numbers with SQL query
- Why does identical code for SQL Merge (Upsert) work in Microsoft SQL Server console but doesn't work in Python?
- Since there is no Sqlserver array parameter, what's the best way to proceed?
- SQL not exists comparing backup
- SQL Server 2008 VarChar(Max) returns only 8000 Characters. Why?
- How do I determine if a column is in the primary key of its table? (SQL Server)
- Foreign key with constant in Fluent API
- Transaction Log in Sql Server
- Auto Increment Reseed when another ID has been changed on Insert Query
- Showing image in a SSRS report using BIDS for CRM 2011
- Is it possible to use CONTAINSTABLE to search for "word1" in column1 AND "word2" in column2
- How to group by and calculate sum and select where is less than given amount in SQL Server / C# Linq
- Pivot Columns to Rows in SQL Server
- How to nest xml through sql query
- SQL Server : select last record for each employee
- Convert Cursor to T-SQL
- Multiple Results with One Query
- How to organize Entity Framework Core migrations (code first) with 2 different databases schema in the same project?
- Send message from SQL Server trigger
- XML Namespace confusion in SQL
- Error from NOLOCK on CTE in SQL Server 2008 SP2
- Dapper Multiple Query Read is slow
- Accounting System for Winforms / SQL Server applications
- Pulling results from two tables if first table has no matches
- merging 2 queries into one query
- BizTalk 2013 Start Message Processing Before Source File Finishes?
- Random char in tsql string
- C# and SQL LIKE condition: '%' works as single-character wildcard
- Create new column with SQL
- Is it really faster to select by position than by name in SQL Server?
- SQL Updatable View with joined tables
- Force multithreading in select query? [Oracle or Sql-Server]
- Adding Customer Specific Record in Master Table
- How to properly access a VARCHAR(MAX) parameter value of a FireDAC dataset when getting "data too large for variable" error?
- How to define self-defined log in SQL Server
- sql collation sorting of nonunicode data for western European languages
- SQL Server - Use substring in where clause
- Automatic Age Update in SqlServer
- Execute MSSQL stored procedure in Mule CE 3.5 (Anypoint)
- Amazon RDS (Relational Data Store) and SSIS
- How to create a deterministic uniqueidentifier (GUID) from an integer value
- select the data from any of the four tables
- All hour of day
- how to pass multiple unique identifiers to stored procedure
- Left function in sql server
- MSSQL - Grant Select rights to all tables except one
- Query to fetch specific part from a filepath
- SQL Server high CPU and I/O activity database tuning
- stuff funct returns duplicate records when used with inner join
- Cannot query ORACLE database through linked server
- SQL query faster on slower machine
- Adding 30 days, 60 days and 90 days column for payments made during that time in SQLServer
- How to run a 2008 SSIS package from a win froms app on a user machine?
- How do I force my T-SQL query to output unmatched values as zero?
- SQL Server OLAP CUBES (Tutorials)
- Conditionally insert a row if it does not exist already
- SQL XML parsing using a attribute value supplied by another field in the same row
- T-SQL equivalent to MySQL aggragate from other table
- SQL REPLACE special characters with value from another table
- Connection.close() does not closes the connection, it make it sleep forever?
- SSIS - Stored Procedure Has An Input Parameter That Runs Different Queries In The Procedure
- How To Get the Sum of a Column within a given Date range in a Table and Update a particular Cell in The Table with The Sum in TSQL?
- HOWTO: Interpret the MSSQL CDC "__$update_mask" field
- SQL Server 2008 big INSERT still slow after wrapped in transaction
- Remove extra spacing, line breaks, tabs from field
- How do I return a boolean value from a query during condition evaluation?
- SQL SERVER - Rank/Row Number through months
- MSSQL Import/Export/Copy IDENTITY_INSERT problems
- How do I select for each row from a list of elements in an XML column?
- Re-Sequence a set of data in SQL Server
- SQL Script to know join relationship between FACT & Dimension Table
- Calculate Running total in a new column based Adding or Subtracting condition using SQL
- Store Stored Procedure return value using JPA
- DELETE performance in SQL Server on clustered index, large table
- SQL, join table on itself, order by created within a certain timespan
- Why is query with phone = N'1234' slower than phone = '1234'?
- How to remove a specific character from a string, only when it is the first or last character in the string.
- SQL Query to combine rows from two tables
- Select Only Characters after First Character in String
- Problem with the joining of SQL tables via relationships
- Invoice database design
- Why is READ_COMMITTED_SNAPSHOT not on by default?
- rolling less than 13 months in sql
- Statistical analysis for performance measurement on usage of bigger datatype wherever they were not required at all
- Dynamic Pivot Function - SQL
- Rollback SQL Server 2012 Sequence
- SQL Server 2005 Linked Server Query Does Not Return Expected Error
- How to convert mm/dd/yyyy militarytime -> mm/dd/yyyy hh:mm:ss AM/PM
- Query to run max and min
- Join three tables on two columns with blanks where they don't match
- Trying to get value of stored procedure - Error: "does not contain a definition"
- Join 2 tables by matching children
- MySQL Migration error: Invalid timestamp literal detected
- SQL 2005 cannot delete user
- Get the aggregated result of a GROUP BY for each value on WHERE clause in TSQL
- Grouping types of bugs by date and priority
- "SQL_Latin1_General_CP1_CI_AS" in the equal to operation
- How to transpose table data into a matrix form
- Is it possible to calculate the total sum within partition and cumulative sum between partitions?