Hello friends, I have learn some useful tips that i fill i need to put on blog so i am sharing with you, so below is the list of Query optimization Tips.
1. Restrict the Query result. Means return the required row and column from the query, This helps in reducing network traffic.
2. User Store procedure in place of the Queries. Store procedure is compile object so next time i will use execution plan created by the sqlserver engine.
3. Avoid Cursor that will improve your performance. Cursor fetches records row by row so it will make round trips.
4. Avoid Trigger if not needed. It will execute every time when data gets updated.
5. Use Table variable in place of the Temporary table. Temporary table will create in Tempdb it’s overhead to create table. Table variable is doesn’t need to create in the memory and it’s automatically remove when transaction complete.
6. Use “where” in place of Having clause.
7. Use “SET NOCOUNT ON” in store procedure, It’s stops sending message of rowcount and reduce traffic.
8.Use Dynamic Query with this syntax.Check below link
http://sqlmca.wordpress.com/2010/11/10/use-of-dynamic-sqlquery-and-its-optimization/
9. Use indexing where you need.(use it carefully)
10. Update your statistics in regular interval. Update Statistics will rebuild your indexes and give your good performance.
11. Check your execution plan for the No table scan.
I hope that will be useful tips for you and you can use it practically.
If you like it or any query give your valuable time by writing comments.