Tips for performance tunning of Query or Store procedure
B Sahoo Posted On : 01/01/2013
About Author
Biswaranjan Sahoo have good working and teching experience in Microsoft technology.

Tips for performance tunning of Query or Store procedure

1. Do not have repeated scan on large tables, instead try to use views to cut down the number of records returned.

2. The tables in Database should be Normalized but Do not use too much of Normalization, as this could lead to reduced performance while querying these tables. In such a case, Denormalization by introducing redundant records or columns in tables to help speed up querying these tables could help.

3. Do not use too much of complicated Joins, Indexing, Order By clause in the tables. This could affect the query performance on the tables.

4. If you use JOIN, put maximum matching conditions between joined tables, it increase performance of query.

5. Stored procedures send flag values after executing every statement in it. Turn off this flag value by setting "SET NOCOUNT ON" at the very beginning of the execution of these statements inside stored procedures.

6. Do not write query to bring entire table data. Use a WHERE clause in SELECT statements to limit the returned records.

7. Use SQL Profiler Trace Wizard to monitor the query performance.

8. Use Stored Procedures for database operations, wherever possible. SQL Server compiles and validates the Store Procedures only at the time of its creation and also stores the Execution Plan in Cache when it is called for the first time. Hence, subsequent calls to the Stored Procedures will be faster as SQL Server will not have to re-compile and validate it and it can use the pre-stored Execution Plan from the Cache.

Also, do not use dynamic query or dynamic WHERE clause in the stored procedures, as this will change the encapsulated query every time you call the stored procedure. Hence, SQL Server will have to re-compile the stored procedure and re-build its Execution Plan every time you run it.

Instead, write different stored procedures and call these stored procedures inside your main stored procedure, using IF ELSE based criteria.

9. UNION ALL returns the non-matching records from the table including duplicate records, where as UNION returns only unique records by returning non-matching records and then eliminating duplicate records.

Thus, UNION performs slower than UNION ALL, as it uses DISTINCT query after returning the non-matching records from the table.

So, try to use UNION ALL wherever possible.

10. Use of DISTINCT in a query makes the query perform slower. Hence, do not use DISTINCT wherever possible.

11. Do not use SELECT * instead specify the required column names in the query.

12. In WHERE clause, use of "=" is more optimized than "< >".

13. In LIKE statements, try using preceding characters i.e., m% instead of %m. As latter is more optimized due to use of Indexing.

14. In SQL Server, Derived tables can be used to write complex queries that would need creating temp tables or views instead. Using Derived tables in query is similar to creating a temporary table and then using the temporary table in your query, but the approach is much simpler and more optimized in query performance.

15. Use Defaults on the table, wherever possible, instead of inserting same values in table over and over again.

16. Do not prefix you stored procedure names with SP_, e.g., sp_authors. SQL Server will try to search these stored procedures first in master database and then in target database. Hence, this will make your calls slower.

17. Use fully qualified names in your call to stored procedures E.g., database_name.dbo.getAuthors.

18. Define Indexes on frequently run and resource intensive queries. Define indexes on tables that do not change through DML statements much.

19. Use SOUNDEX string comparisons to escape Index.

20. Avoid the use of Cursor wherever possible, as it is very resource intensive. Instead use SET for UPDATE operations or use WHILE LOOP for SELECT operations.

21. Avoid to write sub query.

22. Replace “In” with “Exists” key word in query.

Post your Suggetion or Comment
Name :
Email Address :
Comment :