Multiple Ways to Improve SQL Query Performance

Multiple Ways to Improve SQL Query Performance

1. Re-writing SQL Query
Re-writing SQL Query by following best practices for getting the fastest response like  avoid writing a SQL query using multiple joins ( JOIN containing more than 4 tables). ( Pls refer Nested Loop http://avishkarm.blogspot.in/2014/08/what-is-nested-loops-join.html)
Try to remove cursors from the query and use set-based query.
Remove non-correlated scalar sub query as a separate query instead of part of the main query and store the output in a variable.
Avoid Multi-statement TVFs are more costly than inline TFVs.

2. Indexes
Index can magically reduce the data retrieval time and help to improve SQL query performance and give you best query response time.
Keep in mind that clustered index should be created on a unique column.
A non-clustered index is most useful if the ratio of qualifying number of rows/total number of rowsis around 5% or less, which means if the index can eliminate 95% of the rows from consideration. As a best practice, most selective columns should be placed leftmost in the key of a non-clustered index.
Dropping unused indexes can help to speed up data modifications without affecting data retrieval.

3. Statistic Creation and Updates

4.Revisit Your Schema Definitions
Availability of the right constraint(FORIGEN KEY, NOT NULL and CEHCK constraints ) on the right place always helps to improve the query performance, like FORIGEN KEY constraint helps to simplify joins by converting some outer or semi-joins to inner joins and CHECK constraint also helps a bit by removing unnecessary or redundant predicates.

Should phone numbers be normalized or de-normalized?

If you have a Person's contact record, a contact could have N-number of phone numbers, i.e. by class of number such as work, home, cell, fax, and a highly normalized model would put all phone numbers in their own table. e.g. A fax number as a PhoneNumber with PhoneType=3.
What if application requires that contacts be retrieved with phone numbers? Those numbers in the contact record could in fact be a better solution.
Which one is the phone number to be used from N-number of phone numbers?

When storing phone numbers in multiple columns of a single row you experience different issues. What if a person doesn’t have a land line anymore? Do you put cell phone in the home phone number field…it isn’t home phone. When you want to print out a report with phone number, you need to write a query with a case statement or coalesce in order to find the first populated phone number.

What do you do when you need to record multiple phones of the same type? Personally, Person might have three cell phones used for different purposes. How do we know (if we need to) which type of connectivity that number represents?

if Person A and Person B have the same phone number, should they reference the same phone number record? If not, you still have duplication of data. But if so, you have a harder problem to solve when one person’s phone number changes, but not the other.

How to store a fax number ?
A fax number should be stored as a PhoneNumber with PhoneType=3 in a separate phone table?


All of the issues with having multiple phone numbers can be resolved as business rules. Implement those business rules in stored procedures, functions, etc.If you want to restrict your objects to having three phone numbers such as work, home, and cell, then implement that design in your business objects and business rules.
If your database doesn’t force you into that business rule, then when you get a new requirement to allow for tracking another number, your database schema is not impacted; only your business layer.

Regarding the uniqueness of a phone number and Person A and Person B have the same phone number(it being shared by multiple people); normalize to the point of having a many to many relationship between different entities and phone numbers with which they may be associated.

Regarding how to store a fax number, a fax number in a FaxNumber field is far easier to understand than a PhoneNumber with PhoneType=3 in a separate phone table, so Greater understandability of data.