Re: improve performance of query
- From: "Jeje" <willgart@xxxxxxxxxxx>
- Date: Sat, 9 Sep 2006 10:29:52 -0400
before changing your server identify the bottleneck.
in AS2000 by default most of the calculations are evaluated on the client
side!
So, first play with your connectionstring to force the calculation on the
server side:
execution location=3;Default Isolation Mode =1
else try these connectionstring option:
cache ratio2=0.001; cache policy=7
But verify if your formula continue to provide good results because
sometimes these changes can cause formula evaluation issues.
Monitor your server using the performance monitor. you'll found some
counters like hit cache % etc...
And specially the last query:query num
this counter display how many queries are executed against the cube.
A bad formula can result in hundreds or thousand of small queries which is
really bad!!!
(recursive queries or bad usage of the non empty behavior can cause this)
a good formula and a good MDX query should result in 1 query num only or a
small number of query num for complex formula, but hundreds... no!
so if you suffer this problem, revise your calculated members & formulas to
optimize them.
monitor the server CPU & memory usage.
also remember to use the usage based optimization wizard to add missing
aggregations.
now you'll have a better idea of what's appends on the server and/or the
client and you are ready to optimize the system.
Jerome.
"Hash" <harshal.patil@xxxxxxxx> wrote in message
news:1157790181.087380.258470@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi All
I have MSAS 2000.
My cube has 30 to 40 mdx calculated measures which are interconnected
with each other i.e one measure calls another calculated measure and
that measure may call another calculated measure.
that cost the query performance while creating reports. It becomes slow
when reports get access or make any reports.
can anybody tell me how to improved the query performance and can give
the required hardware configuration of server.
Regards
Hash
.
- References:
- improve performance of query
- From: Hash
- improve performance of query
- Prev by Date: Re: MDX problem - NonEmpty makes results go away
- Next by Date: Re: Can you update/process AS2005 cube with AMO and SQL authentication
- Previous by thread: improve performance of query
- Next by thread: Re: Can you update/process AS2005 cube with AMO and SQL authentication
- Index(es):
Relevant Pages
|
