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)
