Hi,
I'm having issues with a reasonably complex MDX query used in a Reporting Services report. I have executed the query and traced activity on the SSAS box and have discovered that there is a major difference in performance between the parameterized query and the exact same one but with 'hard coded' values. The difference we are talking about is from a couple of seconds (hard coded) to minutes when parameterized!!
The key difference is that the parameterized query is causing litterally hundreds of thousands of Query Dimension events (EventSubClass : 1 - Cache Data) to occur against one of the dimensions (the Date dimension to be exact). This dimension is not even large with only a few hundred leaf level members and about five levels. There is another very large dimension in the cube however, c.1.5million members, upon which subsets of member are being ranked but this doesn't appear in the trace any more often than in the non-parameterized version of the query.
Have I missed something here or is there something I can do to improve the performance ofparameterized queries? Any suggestions or adive whatsoever woould be greatly appreciated.
Thanks,
Rob.
Hi Rob,
Can you post up the query you're using?
Chris
|||Hi Chris... below is the query. Note that I've had to change a few of the member names etc so apologies if I've introduced any syntax errors etc...
With
Member [Measures].[Current Period Count] As
(
STRTOMEMBER(@.DateMember),
[Measures].[Count]
)
Set [Current Period Documents Ordered by Count] As
TOPPERCENT(
NonEmpty(
Descendants(
STRTOMEMBER(@.DocumentMember),
[Documents].[Documents].[Document Title],
SELF
),
[Measures].[Current Period Count]
),
100,
[Measures].[Current Period Count]
)
Member [Measures].[Current Period Document Ranking] As
Rank(
([Documents].[Documents].CurrentMember),
[Current Period Documents Ordered by Count],
-[Measures].[Current Period Count]
), FORMAT_STRING="#;#;-;-"
Member [Measures].[Prev Period Count] As
(
STRTOMEMBER(@.DateMember).PrevMember,
[Measures].[Click Through Count]
), FORMAT_STRING="#;#;-;-"
Set [Prev Period Documents Ordered by Count] As
TOPPERCENT(
NonEmpty(
Descendants(
STRTOMEMBER(@.DocumentMember),
[Documents].[Documents].[Document Title],
SELF
),
[Measures].[Prev Period Count]
),
100,
[Measures].[Prev Period Count]
)
Member [Measures].[Prev Period Document Ranking] As
IIF(
[Measures].[Prev Period Count] > 0,
Rank(
[Documents].[Documents].CurrentMember,
[Prev Period Documents Ordered by Count],
-[Measures].[Prev Period Count]
),
Null
), FORMAT_STRING="#;#;-;-"
Member [Measures].[Param1] As
[Documents].[Documents].CurrentMember.Properties("Param1")
Member [Measures].[Param2] As
[Documents].[Documents].CurrentMember.Properties("Param2")
Select
{
[Measures].[Param1],
[Measures].[Param2],
[Measures].[Current Period Count],
[Measures].[Current Period Ranking],
[Measures].[Prev Period Count],
[Measures].[Prev Period Ranking]
} On 0,
TOPCOUNT(
Filter(
[Current Period Documents Ordered by Count],
[Measures].[Count] > 0
),
30
)
On 1
From
[OLAPDatabase]
Where
(
STRTOMEMBER(@.TimePeriod),
STRTOMEMBER(@.UserMember),
STRTOMEMBER(@.ProductMember)
)
|||Hi Rob,
Two ideas to start off with....
I'm not sure exactly why parameterising the query has such a performance hit, but I think it's because using parameters involves using the StrToX family of functions, and any calculated member which uses these functions is going to incur a performance hit and possibly stop caching being so effective. You've got no choice here though, unless your data source in Reporting Services from using the 'Analysis Services' provider to using the 'OLE DB' provider and not using AS parameters, but dynamically generating the entire query string with Reporting Services expressions - which is pretty unpleasant. Just out of interest though, if you remove the parameters from the calculated member definitions and leave them in place elsewhere in the query (eg in the WHERE clause), what's performance like?|||
Chris, many thanks for your reply.
Yes I've been considering dynamically building the MDX statements as a get around but as you say, it's not pleasant and certainly not elegant... however it looks as if your assumptions were correct as removing the parameters from the calculated members causes a significant improvement to performance. Even leaving the parameters in the Where clause, it's returns the result set only fractionally slower then the non-parameterized query. And by implimenting your second suggestion of replacing the calculated members with Tuples throughout the query the performance is greatly improved from what I had originally. It's still far slower than the non-parameterized version but acceptable.
Many thanks for you swift and effective advice!
Rob.
|||Glad I could help. I don't know enough about your cube or parameters to say whether this would be possible, but potentially the query could be further optimised by removing the parameterisation from the calculated measure definitions and putting it somewhere else; it depends on whether the contents of @.DateMember could be put in the WHERE clause or on a visible axis.
Chris
No comments:
Post a Comment