Tips to improve T-SQL Performance


Avoid SELECT * statement

Instead of querying all columns by using * in select statement, give the name of columns which you required.

1. — Avoid
2. SELECT * FROM tblName
3. –Best practice
4. SELECT col1,col2,col3 FROM tblName

Use EXISTS instead of IN

Practice to use EXISTS to check existence instead of IN since EXISTS is faster than IN.
1. — Avoid
2. SELECT Name,Price FROM tblProduct
3. where ProductID IN (Select distinct ProductID from tblOrder)
4. –Best practice
5. SELECT Name,Price FROM tblProduct
6. where ProductID EXISTS (Select distinct ProductID from tblOrder)

If your WHERE clause includes an IN operator along with a list of values to be tested in the query, order the list of values so that the most frequently found ones are placed at the start of the list and the less frequently found ones are placed at the end of the list. This can speed up performance because the IN option returns true as soon as any of the values in the list produce a match. The sooner the match is made, the faster the query completes.

Avoid Having Clause

Avoid Having Clause since it acts as filter over selected rows. Having clause is required if you further wish to filter the result of an aggregations. Don’t use HAVING clause for any other purpose.

Use Table variable in place of Temp table

Practice to use Table variable in place of Temp table since Temp table resides in the TempDb database. Hence use of Temp tables required interaction with TempDb database that is a little bit time taking tasks.

Use derived table instead of temp table

Instead of using temporary tables, consider using a derived table instead. A derived table is the result of using a SELECT statement in the FROM clause of an existing SELECT statement. By using derived tables instead of temporary tables, you can reduce I/O and often boost your application’s performance.
For better performance, if you need a temporary table in your Transact-SQL code, consider using a table variable instead of creating a conventional temporary table.

Use UNION ALL in place of UNION

Practice to use UNION ALL in place of UNION since it is faster than UNION as it doesn’t sort the result set for distinguished values.

Use Scheme name before SQL objects name

Practice to use schema name before SQL object name followed by “.” since it helps the SQL Server for finding that object in a specific schema. As a result performance is best.
1. –Here dbo is schema name
2. SELECT col1,col2 from dbo.tblName
3. — Avoid
4. SELECT col1,col2 from tblName

SET NOCOUNT ON

Set NOCOUNT ON since SQL Server returns number of rows affected by SELECT, INSERT, UPDATE and DELETE statement. We can stop this by setting NOCOUNT ON like as:
1. CREATE PROCEDURE dbo.MyTestProc
2. AS
3. SET NOCOUNT ON
4. BEGIN
5. .
6. .
7. END

Avoid LOWER () & UPPER () string function

By default, some developers — especially those who have not worked with SQL Server before — routinely include code similar to this in their WHERE clauses when they make string comparisons:

SELECT column_name FROM table_name WHERE LOWER (column_name) = ‘name’

In other words, these developers are making the assumption that the data in SQL Server is case-sensitive, which it generally is not. If your SQL Server database is not configured to be case sensitive, you don’t need to use LOWER or UPPER to force the case of text to be equal for a comparison to be performed. Just leave these functions out of your code. This will speed up the performance of your query, as any use of text functions in a WHERE clause hurts performance.

However, what if your database has been configured to be case-sensitive? Should you then use the LOWER and UPPER functions to ensure that comparisons are properly compared? No. The above example is still poor coding. If you have to deal with ensuring case is consistent for proper comparisons, use the technique described below, along with appropriate indexes on the column in question:

SELECT column_name FROM table_name WHERE column_name = ‘NAME’ or column_name = ‘name’

This code will run much faster than the first example.

Avoid using NOT IN

If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options, all of which offer better performance:

• Use EXISTS or NOT EXISTS
• Use IN
• Perform a LEFT OUTER JOIN and check for a NULL condition

When you have a choice of using the IN or the EXISTS clause in your Transact-SQL, you will generally want to use the EXISTS clause, as it is usually more efficient and performs faster.
If you find that SQL Server uses a TABLE SCAN instead of an INDEX SEEK when you use an IN/OR clause as part of your WHERE clause, even when those columns are covered by an index, consider using an index hint to force the Query Optimizer to use the index.
If you use LIKE in your WHERE clause, try to use one or more leading characters in the clause, if possible. For example, use:

LIKE ‘m%’ instead of LIKE ‘%m’

If your application needs to retrieve summary data often, but you don’t want to have the overhead of calculating it on the fly every time it is needed, consider using a trigger that updates summary values after each transaction into a summary table.
When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is much more efficient. For example

SELECT task_id, task_name FROM tasks WHERE task_id in (1000, 1001, 1002, 1003, 1004)

…is much less efficient than this:

SELECT task_id, task_name FROM tasks WHERE task_id BETWEEN 1000 and 1004

Avoid using SUBSTRING()

Using the SUBSTRING function can force a table scan instead of allowing the optimizer to use an index (assuming there is one). If the substring you are searching for does not include the first character of the column you are searching for, then a table scan is performed.
Use the LIKE condition instead for better performance. Instead of doing this:

WHERE SUBSTRING(task_name,1,1) = ‘b’

Try using this instead:

WHERE task_name LIKE ‘b%’

Using AND Operator in WHERE clause

If you want to boost the performance of a query that includes an AND operator in the WHERE clause, consider the following:
• Of the search criteria in the WHERE clause, at least one of them should be based on a highly selective column that has an index.
• If at least one of the search criteria in the WHERE clause is not highly selective, consider adding indexes to all of the columns referenced in the WHERE clause.
• If none of the columns in the WHERE clause are selective enough to use an index on their own, consider creating a covering index for this query.

Use SP_EXECUTESQL instead of EXECUTE

When you need to execute a string of Transact-SQL, you should use the sp_executesql stored procedure instead of the EXECUTE statement.

Use Join instead of sub-query

If you have the choice of using a join or a sub-query to perform the same task within a query, generally the join is faster. This is not always the case, however, and you may want to test the query using both methods to determine which is faster for your particular application.

Don’t use ORDER By unless really need to

Don’t use ORDER BY in your SELECT statements unless you really need to, as it adds a lot of extra overhead. For example, perhaps it may be more efficient to sort the data at the client than at the server.

Using GROUP BY Clause

The GROUP BY clause can be used with or without an aggregate function. However, if you want optimum performance, don’t use the GROUP BY clause without an aggregate function. This is because you can accomplish the same end result by using the DISTINCT option instead, and it is faster. For example, you could write your query two different ways:

SELECT task_id FROM tasks WHERE task_id BETWEEN 10 AND 20 GROUP BY OrderID

…or:

SELECT DISTINCT task_id FROM tasks WHERE task_id BETWEEN 10 AND 20

Reference

http://www.codeproject.com/Articles/22853/SQL-Server-Performance-Tips-and-Guidelines

Hope this will help !!!

Jay Ganesh

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s