5/17/2018

Query performance approach in SQL Server - Simplest way to improve query performance - Part 1


Simplest way to tune SQL Query Part 1

If your query is running slow than you can use following ways to improve its performance. These are my real time scenarios where I got success by implementing these methods to improve performance of the query.
Method 1: Avoid “Select * from”, use column name i.e. Select Column1, Column2…:

To fetch the result suppose if you are having 10 columns in your table and you need only 5 out of 10 for your result than use all 5 columns name only separated by comma in Select statement instead of using as:
Correct Method:

Select Emp.FirstName, Emp.LastName, Emp.Salary, Dept.DeptName

FROM #Employee As Emp

LEFT JOIN #Dept As Dept  ON Emp.DeptID=Dept.DeptID
Incorrect Method:

SELECT * FROM #Employee As Emp

LEFT JOIN #Dept As Dept  ON Emp.DeptID=Dept.DeptID

Method 2: Use Key columns (Index columns) for filter and join:
If you have indexes in your tables which you are querying or joining, then try to use those columns if possible for joining condition and use in where clause to filter data, this will increase performance of your query.

Method 3: Never use Cast or convert in Where clause and in Join condition:
Suppose if you have Date filter in your table and if you are using Cast or Convert operator while filtering your data than you should avoid it. It will make your query much slower. And same for Join condition also.

Method 4: Use CTE result set for big table or expression based table to join with other table:
Select Column1, Column2,.....ColumnN

From TableA

left join TableB  on TableA.ColumnN = TableB.ColumnN     

left join TableC  on TableB.ColumnN = TableC.ColumnN

left join TableD  on TableC.ColumnN = TableD.ColumnN

Where TableA.Column1 = 0 and TableA.Date >= '01/01/2018'

As per given above query example; we have four  tables to join with each other based on conditions so first table here is a big table where we have millions records and another table also big one. But you can see in Where clause, we have filters for TableA only so If we run this query, than it will take time to give output so we can re-write in an efficient way with CTE as:

WITH TableACte

(

Select Column1, Column2,.....ColumnN

From TableA

Where TableA.Column1 = 0 and TableA.Date >= '01/01/2018'

)

Select Column1, Column2,.....ColumnN

From TableACte

left join TableB  on TableACte.ColumnN = TableB.ColumnN     

left join TableC  on TableB.ColumnN = TableC.ColumnN

left join TableD  on TableC.ColumnN = TableD.ColumnN

NOTE: In my Scenario, My query was taking 12 minutes to execute and now with this approach, it is giving result in less than 1 minute as Data is in billions.

Method 5: Avoid Case or any expression i.e. If condition in Where and Join condition
If you are using Case or If condition in Where clause or Join condition in your query than it will make your query very slow if data is huge because for each row it will calculate condition and will apply for filter in Where clause and join condition. So find alternate ways to do same or you can use sub query to build derived column.

Method 6: Try to use non-text columns for where clause and Join condition, use only Key columns where Datatype is not varchar() or nvarchar().
Always best method to use non text columns for join condition and if possible for Where clause also for filtering data because Text value will take time to get filter and join data.

Stay Tune for more methods in next part....
 

CROSS APPLY and OUTER APPLY in SQL SERVER


CROSS APPLY and OUTER APPLY in SQL SERVER.

Apply operator in SQL server introduced in version 2005. This operator gives you flexibility to join two table expressions where right table expression is processed every time row by row from the left table expression

Left table expression is evaluated first and then right table expression is evaluated against each row of the left table expression for final result-set.

There are two types of Apply operators in SQL server:

·         CROSS APPLY

·         OUTER APPLY

CROSS APPLY acts like an INNER JOIN, and OUTER APPLY acts like a LEFT OUTER JOIN.

Let’s understand functionality of Cross Apply and Outer Apply with examples:

Let’s take an example of Cross Apply first to understand it in a better way by comparison with Inner Join query. As we know CROSS APPLY operator acts same as Inner Join here in example we have two tables: Employee and Department

To join Employee Table with Department by Inner Join operator:

NOTE: At the end of this article you will find Sample query to build Employee and Department table for Practice.

SELECT *

FROM Employee As Emp

INNER JOIN Department As Dept  ON Emp.DeptID=Dept.DeptID

Cross Apply to join Employee Table with Department:

SELECT *

FROM Employee as Emp

CROSS APPLY (SELECT * FROM Department As Dept WHERE Emp.DeptID=Dept.DeptID) Dept

