SQL SERVER – Optimization Rules of Thumb – Best Practices – Reader’s Article

This article has been written by blog reader and SQL Server Expert Praveen Barath in response to my previous article SQL SERVER – Optimization Rules of Thumb – Best Practices.

Well Query Optimizations rules are not limited.
It depends on business needs as well,

For example we always suggest to have a relationship between tables but if they are heavily used for Update insert delete, I personally don’t recommended coz it will effect performance as I mentioned it all depends on Business needs;

Here are few more tips I hope will help you to understand.

One: only “tune” SQL after code is confirmed as working correctly.
(use top (sqlServer) and LIMIT to limit the number of results where appropriate,
SELECT top 10 jim,sue,avril FROM dbo.names )

Two: ensure repeated SQL statements are written absolutely identically to facilate efficient reuse: re-parsing can often be avoided for each subsequent use.

Three: code the query as simply as possible i.e. no unnecessary columns are selected, no unnecessary GROUP BY or ORDER BY.

Four: it is the same or faster to SELECT by actual column name(s). The larger the table the more likely the savings.

Five: do not perform operations on DB objects referenced in the WHERE clause:

Six: avoid a HAVING clause in SELECT statements – it only filters selected rows after all the rows have been returned. Use HAVING only when summary operations applied to columns will be restricted by the clause. A WHERE clause may be more efficient.

Seven: when writing a sub-query (a SELECT statement within the WHERE or HAVING clause of another SQL statement):
– use a correlated (refers to at least one value from the outer query) sub-query when the return is relatively small and/or other criteria are efficient i.e. if the tables within the sub-query have efficient indexes.
– use a noncorrelated (does not refer to the outer query) sub-query when dealing with large tables from which you expect a large return (many rows) and/or if the tables within the sub-query do not have efficient indexes.
– ensure that multiple sub-queries are in the most efficient order.
– remember that rewriting a sub-query as a join can sometimes increase efficiency.

Eight: minimize the number of table lookups especially if there are sub-query SELECTs or multicolumn UPDATEs.

Nine: when doing multiple table joins consider the benefits/costs for each of EXISTS, IN, and table joins. Depending on your data one or another may be faster.
‘IN is usually the slowest’.
Note: when most of the filter criteria are in the sub-query IN may be more efficient; when most of the filter criteria are in the parent-query EXISTS may be more efficient.

Ten: where possible use EXISTS rather than DISTINCT.

Praveen Barath

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

7 thoughts on “SQL SERVER – Optimization Rules of Thumb – Best Practices – Reader’s Article

  1. Hello, this is my first comment on this excellent Blog !!!.

    One question that i’ve been asking myself lately and related with sql server query performance is:

    How can i get only the registries between a certain interval, for example in Mysql i use ‘LIMIT Start , END’ in order to limitate the returning set of values.

    How can i achive a similar behavior on SQL Server 2005 ??

    This tip can dramatically improve many aplications that i’m developing right now, thank and keep the good work.

    Like

  2. how to write query can minimize the number of table lookups especially if there are sub-query SELECTs or multicolumn UPDATEs.

    thanks,
    tong

    Like

  3. Hi,

    Some good pointers in the above to get started with – to answer previous question, exists rather than distinct:

    SELECT Column
    FROM dbo.Table1 T1
    WHERE EXISTS
    ( SELECT 1 FROM dbo.Table 2 T2 WHERE T2.Column=T1.Column )

    Instead of:

    SELECT DISTINCT Column FROM dbo.Table1

    Assuming a parent/child relationship between T1 and T2 – this doesn’t apply to all cases, of course. If you have duplication in tables which are not part of a parent/child relationship, it is likely that you need to normalize the data further!

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

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