Performance problems with Nested SCOPE statements

I'm hoping someone has run across this particular issue and/or has identified some acceptable alternatives:

In using some scope statements to alter the behavior for certain measures, I've determined that when scope statements are nested, they will impact performance dramtically. For example

Scope [Measures].[myMeasure];
Scope Leaves( [Periods] ); -- Approx 100 members
Scope Leaves( [Markets] ); -- Approx 1500 members
This = { some simple calculation }
End scope;
End scope;
End scope;

NB. A 4th dimension, Products has about 450,000 members

At connect time, this statement is re-evaluated. The result is a long connect time. If the scope statement is removed for either Periods of Markets, the connection is immeadiate, but of course the results are wrong. Order of the scope statements doesn't affect the outcome (the [Measure] scope must be first).

I understand the difference in scale (100 or 1500 vs 150,000) but so far, the results seem to be out of line with reasonable expectations. (< 1 second vs ~65 seconds).

NB. The techqiue, causes the calculation to be evaluated at the product level, then SUM'ed over the Periods and Markets. All levels of Product aggregation use / evaluate the same calculation.

Thoughts & suggestions?

It probably depends on what is in your {some simple calculation}. There may be an alternative approach which would be faster, but it's impossible to say without more details. I don't think its the nesting of scopes that is the issue, it's more related to the size of the sub cube over which you are performing the assignment.

The same thing could be expressed as a single scope statement, but I would not expect this to change the performance at all.

Scope (Leaves([Periods]) * Leaves([Markets]));

([Measures].[myMeasure]) = "some simple calculation";

End Scope;

|||

Thanks for your feedback.

First, you're right that changing the "form" of the scope statements doesn't change the performance characteritics. (Already tried that). Indeed, the problem seems to the the size / scope of the sub cube being accessed.

The performance characteritics is indeed linked to the complexity of the calculation. For example, if I use this = 1; (the simplest possible expression), the connect returns immeadiately.

In this case, my target expression is:

this = Iif( [myMeasure], 1, null)

Essentually, this "resets" the expression to 1, for every level of the product attributes / hierarchies, then aggregates the result within stores and periods.

