Slow down of processing performance AS2K

Slow down of processing performance AS2K

Post by Daan van Bee » Wed, 29 Aug 2001 18:20:45



Our situation:

2 CPU 700 mhz machine
4 GB memory
SQL and AS on same machine (SQL2000)
NT 4.0 sp6a
fibre connection to EMC disk array
Facttable size: 31.000.000 rows
Base measures: 20
Dimensions: 40

HOLAP

We did proces the cube without aggregations because with the aggregation wizard there are many aggregations created which make no sense. The cube processed very fast because no aggregations had to be processed. After that we query the cube with an OLAP reporting tool on one measure and one dimension (month). This takes 15 minutes to retrieve the data which was not a very bad performance. After that we run the usage based optimization wizard and made one aggregation based on the previous query that was sent to the server. In the beginning the fact data was quit fast read from the database and stored as MOLAP segment (1 million rows an hour). But after a while the performance slows down to 100.000 rows an hour or less.

Did anyone experience the same problems (at the beginning it performs quit fast but later on it slows down).

Thanks in advance.

Daan van Beek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Slow down of processing performance AS2K

Post by Ion G » Sun, 02 Sep 2001 15:43:00


On OLAP designer right click the server and choose propeties. On the second
tab (or the third I don't remeber exactly which) you have to set two kinds
of memory sizes. One is the minimum memory size for the OLAP services and
the second is the memory size from where the OLAP reduce the usage of memory
and grow the usage of disk access. The second size must be less or equal
than the first size. You can increase the first size and than the second.
The defaults are one half of the machine memory.
If it does not work increase the machine memory.

Don't forget, when you choose a new aggregation, the server must calculate
all the combinations between the dimensions and for all the measures. That
can take much longer than a query with a specified combination of dimensions
and one measure.

My best
Ion G.,MCSD
Our situation:

2 CPU 700 mhz machine
4 GB memory
SQL and AS on same machine (SQL2000)
NT 4.0 sp6a
fibre connection to EMC disk array
Facttable size: 31.000.000 rows
Base measures: 20
Dimensions: 40

HOLAP

We did proces the cube without aggregations because with the aggregation
wizard there are many aggregations created which make no sense. The cube
processed very fast because no aggregations had to be processed. After that
we query the cube with an OLAP reporting tool on one measure and one
dimension (month). This takes 15 minutes to retrieve the data which was not
a very bad performance. After that we run the usage based optimization
wizard and made one aggregation based on the previous query that was sent to
the server. In the beginning the fact data was quit fast read from the
database and stored as MOLAP segment (1 million rows an hour). But after a
while the performance slows down to 100.000 rows an hour or less.

Did anyone experience the same problems (at the beginning it performs quit
fast but later on it slows down).

Thanks in advance.

Daan van Beek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!