Performance Optimization Tips For Microsoft T-SQL Queries

In any organization, the data is always growing. There is no instance when the data reduces unless they are moved and archived. Therefore it is very important to optimize the way data is retrieved. And if the data is stored in SQL Server, there are many optimization techniques that can be used to achieve this goal. These techniques are suggested and published by Microsoft from time to time and version to version. There are many DBAs who, from their own experience, suggest these techniques. In this series of articles, I will discuss some T-SQL query performance tips and tricks for SQL server programmers. Many times developers don't think about the performance while writing the SQL queries.

For optimum performance of SQL query, the thumb rule is: always return the data that is actually required. To achieve this, you have the options of using specific column list and TOP, DISTINCT, WHERE, ROWCOUNT like keywords. Let us see these options one by one.

Retrieve Specific Fields/Columns

Usually most of the developers use “*” in the returned columns list, whereas they actually need fewer columns. Sometimes this is purposely done to avoid maintaining multiple queries or stored procedures. Especially, in many software products, for many scenarios one common query or stored procedure is written to retrieve data. This common query or SP may have many JOINs to fetch the final set of data. So even if the data is required from one table, because of common query or stored procedure usage, the query with multiple JOINs is executed taking more time for execution. If performance is important, this should be avoided. The thumb rule is, retrieve only those columns that are required. For example, if <Your-Table> contains 100 columns, but you need only 2 columns, it’s better to specify those 2 columns in the columns list instead of using a “*”.


SELECT CompanyName, ContactName, ContactTitle, Address, City
FROM Customers

Is always faster than

SELECT * FROM Customers

Using SELECT * prevents the use of covered indexes, reducing the performance. Therefore, you will see better performance if you specify the column names instead of using “*” even if you need all column values.

We will see more optimization tips in next few posts. Please do not hesitate to leave your comments on my posts.

posted @ Monday, April 09, 2012 9:07 AM

Print

Advertisements at BlogUnlimited.com

Comments on this entry:

# re: Performance Optimization Tips For Microsoft T-SQL Queries

Left by Nachiketa at 5/7/2012 7:35 AM
Thanks for the useful tips.

# click for more info

Left by click for more info at 10/9/2014 9:04 AM
Performance Optimization Tips For Microsoft T-SQL Queries


Your comment:



 (will not be displayed)


 
 
 
Please add 7 and 4 and type the answer here:

 

Live Comment Preview:

 
«December»
SunMonTueWedThuFriSat
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910

Advertisements at BlogUnlimited.com