Best 17 Easy Steps For Improve SQL Query Performance

Introduction

In this article, I will discuss how can we improve SQL query performance. Several times you have seen your SQL stored procedure or query getting too much slow. which is one of the primary reasons for your application slowness. So in this article, I will discuss a few easy SQL query optimization steps by which you can improve your SQL query performance as well as increase your application loading time.

Pre-requirements for SQL query optimization analysis

Before going to SQL query optimization here I will create a table for a better understanding of the whole process, so please go through the below code which I have used for creating a  table.

CREATE TABLE [dbo].[StudentInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Section] [char](1) NULL,
[IsActive] [bit] NULL,
CONSTRAINT [PK_StudentInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Now Inserting some records to the column using the below Query

insert into [StudentInfo] (Name, Section, IsActive) values ('James','A','True')
insert into [StudentInfo] (Name, Section, IsActive) values ('Maria','A','True')
insert into [StudentInfo] (Name, Section, IsActive) values ('Andrew','A','True')
insert into [StudentInfo] (Name, Section, IsActive) values ('Bryson','A','True')
insert into [StudentInfo] (Name, Section, IsActive) values ('James','A','True')
insert into [StudentInfo] (Name, Section, IsActive) values ('Kayden','A','True')
insert into [StudentInfo] (Name, Section, IsActive) values ('Matthew','A','True')
insert into [StudentInfo] (Name, Section, IsActive) values ('Braxton','A','True')

Now the table looks like below

So in the below, I have created a stored procedure for fetching/retrieving the data from the table.

CREATE PROCEDURE Getstudentinfo 
AS 
  BEGIN 
      SELECT * 
      FROM   studentinfo 
  END 

The above stored-procedure looks like a normal stored-procedure. Now the table has 8 records. let’s assume if the table has thousands of records, during that time the above procedure will take minutes of time. So we will follow some SQL query optimization by which we will able to fetch the records within seconds or milliseconds. So in the below section, I will discuss how to improve SQL query performance.

Improve SQL query performance

For improving the SQL performance we will use the below steps. We will use the following SQL query optimization steps in our end to end SQL applications by which we can improve SQL query performance.

EXISTS:

Always use EXISTS instead of IN when you are checking existing data. Because when you will use EXISTS on your query, it will collect the information which is an exit in the table but in the case of IN it will collect all the information from the table then it will apply the condition.

Example:

SELECT NAME 
FROM   studentinfo 
WHERE  EXISTS (SELECT 1 
               FROM   studentinfo 
               WHERE  section = 'A') 

Datatype Format:

Always use an appropriate datatype format along with the required number of bytes instead of using blindly. INDEX is one of the reasons for maintaining proper datatype and bytes. The limitation of the index is 900 bytes, if blindly you will use the Datatype and bytes then you will get a hurdle during INDEX.

Suppose during the above table creation I have used char[1] for the section column. We know the section column needs only 1 byte, if we will use any other Datatype or we will use more bytes on that char datatype then it is just a waste of memory nothing else.

Avoid *:

Always use the appropriate column name instead of using * in the query. When we are using * then it will go to the table and it will retrieve all the column names from the table, so always avoid *. Never use count(*) or count(1). Instead of this use count(col_name). We can write the select query like the below format for better SQL query optimization.

select NAME,Section from StudentInfo    

Use Schema Name:

Always use schema name while dealing with any SQL Table. When we are using the Schema name with the table it directly heats the table instead of scanning the whole server. Follow the below example for learning how to use the schema name.

Example:

select NAME,Section from [Practice_Dummy].[dbo].StudentInfo

Alias Name

Always try to use an Alias name. Basically, during the SQL join timing, we use Table_Name.ColumnName. Never use full table name with the column name always create an alias name for the table name. Use that alias name with the column name which will help you to improve SQL query performance

Example:

select SI.NAME,SI.Section from StudentInfo SI

Avoid nchar and nvarchar

Always try to avoid using the above two datatypes, try to use char and varchar if possible since nchar and nvarchar take double memory as char and varchar.

Avoid NULL

Always try to use NOT NULL, if possible in fixed-length variables. If we have a fixed variable Varchar(20) when we are using NULL at that time it will take 20 bytes for the NULL.

In the above table, I have used varchar(100) for the Name field so if I am putting NULL then it will take 100 bytes. Suppose I am using any name value in that field then it will take space as per the name’s value size.

Avoid HAVING clause:

Basically Having clause is used when we have further wish to filter the result set of aggregations. So always try to avoid the HAVING clause if possible

INDEX.

Always create an INDEX. Before creating, always check on which field to create clustered and non clustered INDEX. Always limit the non clustered index to less than 900 bytes. Always drop unused indexes. Always try to create an INDEX on integer values.

SQL JOIN

While we are fetching/retrieving from one or more tables at that time we use SQL join or Subqueries. SQL join is faster than the subqueries because it works on tables that have indexed. SQL Join is faster but on some complex queries, we have to use Subqueries. SQL join reduces redundancy of records. So try to use SQL Join.

Cursor

Always avoid the cursor if possible, instead of the cursor always use UDF or WHILE loop.

NO LOCK

Always use NO LOCK in your select statements, coz it will prevent the deadlock from happening by other statements.

Example:

select NAME,Section from StudentInfo WITH NOLOCK

NO COUNT

Always use “SET NO COUNT ON” on your SQL statements, if we will use “NO COUNT ON” then on the message section it will show the messages command completed successfully otherwise it will count how many values affected during our SQL operation and that will return.

Example:

SET NOCOUNT ON
select NAME,Section from StudentInfo

Using SET NO COUNT ON

Not Using SET NO COUNT ON

TABLE VARIABLE, CTE, and TEMP TABLE:

If you have not a large amount of data or you want to use a declared temporary value for the whole session then always use a TABLE VARIABLE. If you have a large amount of data or you want to use a declared temporary value for the whole session then always use TEMP TABLE. If you do not want to use the declared temporary value for the whole session you just want to use it after the next statement of the declared temp variable then only use CTE.

Avoid Prefix words for Procedure, table, and function

Most of the time some developers add prefix values like tbl, sp, and  UDF for the table, Procedure, and function respectively. Never use these prefix values. During scanning, it will first scan all the Procedure, table, and function instead of your input values because of your prefix name.

The actual time and Execution time check:

Always check the actual time and execution time of the SQL queries. This is the best practice for SQL query optimization, It will show some missing indexes with the improvement tips for your query.

SUMMARY

In this article, I discussed SQL query optimization techniques. Here, I discussed the proper use of SQL join, temporary variables, naming convention details, and many more. Go through the above SQL query optimization techniques before writing your next SQL queries. If you will follow the above tips, your SQL query will faster which will help you load your application faster. I hope the above tips and tricks will help you to improve SQL query performance. If you found this article is help full, please provide your feedback in the comment section, like my page, and share this with your developer’s community along with your colleagues for knowing them how to improve SQL query performance. Happy to help you😊.