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
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.
Monday, April 09, 2012 9:07 AM