I see a lot of customer MDX queries where comments are used to help explain complex logic inside an MDX query, or to remove a block that is not needed.
Please avoid using the single line style of comment as below:
SELECT -- Reseller Sales Amount by Quarter
[Measures].[Reseller Sales Amount] ON 0
,[Date].[Calendar].[Calendar Quarter].Members ON 1
FROM [Adventure Works];
This causes havoc for diagnostics and load testing as when we capture a trace, the entire query is concatenated onto one line as below:
SELECT -- Reseller Sales Amount by Quarter [Measures].[Reseller Sales Amount] ON 0 ,[Date].[Calendar].[Calendar Quarter].Members ON 1 FROM [Adventure Works];
A better approach is to use the start and end comment block approach, so that when the query is reduced to one line it is still valid syntax.
SELECT /*Reseller Sales Amount by Quarter*/ [Measures].[Reseller Sales Amount] ON 0 ,[Date].[Calendar].[Calendar Quarter].Members ON 1 FROM [Adventure Works];
We do have techniques to clean the comments out of the MDX, but the best way I have found so far is to insert CRLF’s before know heuristics that comments might come before (very messy).
As an aside, if you also code in TSQL on the DB Engine platform, commenting queries is also an even bigger no no. This is because the hash of the entire query “including comments” is used to identify similar query plans. If you have the same queries with different comments, then the query processor will have to make up multiple plans for them!
So lets recap:
- Comments in compiled code =good.
- Comments in the middle of query statements=handle with care or avoid completely
