Slow to process the new dimension


This is a great tool, but the new dimension is now very slow to process. I'm trying it on a subset of the data in development and it took 24 minutes to process 178,000 rows whereas it only took a few seconds before converting it.
Obviously the view is more complex than before, but this seems like too much of a difference and I have concerns about moving to production where we have 7.5 million rows. I've double checked indexes etc, but there are only 3 fields used in this dimension so there's not much to go wrong.
Any suggestions?
Closed Jun 13, 2009 at 3:34 AM by jburchel
Closing the issue for now but we can work offline to identify possible improvements for the query against your database and if we can find problems with the tool, I will still address it.


jburchel wrote Jun 13, 2009 at 3:33 AM

Hi Sean,

This is part of the trade-off for PC dimensions vs. natural ones, in that processing requires significantly more time with a non-PC than a PC dimension. With the PC dimension, the structure is calculated on the fly at query time based on the ID and Parent ID of the members. This is why no pre-built aggregations are possible with PC dimensions, and why they process more quickly than natural ones.

It is possible there is a suboptimal design for the naturalized query. I worked on this quite a bit and have made some big improvements since my first attempt to design it. If you execute the underlying SQL view directly, you can see how much time it consumes, and experiment to see if there are optimizations that could return the data more quickly. If we can identify any, I would definitely implement them into the tool. If you can send me your database I will try it out myself to see if I can find any way to do this using the same data. You can contact me directly through the CodePlex site to arrange that.

Hopefully we can find a good balance between processing time and query time improvements so both will be good! But it is inevitable that naturalized time will take longer than PC time at processing to some degree.


wrote Jun 13, 2009 at 3:34 AM

wrote Feb 13, 2013 at 11:17 PM

wrote May 16, 2013 at 5:06 AM