Skip Navigation LinksHome > Categories > Code from a Category

Understanding SQL Server Performance Tuning - Why DateAdd is performing too slow.



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 2251
Add Date: 10/04/2012
In this article we will Understand SQL Server Performance Tuning - Why DateAdd is performing too slow.
Writing a proper T-SQL query which doesn't have any bad impact on the server should be ideally a job of a good developer. But Many a times, the developers don't focus on this area thus resulting in an improper query structure causing a bad damage to the server resources.

A developer comes to me and says that a particular portion of the application is timing out. Her further investigation said that there is an issue with the T-SQL query involved in it. She suspected that there could be some problem with the SQL Server Indexes and asked me to investigate further.

In order to take the case forward, I asked her to send me the T-SQL query involved in it and the same is as follows:

When I studied the logic involved in the query, I noticed that there is something wrong with the date condition written inside the where clause.

The date conditions inside the WHERE clause was written as follows:

Now if you have a look, the below condition is the one which was getting repeated again and again.



Now if you clearly have a look at the select statement above, we are trying to access the maximum InsertedWhen by joining the tables named tblstatuslog and the view vewwork. The main aim was just to have a single value which needs to be compared for which there was an attemp to perform 3 iterations as shown above, each of them scanning more than 1000000 rows of data which will slow down the entire operation.
Now in order to get rid of this problem, we will need to use a temporary table and dump the data into it using the same condition as shown below:



Now instead of checking the entire set of data as written in the original code, you can just refer the insertedwhen column present in the temporary table itself. Another question which comes in mind is whether it would be an INNER Join or a LEFT OUTER Join. Let’s see the query execution plan in both the cases:

INNER JOIN:

Now from the above screen capture, we can see that there is an HASH MATCH with an operator cost of 135 and scanning 3963160 records which will slow down the entire operation.

LEFT OUTER JOIN:

Now let’s have a look at the query execution plan involved in LEFT OUTER Join.
Now as you can see from the above screen capture, there is just an Index scan happening which fetches just 1 row of the required data which is excellent. This means that the query now uses the proper index associated with it thus making the performance excellent.
Also since you are trying to fetch the maximum record from the tblstatuslog table itself therefore I would say LEFT OUTER join would be a much better candidate than the INNER JOIN.
The overall query should now be written like below:



With this change,the query now executes just within 28 seconds when it fetches the data for the period of 2 years instead of the earlier 28-39 minutes.

Happy Programming!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.