I was under the assumption that NON EMPTY clause must be avoided whenever possible. So, I was in for a shock when I accidentally found that it actually made the query much faster!
Sample this:
select
[Measures].[Count Of Requests] on 0,
([Client].[Client Number].children , [Date].[Year].children) on 1
from [MyCube]
--19 seconds on a hot cache
select
[Measures].[Count Of Requests] on 0,
non empty ([Client].[Client Number].children , [Date].[Year].children) on 1
from [MyCube]
--5 seconds on a cold cache(Consistently)
Isn't NON EMPTY recursive? Is it because of local cache size?
I was under the impression thatNON EMPTY is applied at the very end of the script's process. See previous question/answer here:
Logical order an MDX query is processed
So effectively everything is returned and then before rendering results to grid or client application the NON EMPTY instruction means that null tuples on either rows or columns are discarded.
If your first script is returning a lot of data then does it take the extra time for the render process to complete?
Another interesting article re. NON EMPTY is here:
http://www.bidn.com/blogs/DustinRyan/bidn-blog/2996/non-empty-vs-nonempty-to-the-death
