locked
serialization concern RRS feed

  • Question

  • hi all,
    there's a thread, at http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/87d668de-12fa-48a1-ab45-7b0d6c674736/
    that's similar to my problem:
    Basically, I have a very simple MDX:

    SELECT {} ON 0,
    HEAD(NONEMPTY([Org].[LocID].[LocID].ALLMEMBERS),25000)
    DIMENSION PROPERTIES MEMBER_TYPE , PARENT_UNIQUE_NAME ON 1
    FROM [XXX]

    that when traced, it only involves NONEmpty and Serialization operations, and it takes about 1.2 seconds to run  (not to display, that's a different thing-- it takes about 30 seconds or more for that!)

    My observation is, when I only ask in the MDX for the first 2000 members (vs 25000), the engine only serializes Axis0 and takes about 0.5 seconds.  If I submit the original MDX returning 25000 members, the engine serializes both axes and takes more then double the time.

    So, from what I see, there's a threshold where Serialization kicks in for both axes (depending on the amount of data involved) .... so my question is, how to expand that threshold, what memory/"AS parameter" do I need to tweek to address that?

    thx much,
    Cos


    • Edited by Cos2008 Wednesday, July 1, 2009 2:26 PM
    Wednesday, July 1, 2009 2:16 PM

Answers

  • As Chris Webb mentioned in the other thread that you reference, the serialization even starts very early in the query lifetime, but does not finish until the end of the query. SSAS does not assemble all the resuts first then serialize them. Every axis needs to be part of the serialization, there is no "threashold" for the serialising other axis. I think the fact that you don't see an event for this is possibly a separate issue. All of your data is on Axis1, if SSAS did not serialise that you would get no data.

    If you are getting 0.5 seconds for 2,000 members and then only going up to 1.2 seconds for 25,000 members (just over double the elapsed time for more than 12 times the amount of data) then I think the results are not too bad.

    You are also not specifying a measure in your NonEmpty() function, so SSAS will be using the default measure and if that is a calculated measure or has assignments on it, that is more likely to have an impact on the query performance.


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by Cos2008 Thursday, July 2, 2009 2:57 AM
    Thursday, July 2, 2009 2:45 AM

All replies

  • As Chris Webb mentioned in the other thread that you reference, the serialization even starts very early in the query lifetime, but does not finish until the end of the query. SSAS does not assemble all the resuts first then serialize them. Every axis needs to be part of the serialization, there is no "threashold" for the serialising other axis. I think the fact that you don't see an event for this is possibly a separate issue. All of your data is on Axis1, if SSAS did not serialise that you would get no data.

    If you are getting 0.5 seconds for 2,000 members and then only going up to 1.2 seconds for 25,000 members (just over double the elapsed time for more than 12 times the amount of data) then I think the results are not too bad.

    You are also not specifying a measure in your NonEmpty() function, so SSAS will be using the default measure and if that is a calculated measure or has assignments on it, that is more likely to have an impact on the query performance.


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by Cos2008 Thursday, July 2, 2009 2:57 AM
    Thursday, July 2, 2009 2:45 AM
  • thx for the feedback, Darren.  I think I  have a better grip on the issue, now.
    Thursday, July 2, 2009 2:57 AM