Sunday, May 26, 2013

SQL and MDX Performance Tips

Improving performance is key skill for a database developer. So, every database developer should have check few key points to improve the query.

I mentioning some of them below. Please add in comments if there are any you know.

SQL Performance Tips:

1. Include SET NOCOUNT ON statement

2. Use schema name with object name - reduces searching time of objects

3. Use IF EXISTS (SELECT 1) instead of IF EXISTS (SELECT *):

4. Use the sp_executesql stored procedure instead of the EXECUTE statement: The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code

5. Use TRY-Catch for error handling

6.  Avoid using <> as a comparison operator

7. Avoid using dynamic SQL statements if you can write T-SQL code without using them

8. Minimize the use of Nulls because they incur more complexity in queries and updates. ISNULL and COALESCE functions are helpful in dealing with NULL values

9. Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause

10. Try to use UNION to implement an "OR" operation. Better use UNION ALL if a distinguished result is not required

11. Using JOIN is better for performance then using sub queries or nested queries.

MDX Performance Tips:

1.       Use Sub cube instead of WHERE clause

2.       Use NON EMPTY where ever possible

3.       While calculating a measure, use “NULL” instead of “0”. This allows NON EMPTY to work and makes calculation faster

IIF([Measures].[B] = 0, null, [Measures].[A] / [Measures].[B] )

4.       Create a calculated SET to reuse in query

5.       Sparse sets first in cross joins before dense sets

6.       Filter a set before using it in a cross join to reduce the cube space before performing the cross join

7.       Use IS operator while comparing levels/ members

8.       Use Exists functions for filtering to enable the query execution engine to use bulk evaluation mode

9.       Avoid unnecessary calculations like checking for null in IIF condition

10.    Rewrite MDX queries containing arbitrary shapes to reduce excessive sub queries where possible