SLOW Cellputn performance

[GPC]

I have a similar issue to http://forums.olapforums.com/viewtopic.php?f=3&t=2695.

We have an allocation process, based on a view, which takes nearly 5 hours to read approx 1 million records and write a slightly larger number. "Way too slow" I thought, (our main load processes from .csv files are doing cellputn's at a rate of approx 1 million per minute (or 17,000 odd per second), so I tinkered with the logic but was not able to make any difference. At some point, however, without making any changes, the process began to run in under 2 minutes. There did not seem to be any reason for the change. I suspected at the time that the initial slow performance may have been due to the server running out of physical memory & using virtual memory. (The model is about 11Gb & the Server had 12Gb). So we added another 12Gb of memory to the server (total now = 24Gb). At some stage though, the process has reverted to its former behaviour and is currently taking nearly 5 hours again.

For testing purposes I've set it to process 10,000 records only. This currently takes 2 minutes 45 seconds.
If I comment out the cellputn & replace it with asciioutput it writes the 10,600 records in about 1 second, so I don't believe there is any problem with the logic.
There are no rules based on the measures being written. To completely eliminate feeder issues I deleted the .rux file and ran the test but it made no difference.

Other things I've tried are;
- Changing the ViewConsolidationOptimization parameter (& restarting the Service)
- Changing the ProgressMessage parameter (& restarting the Service)
- Upgrading to 9.4.1 FP3
- removing the while loop (It seems in http://forums.olapforums.com/viewtopic. ... 5815#p5815 that Martin had a similar issue.)

all to no avail.

I've logged the issue with IBM but any suggestions or insight while they are scratching their heads would be much appreciated.

thanks, Gregory

[John Hammond]

Its not anything as simple as logging on the cube being switched on is it?

Does the default view get calc'd at loadtime. Maybe that's changed into something more complex.

[Steve Rowe]

Has the cube been reordered at all?

[GPC]

John - sorry I should have mentioned that logging is turned off in the prolog. I've even "turned it off at the wall" (Cube->Security) to be sure.
The view is built in the Prolog - the fact that the Asciioutput is fast suggests to me that there is nothing untoward with the view.

Steve - the cube was reordered some time ago, I haven't changed it recently. The fact that the load speed went from slow to fast, then back to slow without the cube being re-ordered tends to suggest that its not a factor. I've "tested" a few changes (In the Cube Optimizer only) but they result in larger size. The measures dimension is the last in the current order.

This morning I deinstalled TM1 from the Server, rebooted, installed 9.4.1 then 9.4.103 & ran the test. No change...

There is something odd about the "Process Progress Report" - while the process is running Instead of saying "Running Data procedure of process XXX - Records Processed - xxxx" it flashes "Completing Display procedure of process", could it be that this message is bogging it down?

[GPC]

OK I've found the problem and it appears to be a bug.

It seems that if you refer to a dynamic subset in the data tab you will get the "Completing Display procedure of process" message in the "Process Progress Report" and the process (if it is doing any cellputn's) will run much slower. (Static subsets are fine).

It seems either SubsetGetSize or SubsetGetElementName will cause this behaviour. In our case because the SubsetGetElementName was inside a while loop, the time difference was 4 hours 45 minutes vs 1 minute and 20 seconds.

If someone could please validate this by putting a SubsetGetSize in the data tab of a process which does cellputns it will help eliminate the environment before I pass it on to IBM. (You'll probably want to cancel the process from TM1Top once it has gone over the usual time it takes).

Thanks,

Gregory

[lotsaram]

Hi Greg,

This is somewhat of a known issue, and when given some thought also not that surprising. (I'm unsure whether IBM would classify this as a bug or more of a watch-out though..)

When you use subset element information functions on a dynamic subset this causes the subset to be re-evaluated. Depending on the complexity of the MDX, the size of the subset and the amount of time it takes to evaluate the subset this can add significantly to processing time versus a static subset. For this reason it is always recommended to only use static subsets for TI processing. If a dynamic subset is needed best to read it to a static subset in the prolog then use the static copy for the process.

[GPC]

Hi Lotsaram,

Thankyou for your response.

the dynamic subset is a very simple Level 0 on a 16 Element dimension. The fact that the speed is fine when outputting to ascii rather than than doing a cellputn suggests to me it's a bug. I believe there is also some other environment or external factor involved too, as at one stage the speed was OK.

cheers,

Gregory

[LoadzaGrunt]

The fact that the speed is fine when outputting to ascii rather than than doing a cellputn suggests to me it's a bug

A CellPutN could force re-evaluation of dynamic subsets, whereas an AsciiOutput never would. That could explain your performance issue.

[achaves]

I ran into this a while back and IBM confirmed this is expected behavior. Any action you take that could impact the mdx results (such as a cellputn) will cause the mdx to re-evaluate. Just writing to an ASCII file wouldn't cause a re-evaluation

I have found the same issue if I try to directly create a static subset from the dynamic one on the prolog as the act of adding an element to a static subset is enough to cause the Mdx to refire.

An inelegant solution if the subset isn't too large is to read through the dynamic subset and write the elements to a string variable then parse the variable and add the elements to a static subset. Adds some overhead but not nearly as much as using the mdx subset to drive the TI.

Regards,

Andrew