Some Important Tips For SQL Query Performane

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. By using this, the table will be not 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 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 keys 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 has been given the size of 100, generally the FirstName and LastName will be not that large.

In the same way the Age column has been defined as an int. Which 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 that we can thereby store is 255.

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

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

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