Some Important Tips For SQL Query Performance

Iftikar Hussain
2y
10.9k
0
3

Article

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.