#74 Lot of events produced in SSAS (optimization) Unmonitor



  • Julian Hyde

    Julian Hyde - 2012-09-18

    Email from Steve Joynt:

    Has any progress been made on the metadata performance issues when using
    the XMLA driver to connect to SSAS as referred to in this bug:

    From the post it looks like there are two possible solutions:
    1. Replace the multiple metadata calls to retrieve member info with a single
    MDX query
    2. Populate the member objects with the information we already know from the
    initial query response, and delay any metadata calls to retrieve further
    information such as "advanced" properties until they are actually requested.

    My preference is option 2, but I'm happy to follow whatever the community
    thinks is the best solution.

    I'd like to get moving on a solution for this, but I'm after some guidance on how
    to go about it.

    I haven't made any progress on this. I don't have any spare development cycles at the moment.

    Options 1 and 2 both look acceptable. Your preferred option, option 2, lazily-populated members, is a neat idea. There may be some gotchas, such as deadlocks if the missing properties (especially collections) are retrieved in an unfortunate order. And the possibility of poor performance if a significant number of the lazy members need to be populated, because this would tend to happen one-at-a-time.

    I would welcome contributions using either approach (or even a hybrid).

  • Sergio

    Sergio - 2012-09-20

    Hi sjoynt,

    thanks for your first patch.

    I was testing your changes and for my case are still insuficient.

    In the another thread (forum posts) I posted some changes I did to my olap4j driver, and I've compared results with those changes and with your changes. Well, for me it is not necessary to retrieve attribute members, onlye desired data is recovered from source, so performance is greater.

    I tested with a query that returns 18000 rows aproximately, and generated events in SSAS are too different:
    - My driver: 1055 evetns
    - Actual patch.diff: 56035 events!

    And execution time was also better with the other changes.


  • Sergio

    Sergio - 2012-09-20

    Of course, object methods to retrieve members info will fail, but performance is better.

    Maybe could be interesting to have this retrieval method as an option? I suppose that not all people will navigate over members with their methods, and more if this causes a bottleneck.

  • Steve

    Steve - 2012-10-07

    Hi Sergio,

    Thanks for the feedback. I realise that in some cases there won't be any performance improvement. One of my main goals here was to ensure there's no loss in functionality - any metadata calls that worked before should still work with this patch.

    Perhaps a better idea would be to have an option/property with a few different strategies for retrieving member data:
    - standard (retrieve all member properties when the CellSet is created)
    - lazy (retrieve extended properties when they are requested)
    - never (never retrieve extended properties - the related functions will always return null or throw an UnsupportedOperationException)
    - other?

    This way if the client application knows they are never going to need the extended properties they can turn them off completely.

    My patch is also fairly naive in that it only takes the member unique name and caption from the original query result, even if more information is returned.

  • Sergio

    Sergio - 2012-10-08

    Use of properties seems a good option, and ensure backward compatibility.

    One question, this first patch required a system property, but, would be better a driver property?, like a JDBC property? By this way users could use different connections with different behaviours.

  • Austin Chu

    Austin Chu - 2013-05-14

    Hi, I was wondering whether there is any more news on this bug. We just ran into it in our deployment of olap4j, and we had to take the linked experimental patch in order to get the run time for a query that was returning around 15,000 axis members down from 7 minutes to 10 seconds. Our queries talk to SQL Server Analysis Services 2012 and don't make use of any of the extended metadata.


Log in to post a comment.