SQL PERFORMANCE: HOW TO IMPROVE SQL QUERY PERFORMANCE

INTRODUCTION

SQL query performance improvement is an extremely intriguing topic amongst developers and the client group. Clients dependably needs a quick reaction on their data recovery actions and developers set forth their earnest attempts to give the information in the most brief time, be that as it may, there is no clear approach to characterise what is the best performance. At some point it's easily proven wrong what is good and what is bad execution of a question however generally on the off chance that you take after prescribed procedures amid development, you can give the best inquiry reaction to clients and avoid such discussions about optimal performance.

There are multiple approaches to improve SQL query performance, which falls under different classes like re-composing the SQL query, creation and use of Indexes, management of statistics, etc.
Here are some SQL best practices to ensure query optimisation:

Avoid multiple joins

Avoid writing a SQL query using multiple joins with outer joins, cross apply, outer apply and other complex sub queries.

Eliminate cursors 

Remove cursors from query and opt for set-based query as it is more efficient than cursor-based.

Avoid using non-correlated Scalar Sub query 

Re-write query to eliminate non-correlated scalar sub query as a separate query and store output in a variable.

Avoid Multi-statement Table Valued Functions 

Avoid Multi-statement TVFs as they are costlier than inline TFVs.

Clustered and Non-Clustered index

Create Clustered and Non-clustered index in SQL. These allows SQL server to rapidly and proficiently discover rows associated with key values.

Restrict size of working data set

Analyse the tables utilised as a part of the SELECT statement to check whether you can apply filters in the WHERE clause of your statement. An example case will be when a query initially functioned well when there were just a few  thousand lines in the table. As the application grew the query slowed. The answer might be as basic as limiting the query to look  at the present month's data. When you have queries that have sub-selects, apply filters to the internal statement of the sub-selects rather than the external statements.

Eliminate unnecessary tables

Writing SQL statements is a procedure that generally takes various iterations as you write and test your SQL statements. Amid development it is conceivable that you add tables to the query that might not have any effect on the data returned by the SQL code. By deleting these unnecessary tables you decrease the degree of processing the database has to do.

Exclude prefix “sp_”

Avoid prefix “sp_” with user defined stored process name since SQL server search the user defined process in the master database before in the current session database.

UNION ALL in place of UNION 

UNION ALL does not need to make the additional stride of checking the result sets and separating just the unique values, so use UNION ALL over UNION, since it doesn't need to sort the result set.

Specify All Primary Keys and Foreign Key Relationships

Primary keys and foreign key relationships that are effectively characterised help ensure that you can compose optimal queries. One common consequence of incorrect relationships is adding DISTINCT clauses to dispose of excess data from result sets.

Examine Your Server Specifications and Performance

You should have a database maintenance plan with focus on index management and database reduction. Your database server should have required RAM to support SQL Server. SSIS ETL processes should run on an ETL or staging server if they are memory/CPU intensive for long periods of time.

Use Stored Procedures or Parameterized Queries 

The SQL server saves execution plans for stored procedures and parameterized queries under most circumstances. This allows them to be reused on later calls.

Add Schema Name before your SQL object name

To improve your SQL query performance, you are required to apply schema name followed by  “.”

 -- dbo is here used as schema name
SELECT col_1, col_2, col_3 from dbo.tblMyTable;
-- Should Avoid T-SQL statement like this
SELECT col_1, col_2, col_3 from tblMyTable;

Best Practice by Applying SET NOCOUNT ON

After applying SET NOCOUNT ON, it is quite sure to improve your SQL statement performance. SQL Server returns no of rows affected after statements SELECT, INSERT, UPDATE and DELETE statement, after applying SET NOCOUNT ON we can stop it.

CREATE PROCEDURE USP_MyFirstProcedure
AS
SET NOCOUNT ON
BEGIN
.
.
.
END

Always Apply TRY-Catch

By practising TRY-Catch, we can cut deadlocks caused by complex queries.

BEGIN TRY  
     { sql_statement | statement_block }  
END TRY  
BEGIN CATCH  
     [ { sql_statement | statement_block } ]  
END CATCH  

CONCLUSION

These are some quick and simple techniques for enhancing query performance for some long-running process. While these systems won't have any significant bearing to each issue you may experience but, they will help in a few cases.

Relevant Reading

• Saving Changes not permitted in SQL Server
• UNION VS UNION ALL IN SQL SERVER
• How to avoid duplicate records in SQL view using Entity Framework