My basic problem is how to stop this calculation from occuring "during the connect" and instead have it invoked / calculated when the measure is actually used (when I'm willing to take the performance hit). It's easy to see / test, since in VS, I simply browse the cube and reconnect and watch Task Manager on the server. (It jumps to 100% for 60 seconds or so).

NB. The server in question is a x64, 4 way, dual core Opteron (2.6 Ghz) with 20 Gig of memory, running Enterprise edition (SP2). Everything is running 64 bit code.

|||

Found the answer, so I thought I'd post it for other people who might trip across this issue:

The problem actually exists in the front end "tool", not in Analysis Services. In this case, I was testing / debugging with Visual Studio, making small modifications to the calculation, then quickly browsing.

Turns out VS issues the following statement shortly after connecting:

select filter([Measures].members, [Measures].currentmember.member_caption = [Measures].currentmember.member_caption) on columns from [myCube]

This statement causes all of the facts to be referenced / resolved, including the measure in question. Therefore, a full bottom up calculation is performed. The query only ends after 60 seconds, because VS uses a 60 second timeout. (No wonder all of my tests came out the same!)

I can only speculate, that the reason VS does this, is to "warm" the cache, to make the subsequent queries faster. Results (and error messages) are discarded.

NB. For the record, this particular expression isn't intended to be used at the "higher levels". But, since I've isolated the key performance issue, at least alternatives can no be developed.

|||Glad you found the issue. I tend to test more complicated stuff with queries in SSMS, rather than using the browser, so I have not hit this issue myself. The OWC browser in VS does not really have a reputation for sending the "best" MDX.

Performance problem -- Execute stored procedure

Hello,
I have a problem on running one particular stored procedure. It takes
less than 1 second when I run this stored procedure using SQL query
analyzer. However, when I run the same stored procedure using
Reporting Services, it takes 3,4 minutes to execute. This stored
procedure returns 52 rows with 16 fields. Does anyone know why and
how to solve this problem?
Thanks!I have never seen this myself but have heard of it before. For whatever
reason the query plan is messed up for that stored procedure when executing
it from RS. Try one of the below (I would start off with the With Recompile
as a test of whether this is the problem).
Forcing a Stored Procedure to Recompile
SQL Server provides three ways to force a stored procedure to recompile:
a.. The sp_recompile system stored procedure forces a recompile of a
stored procedure the next time it is run.
b.. Creating a stored procedure that specifies the WITH RECOMPILE option
in its definition indicates that SQL Server does not cache a plan for this
stored procedure; the stored procedure is recompiled each time it is
executed. Use the WITH RECOMPILE option when stored procedures take
parameters whose values differ widely between executions of the stored
procedure, resulting in different execution plans to be created each time.
Use of this option is uncommon and causes the stored procedure to execute
more slowly, because the stored procedure must be recompiled each time it is
executed.
If you only want individual queries inside the stored procedure to be
recompiled, rather than the entire stored procedure, specify the RECOMPILE
query hint inside each query you want recompiled. This behavior mimics SQL
Server's statement-level recompilation behavior noted above, but in addition
to using the stored procedure's current parameter values, the RECOMPILE
query hint also uses the values of any local variables inside the stored
procedure when compiling the statement. Use this option when atypical or
temporary values are used in only a subset of queries belonging to the
stored procedure. For more information, see Query Hint (Transact-SQL).
c.. You can force the stored procedure to be recompiled by specifying the
WITH RECOMPILE option when you execute the stored procedure. Use this option
only if the parameter you are supplying is atypical or if the data has
significantly changed since the stored procedure was created.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<chiekot@.taiweb.com> wrote in message
news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> Hello,
> I have a problem on running one particular stored procedure. It takes
> less than 1 second when I run this stored procedure using SQL query
> analyzer. However, when I run the same stored procedure using
> Reporting Services, it takes 3,4 minutes to execute. This stored
> procedure returns 52 rows with 16 fields. Does anyone know why and
> how to solve this problem?
> Thanks!
>|||On Oct 15, 2:37 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have never seen this myself but have heard of it before. For whatever
> reason the query plan is messed up for that stored procedure when executing
> it from RS. Try one of the below (I would start off with the With Recompile
> as a test of whether this is the problem).
> Forcing a Stored Procedure to Recompile
> SQL Server provides three ways to force a stored procedure to recompile:
> a.. The sp_recompile system stored procedure forces a recompile of a
> stored procedure the next time it is run.
> b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> in its definition indicates that SQL Server does not cache a plan for this
> stored procedure; the stored procedure is recompiled each time it is
> executed. Use the WITH RECOMPILE option when stored procedures take
> parameters whose values differ widely between executions of the stored
> procedure, resulting in different execution plans to be created each time.
> Use of this option is uncommon and causes the stored procedure to execute
> more slowly, because the stored procedure must be recompiled each time it is
> executed.
> If you only want individual queries inside the stored procedure to be
> recompiled, rather than the entire stored procedure, specify the RECOMPILE
> query hint inside each query you want recompiled. This behavior mimics SQL
> Server's statement-level recompilation behavior noted above, but in addition
> to using the stored procedure's current parameter values, the RECOMPILE
> query hint also uses the values of any local variables inside the stored
> procedure when compiling the statement. Use this option when atypical or
> temporary values are used in only a subset of queries belonging to the
> stored procedure. For more information, see Query Hint (Transact-SQL).
> c.. You can force the stored procedure to be recompiled by specifying the
> WITH RECOMPILE option when you execute the stored procedure. Use this option
> only if the parameter you are supplying is atypical or if the data has
> significantly changed since the stored procedure was created.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <chie...@.taiweb.com> wrote in message
> news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> > Hello,
> > I have a problem on running one particular stored procedure. It takes
> > less than 1 second when I run this stored procedure using SQL query
> > analyzer. However, when I run the same stored procedure using
> > Reporting Services, it takes 3,4 minutes to execute. This stored
> > procedure returns 52 rows with 16 fields. Does anyone know why and
> > how to solve this problem?
> > Thanks!
Also, to improve the performance of the stored procedure in general,
you could evaluate it with the Database Engine Tuning Advisor and
implement the suggested indexes where acceptable. Hope this helps
further.
Regards,
Enrique Martinez
Sr. Software Consultant|||Bruce and Enrique, Thank you very much for your responses. We solved
this problem. What our DBA told me is that he changed that the stored
procedure is created with SET QUOTED_IDENTIFIER to ON. I appreciate
all your suggestions.
Retards,
Chieko
On Oct 15, 6:03 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Oct 15, 2:37 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>
>
> > I have never seen this myself but have heard of it before. For whatever
> > reason the query plan is messed up for that stored procedure when executing
> > it from RS. Try one of the below (I would start off with the With Recompile
> > as a test of whether this is the problem).
> > Forcing a Stored Procedure to Recompile
> > SQL Server provides three ways to force a stored procedure to recompile:
> > a.. The sp_recompile system stored procedure forces a recompile of a
> > stored procedure the next time it is run.
> > b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> > in its definition indicates that SQL Server does not cache a plan for this
> > stored procedure; the stored procedure is recompiled each time it is
> > executed. Use the WITH RECOMPILE option when stored procedures take
> > parameters whose values differ widely between executions of the stored
> > procedure, resulting in different execution plans to be created each time.
> > Use of this option is uncommon and causes the stored procedure to execute
> > more slowly, because the stored procedure must be recompiled each time it is
> > executed.
> > If you only want individual queries inside the stored procedure to be
> > recompiled, rather than the entire stored procedure, specify the RECOMPILE
> > query hint inside each query you want recompiled. This behavior mimics SQL
> > Server's statement-level recompilation behavior noted above, but in addition
> > to using the stored procedure's current parameter values, the RECOMPILE
> > query hint also uses the values of any local variables inside the stored
> > procedure when compiling the statement. Use this option when atypical or
> > temporary values are used in only a subset of queries belonging to the
> > stored procedure. For more information, see Query Hint (Transact-SQL).
> > c.. You can force the stored procedure to be recompiled by specifying the
> > WITH RECOMPILE option when you execute the stored procedure. Use this option
> > only if the parameter you are supplying is atypical or if the data has
> > significantly changed since the stored procedure was created.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > <chie...@.taiweb.com> wrote in message
> >news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> > > Hello,
> > > I have a problem on running one particular stored procedure. It takes
> > > less than 1 second when I run this stored procedure using SQL query
> > > analyzer. However, when I run the same stored procedure using
> > > Reporting Services, it takes 3,4 minutes to execute. This stored
> > > procedure returns 52 rows with 16 fields. Does anyone know why and
> > > how to solve this problem?
> > > Thanks!
> Also, to improve the performance of the stored procedure in general,
> you could evaluate it with the Database Engine Tuning Advisor and
> implement the suggested indexes where acceptable. Hope this helps
> further.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -