We are going to explain how to optimize SQL queries and improve query performance by using SQL query performance optimization tips and techniques. This SQL server query optimization is one of the most important parts of our System. Because without query performance optimization users are not satisfied to get the data faster.
We will discuss the best SQL server query performance optimization in this session.
1. Add missing indexes
Table indexes in the database help retrieve information faster and more efficiently. So When executing a query make sure it’s required indexes then make indexes. In the SQL server, When you execute a query please enable include the actual execution plan.
In the Below SQL query, we have to get username and clientID using the inner join clause. So we have to add where clauses to filter aggregations.
SELECT U.Username,
c.Clientid
FROM Users U
INNER JOIN dbo.Clients C
ON C.ClientId = U.ClientID
WHERE U.Userid=@UidAfter query execution Now we can see in the execution plan it informs you which columns the current SQL should be indexed, and how performance can be improved upon completion. So we can easily identify missing indexes and we will create indexes that are actually required. Before creating missing indexes we need to find out the actually efficient result provide for our system.
Now, Click on right-click to the missing index messages. And After that click on missing index details so will open a new SQL query window with actually missing indexes.
After that, we can see the NonClustered index in a query plan. Before creating an index add the name of the index
Notes: Higher Creation of indexes also impacts our system. So to be careful don’t make more than indexes
2. Multiple tables join Query
It’s not possible for all field of data comes in one table. Because we have separated the field of data according to the normalization of multiple tables. Whenever we need a record of multiple tables then we are using the join query. so they will provide records of multiple tables
Let’s see on SQL Query, In the Below SQL Query, we have added multiple join queries to get results. First of all, we have used tables using the lowest to largest records of tables. So In the above disk table we can see the user’s table total record is 1759, TrackData table total record is 49,496 and tbl_Travel table total record is 1,97,105. When we follow the smallest to the largest amount of table record to apply filters and aggregation then we get the fastest result compared to vice versa.
select distinct t.DeviceNo,
tr.TravelFrom,
tr.TravelTo,
tr.TrackDateTime
FROM users u
inner join TrackData t
on t.Uid = u.Uid
inner join tbl_Travel tr
on tr.ID = u.uID
where u.Userid='test'
So here when we used multiple tables join query then add inner join first lowest record to largest record. So we can easily compare the smallest amount of records to more than records.
So whenever we needed multiple table join query then add inner join query accordingly smallest record table to the biggest records table. So after that, we can find easily fast minimal amount of records. Petty the data retrieved, the faster the query will run. avoid applying too many filters on the client-side, make filter the data as much as possible at the server. This limits the data being sent on the wire and you’ll be able to see the results much faster.
3. With N0LOCK
If you need to run multiple SELECT queries from a large table or execute complex queries with nested looping queries, subqueries, etc. then best practice is to use a NOLOCK. If you are running a heavy query against a database. SQL server locks tables that are related to tables. That means that other users are not able to work with those tables which are already used in other queries. So whenever we need other query data so we do not get its records. We can solve this problem with NOLOCK.
Whenever executing a query then used the NOLOCK keyword after the table name so we can use those tables in other queries without locks.
4. Minimize large write operation
When writing, modifying, deleting, or importing large numbers of data may impact query performance and even block the table. So it requires updating and manipulating data, adding indexes or checking constraints to queries, processing triggers, etc. And, writing more data will increase the size of log files. Thus, large write operations may not be a big performance issue, but you should be aware of their consequences and be prepared in case of unexpected behavior.
The best practices in optimizing SQL Server performance lies in using file groups that allow you to spread your data on multiple physical disks. Thereby multiple write operations can be processed simultaneously and thus much faster.
Compression and data partitioning can optimize performance and also help minimize the cost of large write operations.
5. Create JOINs with INNER JOIN (not WHERE)
The INNER JOIN statement returns all matching rows from two or more joined tables, While the WHERE clause filters the resulting rows based on the specified condition in a query.
Let’s see how to optimize a SQL query with INNER JOIN on a particular example. We are going to retrieve data from the tables humanResources.department and humanResources.employeeDepartmentHistory where departmentIDs are the same.
Let’s, execute the SELECT statement with the INNER JOIN type:
SELECT d.DepartmentID,
d.Name,
d.GroupName
FROM HumanResources.Department d
INNER JOIN HumanResources.EmployeeDepartmentHistory edh
ON d.DepartmentID = edh.DepartmentIDSELECT d.Name,
d.GroupName,
d.DepartmentID
FROM HumanResources.Department d,
HumanResources.EmployeeDepartmentHistory edh
WHERE d.DepartmentID = edh.DepartmentIDBoth queries return the same results.
6. SELECT fields instead of SELECT *
The select statement is used for retrieving data from the database. In the case of a large database, it is not recommended to retrieve all data because this will take more time to get data. If we execute the following query, they will get all data from users’ tables, and take more time they will take much memory and CPU usage.
SELECT * From USERSInstead, you can specify the exact column to get data. If you need then they will save database resources. So the query will return the data at a lower cost.
SELECT firstname, Lastname, Email From USERS7. Avoid running queries in a loop
Coding SQL queries in a loop slows down the entire sequence. Instead of queries in a loop, you can use a bulk insert and update depending on the situation.
For example, we need to insert 1000 records, so when we will insertion them in a loop they will be executed 1000 times. So we avoid these running queries in a loop and we will use a bulk of insertions at a time.
Inefficient
for (int i = 0; i < 10; i++) {
//Insertion logic
}Efficient
INSERT INTO TBL (A,B,C) VALUES (1,2,3), (4,5,6). . . .