Query Optimization is a process of writing a SQL query to improve the database performance. When I started learning SQL, I have noticed that even small improvements reduced the query execution time. Below are the few practical tips I use whenever I write SQL queries:
Use specific columns instead of * to get all records
select EmployeeId,EmpName,city from EmployeeUse Group by clause instead of distinct if possible
select distinct Department from Employee
Below query also gives the same result but this has some query improvements.
select Department from Employee Group by Department
3.Use primary key while creating a table
Create table Employee (
id int constraint id_employee primary key,
name varchar (50)
);
4.Use top/limit to preview query results
select top 3 * from Employee order by 1 desc
5.Indexing the right columns - Adding index is always better for the select operation.
select * from Employee
create index idx_department_employee on Employee (Department)
6.When you want to create a new stored procedure, you should always create a one stored procedure started with usp_ not with sp_. if you try to create stored procedure started with sp_ it will first try to look into the master database and then it will go to your database but at the same time when you have created the store procedure started with usp_, it will go directly to your database and it will search for that stored procedure. so, using this usp_(User Stored Procedure) is a bit faster than sp_.
Bad Approach:
CREATE PROCEDURE sp_GetEmployee
AS
BEGIN
SELECT * FROM Employee;
END
Good Approach:
CREATE PROCEDURE usp_GetEmployee
AS
BEGIN
SELECT * FROM Employee;
END
7.We should always use nolock when we are querying something
we are querying this employee table and assume this table has a lot of records and the query is bit complex then in that case if your transaction got a stuck then other people who wants to query this table on the same server will not be able to do so because this table has been locked for that same reason we should always use nolock
Bad approach:
SELECT EmpName FROM EMPLOYEE WHERE EmpId=7
Good approach:
SELECT EmpName FROM EMPLOYEE WITH (NOLOCK) WHERE EmpId=7
OR
SELECT EmpName FROM EMPLOYEE (NOLOCK) WHERE EmpId=7
8.If possible, use wildcards only at the end of the phrase.
SELECT EmpName FROM EMPLOYEE WHERE DEPARTMENT='H%'
Top comments (1)
This is a great collection of foundational tips for developers new to SQL. The advice on
SELECT *and wildcard placement is a critical lesson.However, I have to add a strong senior-level caution to Tip #7 regarding
WITH (NOLOCK).