2

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?

SouravA
  • 5,147
  • 2
  • 24
  • 49
  • Hi sourav - I've added my "answer" which isn't a definite answer. +1 for the question as I'm interested to know the reason for this behaviour. – whytheq Nov 25 '14 at 10:25
  • Do you use the `NON EMPTY` axis expression, or the `NonEmpty()` function? From your question, I am not sure which one you are using. – FrankPl Nov 25 '14 at 10:49
  • @FrankPl - I am not talking about the function. `NON EMPTY` expression is that one I had used. – SouravA Nov 25 '14 at 11:15

1 Answers1

0

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

Community
  • 1
  • 1
whytheq
  • 34,466
  • 65
  • 172
  • 267