Some Important Tips For SQL Query Performance

Iftikar Hussain

SQL QuerySQL Query PerformanceBy using UNIONsubquery in SQLWith HAVING clause

7 Comments

Join the conversation! Your thoughts help the community grow.

Parthiban K12y ago
Gr8... Appropriate example for sql user's...
Kavi suja13y ago
Thank you.. This information helps me.....
Prasenjit Dey13y ago
Hi Iftikar Hussain, I had create a table and insert data into that table. There are 1426530 rows. So you can see here, this is a big table. So when I going to test your article, I struct in the first line, that is, when I execute select query in that table, and I see Total execution time in Client script, the time automatically decreases in every executing that query without using NOLOCK or any other extra function. For that problem I couldn't go to the next topic. So please help me out. Another important thing is, in the timer of result window the time is constant. But whenever I noticed in Client Statictics, the average time decreasing on every execution of that query
Guest User13y ago
Do NOT always use the WITH(NOLOCK) option. When giving advice like this it is very important to mention the side effects of such actions. In this case the select statement will perform dirty reads and can return incorrect data.
- Guest User13y ago
  please re-read my comment. I don't mention anything about blocking.
- Iftikar Hussain13y ago
  Hi..using WITH(NOLOCK) option will not block your table. It is nothing to do with your reading the column value incorrectly. You can test it with a table containing huge data (more than 10K records of a table). First you just run one select statement without WITH(NOLOCK) and without any condition and then in other window just run same select statement with filter condition to get you less than 5 records. You will be able to see difference.
Akshatha Salian13y ago
Gr8..Thanks Fr The Info!!

1. Always use SET NOCOUNT ON and SET NOCOUNT OFF inside a Stored Procedure. It will then not print how many records are being affected by the execution of the Stored Procedure.

CREATE PROCEDURE [dbo].[usp_AllEmployee]
AS
SET NOCOUNT ON
     ----------------------
     ----------------------
     ---------------------- 
SET NOCOUNT OFF

2. Always use the WITH(NOLOCK) option while writing a SELECT query. Using this, the table will not be locked until the execution of the statement is completed.

SELECT EmployeeID, FirstName, LastName FROM Employees WITH(NOLOCK)

3. Always use actual column names instead of (*) in a SELECT query. Using (*) will make the query slower. For example

By using Select *

The following sample uses Select *:

SELECT * FROM Employees WITH(NOLOCK)

By using Column Names

The following sample uses Column Names:

SELECT EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,Address,City,Region,PostalCode,Country,HomePhone,
Extension,Photo,Notes,ReportsTo,PhotoPath FROM Employees WITH(NOLOCK)

4. Avoid using many sub-queries. For example, write a SUB-QUERY like this:

SELECT FirstName, LastName FROM Employees WITH(NOLOCK)
WHERE (Salary,Age) = (SELECT MAX(Salary), MAX(Age)
FROM EmployeesDetails WITH(NOLOCK)) AND Dept = 'Electronics'

Instead of like this.

SELECT FirstName, LastName FROM Employees WITH(NOLOCK)

SELECT FirstName, LastName  FROM Employees WITH(NOLOCK) 
WHERE Salary = (SELECT MAX(Salary) FROM EmployeesDetails WITH(NOLOCK))
AND Age = (SELECT MAX(Age) FROM EmployeesDetails WITH(NOLOCK))
AND EmpDept = 'Electronics'

5. Use a HAVING clause appropriately. The HAVING clause filters the rows after all the rows are selected. It is just like a filter. Do not use a HAVING clause for any other purposes.

The following are examples of that.

With HAVING clause

SELECT EmployeeID,COUNT(EmployeeID) FROM Orders WITH(NOLOCK)GROUP BY EmployeeID
HAVING EmployeeID!= 2 AND EmployeeID!= 3

Without HAVING clause

SELECT EmployeeID,COUNT(EmployeeID) FROM Orders WITH(NOLOCK)
WHERE EmployeeID!= 2 AND EmployeeID!= 3
GROUP BY EmployeeID

6. Use UNION ALL instead of UNION. A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

The following are examples of that.

By using UNION

SELECT OrderID,CustomerID,EmployeeID,OrderDate FROM Orders WITH(NOLOCK) WHERE ShipVia=1
UNION
SELECT OrderID,CustomerID,EmployeeID,OrderDate FROM Orders WITH(NOLOCK) WHERE ShipVia=2

By using UNION ALL

SELECT OrderID,CustomerID,EmployeeID,OrderDate FROM Orders WITH(NOLOCK) WHERE ShipVia=1
UNION ALL
SELECT OrderID,CustomerID,EmployeeID,OrderDate FROM Orders WITH(NOLOCK) WHERE ShipVia=2

7. Sometimes, you may need to do an INNER JOIN between tables. In that case, try to put all your filter conditions before joining conditions.

The following are examples of that.

By putting filter condition in WHERE clause

SELECT A.OrderID,A.CustomerID,A.CustomerID,B.CompanyName FROM Orders A WITH(NOLOCK) INNER JOIN Customers B ON A.CustomerID=B.CustomerID
WHERE A.ShipVia=2 AND B.City='México D.F.'

By putting filter condition in INNER JOIN clause

SELECT A.OrderID,A.CustomerID,A.CustomerID,B.CompanyName FROM Orders A WITH(NOLOCK) INNER JOIN Customers B ON A.ShipVia=2 AND A.CustomerID=B.CustomerID AND
B.City='México D.F.'

8. Always create indexes on Primary Keys and Foreign Keys. This is because primary and foreign keys are frequently used to join tables. Indexes on these keys let the optimizer consider more efficient index join algorithms. If your query joins tables using other columns, it is frequently helpful to create indexes on those columns for the same reason.

9. Whenever you create a new TABLE, ensure you have enough information to design the same.

For Example

CREATE TABLE EmpInfo
(
      EmpId int identity primary key,
      FirstName varchar(100),
      LastName varchar(100),
      Age int,
      IsActive varchar(20)
)

In the preceding TABLE design, we can see FirstName and LastName have been given the size of 100. Generally, the FirstName and LastName will not be that large.

In the same way, the Age column has been defined as an int. This means it can hold values from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647), and the storage size is 4 bytes.

The IsActive column is also defined as varchar(20) to store either "YES" or "NO."

Now let's re-design the table as in the following.

CREATE TABLE EmpInfo
(
      EmpId int identity primary key,
      FirstName varchar(40),
      LastName varchar(40),
      Age tinyint,
      IsActive bit
)

Now the FirstName and LastName have been reduced in size to 40, and this is enough for storing a person's name.

The Age column has been defined as a tinyint datatype of 1 byte since the maximum age value we can store is 255.

The IsActive column has been defined as a bit, a Boolean datatype of 1-bit size. It will hold either 1 (true) or 0 (false).

The correct and optimized way to make your DB performance better.

10. Never store large binary objects in the DataBase; first, place them in the file system and add the file path in the database.