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 (https://blog.sqlauthority.com)

Best Practices, Database
Previous Post
SQL SERVER – Optimization Rules of Thumb – Best Practices
Next Post
SQL SERVER – Query Analyzer Shortcuts – Part 2

Related Posts

6 Comments. Leave new

  • Rahul Sharma
    May 5, 2008 11:53 am

    Nice Explanation

    Reply
  • 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.

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

    thanks,
    tong

    Reply
  • Kamran Shahid
    July 28, 2008 12:08 pm

    How To get distinct record by using Exists rather then Distinct
    [“where possible use EXISTS rather than DISTINCT”]

    Reply
  • Matt Chatterley
    December 23, 2008 7:38 pm

    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!

    Reply
  • I’ve Found a great MSSQL scanner that can locate any MSSQL server at your network (multi-subnet) it can also try to brute force the SA user account (or any other account) to make sure the password is not easy.
    you can get it here:

    Reply

Leave a Reply

Menu
Exit mobile version