Now you will say these both query works same than why we need complex code of Corss Apply whenever we have Inner Join as simple so let’s take one scenario as:

Suppose if want to see Top 2 salary for each Department where Salary is in Empoloyee table and Department we have in Department Table.

With Cross Apply, by writing Simple Query we can achieve result as:

Select Dept.DeptName, Emp.Salary from #Dept as Dept

CROSS APPLY (SELECT Top 3 * FROM #Employee As Emp WHERE Emp.DeptID=Dept.DeptID) Emp

Order By Dept.DeptName,Emp.Salary

Result will be as:

DeptName
Salary
IT Analytics
19000
IT Analytics
20000
IT Analytics
35000
IT Apps
15000
IT Apps
18000
IT Apps
26000

 

Now we look at below query without CROSS APPLY, where we have to use Rank or Row Number function to achieve the same result:

SELECT Sal.DeptName,Sal.Salary FROM (

  SELECT Dept.DeptName,Emp.Salary , ROW_NUMBER() OVER (PARTITION BY Dept.DeptName ORDER BY Emp.Salary Desc) RN 

  FROM #Employee as Emp

  INNER JOIN #Dept As Dept ON Emp.DeptID=Dept.DeptID

) Sal

WHERE Sal.RN <= 3

Benefits of Using CROSS APPLY:

We can use TOP operator with CROSS APPLY where it will return TOP N rows for each matching row and no need to use partitioning for using Rank or Row number to short.

OUTER APPLY

OUTER APPLY is similar to Left Join in SQL Server as OUTER APPLY operator in SQL Server returns all rows from the LEFT table expression.

Syntax for OUTER APPLY as:

Simple Query with Left Join:

 

SELECT *

FROM #Employee As Emp

LEFT JOIN #Dept As Dept  ON Emp.DeptID=Dept.DeptID

 

Outer Apply Logic to achieve same result as Left join

 

SELECT *

FROM #Employee as Emp

OUTER APPLY (SELECT * FROM #Dept As Dept WHERE Emp.DeptID=Dept.DeptID) Dept

Drawback of Using Apply Operator:

As apply operator are process row by row so it will be slower than Join.

Sample Data query for Employee and Department Table:

Create Table #Employee

(

EmpID int,

FirstName varchar(20),

LastName varchar(30),

Gender varchar(10),

Salary int,

DeptID int,

ManagerID int

)

 

Insert into #Employee Values (11,'Sonu','Kumar','M',10000,113,16)

Insert into #Employee Values (12,'Ritu','Singh','F',15000,112,15)

Insert into #Employee Values (13,'Parul','Sharma','F',12000,113,16)

Insert into #Employee Values (14,'Sanjay','Kumar','M',18000,112,15)

Insert into #Employee Values (15,'Ajay','Singh','M',20000,114,24)

Insert into #Employee Values (16,'Vijay','Sehgal','M',26000,114,24)

Insert into #Employee Values (17,'Kishor','Kullar','M',19000,115,28)

Insert into #Employee Values (18,'Pramod','Jha','M',26000,112,15)

Insert into #Employee Values (19,'Jai','Kumar','M',15000,113,16)

Insert into #Employee Values (20,'Ram','Singh','M',12000,114,24)

Insert into #Employee Values (21,'Sunil','Verma','M',18000,113,16)

Insert into #Employee Values (22,'Maynak','singh','M',20000,115,28)

Insert into #Employee Values (23,'Prabhu','Sharma','M',26000,112,15)

Insert into #Employee Values (24,'Ankita','Kumar','F',35000,115,28)

Insert into #Employee Values (25,'Anu','Singh','F',19000,112,15)

Insert into #Employee Values (26,'Pooja','Verma','F',26000,115,28)

Insert into #Employee Values (27,'Kamal','Singh','M',31000,112,15)

Insert into #Employee Values (28,'Sunita','Rajpal','F',42000,115,28)

Insert into #Employee Values (29,'Kritika','Kalra','F',18000,114,24)

Insert into #Employee Values (30,'Shilpa','Tripathi','F',20000,114,24)

 

 

Create Table #Dept

(

DeptID int,

DeptName varchar(30)

)

 

Insert into #Dept Values (112,'IT Apps')

Insert into #Dept Values (113,'IT Testing')

Insert into #Dept Values (114,'IT Mainframe')

Insert into #Dept Values (115,'IT Analytics')

 

Select * from #Employee

 
Select * from #Dept