Hello Everyone,

I have a question about what we should do if the resultset of MDX is big
like (20000-30000) rows with 5-7 columns. The query takes avg. of 7-8 sec to
bring this much data. Can anyone say any performance improvements in such a
deal or it is just too much data the query is bringing. Will partioning the
cub help here or the design is bad becuase of which so much data is needsd to
be sent to the application.

Thanks and regards

Hi Vicky,
Its hard to say what is the slowest part of your design without knowing more
about it. Network speed will be a consideration, how long the front end
takes to render the data will also be a consideration. Do you have any
calculated members that will take time to calculate on the client machine,
this typically is the biggest bottleneck.

The usuall response with such large result sets is that a user can't consume
that many rows, so you need to look at if you really need to return that
many.

David

"vicky" <vicky (AT) discussions (DOT) microsoft.com> wrote

Hi Vicky,

If it grows by that much, how many rows are in your fact table. How many in
the dimensions? The dimensions will still play a big part in the amount of
data that comes back to the client, if your MDX queries at the leaf level of
each of the dimension the query could be bringing back a very large cell
set. Remember PTS tries to bring back more than you actually need in
members.

Partitioning will help if you are querying just a specific date or a
specific member that you will slice by. If you have to go across all
partitions then it wouldn't have much effect on the query.

Star VS Snowflake won't have much difference in query performance, just
processing performce.

Do you have member properties and do you access them during the query?
Virtual Dimensions?

Why are you trying to pull back such a large result set? What are you doing
with the query?

SQL vs OLAP - remember the performance gain of OLAP is in its
preaggregation. If there isn't any aggs to help cover the query then the
caluclations have to be calucated on the fly. Other then that there are
other things that OLAP gives such as the joins don't get in the way as far
as performance. But if you are retrieving a large result set the amount of
data being sent to the client is restricted by the network performance. What
kind of connection is it?

Have you looked at the Peformance Tuning Whitepaper by Dave Wickert?

David


"vicky" <vicky (AT) discussions (DOT) microsoft.com> wrote