Skip to Content
An Experience with SAP TechED’06 Session at Bangalore – BIM205  Little tweaking of famous quote “He came he saw and he conquered” will display my experience at TechED’06 Bangalore…. In my case it truly was “I went I saw and I was floored….” TechEd’06 was an event very well managed and presented a lot of learning opportunities for lesser mortal like me…Amid that entire hullabaloo I had managed to deliver my session (Thanks to SDN jury and each one of you who voted for it) – BIM 205 “Getting Through the Maze of Query Performance – a Quantitative Approach”.   I went into the session with the intention of presenting a new perspective on Query Performance and at the end of it, I myself ended up having an altogether new perspective to Query Performance.  Especially the post session discussion made my grey cell working overtime; I was compelled to think about certain obvious things which I conveniently managed to skip over all the past years.  I am still grappling for answers & would appreciate your thoughts on below mentioned points – 1.Ratios of times –   I talked about three important time components of Query Execution Database Time, OLAP Time & Front End time, is there a ratio (optimized), which is recommended for these time. For example if I say my query execution time is 100 sec & ratio of these times is 30, 30 and 40. Is that the best distribution among times?  2.Sequence of optimizations –   What is the best sequence of optimizations steps – which one I should optimize first Database, OLAP time or Front-End? My suggestion was to start with recognizing the culprit among these components & start with most time consuming part. Any more suggestions? 3.Efforts trade off – Another point I mentioned make sure that you are aware of trade off involved of any action taken in order to improve query performance, for example if you decide to create Aggregates for query performance then also take added maintenance efforts into consideration. Question is – Is there a way to quantify this trade off?   And there were many more… which I can’t recall as of now.  Before I sign off, I shall like to thank every one, who attended the session and helped me in developing a new perspective towards Query Performance.  IT WAS AN EXPERIENCE BEYOND WORDS!!!
To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Mark Yolton
    These are good questions in your blog and I’m glad you’re asking the community to help you answer them.  We have many experts out there.  Can someone offer an opinion on the questions? 

    Mark Y.

    (0) 
  2. Vikash Agrawal Post author
    And I Quote –

    “This is what I have to say about the 3 points that you have raised :

    Ratios of times: There cant be a fixed ratio, as it will be subjective to the kind of requirement. For Eg. I have a cube which stores Inventory Data on a Plant/Matreial/Calday level. In this case it has to read a lot of data from the cube. So, if i try to Calculate a Ratio of Database Read Time : Olap Time : Front End Time , it will be more Skewed towards the Data base read time, as it is this activity which takes more time. Olap Time and Front End Time would be relatively less and would be proportional to the No of Records processed. In my experience to improve the time, if i target the Database Read time, it is more beneficial ( as can be seen in the case of Bex Accelerator). If i Target improving the Front End time, There isnt much that i can gain from it(and there is not much of flexibility that i have there ). Even for that sake , Olap Time cannot be improved beyond a certain limit.”

    Thanks Jaspreet for his comments.

    Regards
    Vikash

    (0) 
  3. Dirk Herzog
    1.Ratios of times –
    I talked about three important time components of Query Execution Database Time, OLAP Time & Front End time, is there a ratio (optimized), which is recommended for these time. For example if I say my query execution time is 100 sec & ratio of these times is 30, 30 and 40. Is that the best distribution among times?
    — I only can say that 100 sec is too long for 99% of the queries. There is no best ratio because it depends on the query.
    Let’s say you have a cube with 50.000 records for one year and 50 materials. If you design a query that displays only the sum of these values the system reads 50.000 records and displays one figure, i.e. DB high, OLAP medium, Frontend low. Now you put the materials into the columns and the calday into the rows -> DB medium (even if you read the same number of rows from the DB), OLAP low (no aggregation needed) Frontend high.
    If you are doing lots of virtual key figures and calculation of any kind you raise the OLAP time with the same DB effort.

    2.Sequence of optimizations –
    What is the best sequence of optimizations steps – which one I should optimize first Database, OLAP time or Front-End? My suggestion was to start with recognizing the culprit among these components & start with most time consuming part. Any more suggestions?
    — You’re right here, as Goldratt says, find the constraint. Normally there are very few efficient ways of handling OLAP or Frontend performance problems unless you did a lot of tweaking before so I normally focus on DB, i.e. aggregates and indexes. Works most of the time.

    3.Efforts trade off –
    Another point I mentioned make sure that you are aware of trade off involved of any action taken in order to improve query performance, for example if you decide to create Aggregates for query performance then also take added maintenance efforts into consideration. Question is – Is there a way to quantify this trade off?
    — There is no real trade-off. As long as your process chains are finished before the users normally start it’s fine. I don’t think that you can calculate this effect beforehand. But limiting the number of aggregates is normally a good idea.

    Best regards
    Dirk

    (0) 

Leave a Reply