Sql Query Optimization Tips

As a sql developer, we often get task of sql query optimization, here we see some easy way to figure out what causing slow performance and how we can optimize our sql query for better performance.

You may look at my earlier post about Sql database performance tuning - Part1.

Normally we face performance issue with large query, when it takes long time to produce result, below is a quick example (though this query is not very long).

Select * from vwGoogleLinks
where  durl not in (select url from [dbo].[tbDisavo])
and url not in (select url from [dbo].[tbApprovedUrl])

Before we use any tool or technique to figure out where the problem is, we should check if the sql query is written as per standard guideline, and check each sql statement executing perfectly.

In above example, we can execute the query thrice separately to see which part causing the slow result, we also can see that in above query “where not in” clause is used twice, which is not good practice, we have used join instead.

SQL Query Execution Plan

now in real-time situation, most of the query that we have performance issue, will be longer and complicated, and to figure out exactly what’s going wrong, will be difficult without using some tools.

So to figure out what’s going wrong in big sql query, write the query in analyzer then select the “Display Estimated Execution Plan” from query menu.

As you can see in above picture, each block says about the percent of execution time, target the long one and check the details.

Now keep your cursor on each block to see the details like box below, and check each execution details, it also shows the query at the end, that will help to pinpoint the piece of query that taking long execution time.

• Never write “select * from”, instead define each filed name
• Avoid using “where in”, instead write join, query works much faster.
• Use page size or limit in case of large result set
• Avoid using DISTINCT or Having clause in case of large query

Even after following all above basic rules of writing sql query, when we end up creating a big sql statement that causes slow performance, that become very difficult to dig down and find where the exact problem is! That’s where we need to use some tools.

Now every time we optimise a sql statement, we need to check the performance to see if the query is executing faster than earlier, sometimes we need to compare with earlier execution times.

Just turn on STATISTICS, that will allow to see how many logical and physical reads are done while executing the query. we also can turn it off by executing SET STATISTICS IO on; .

SET STATISTICS IO ON; 

For example, if execute the following sql statement after setting STATISTICS on, we see additional details of all reads as given below.

Select * from vwGoogleLinks
where  durl not in (select url from [dbo].[tbDisavo])
and url not in (select url from [dbo].[tbApprovedUrl])

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbGoogleLinks'. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbDisavo'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 19, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbApprovedUrl'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Logical read: how many reads are made from the buffer cache.

Physical read: how much data read was done from a storage device, which was not yet present in memory.

You may be interested to read following posts:

• SQL Server Performance Tuning Tips