Skip to Content

Query response time HANA

Hello Experts.

I need some help with understanding the query response time/cost in HANA instance.

I ran a select query on HANA instance and got different query response times or the execution times from sql console and planviz respectively.

From sql console the query execution time: ~10ms

From Planviz prepare procedure: ~90ms

From Planviz execute procedure: ~60ms

Now, which one should be considered as actual execution time and why we see the difference in this metrics. Please advise.

Thanks,
Abhisek

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Jul 23, 2016 at 04:00 AM
    10

    In addition to Lucas' comments I like to add this:

    Looking for "the" response time is a common fallacy and the result of trying to make things simpler than they are.

    One point here is that response time is not a number or a specific value. Response time (at least) has to be looked at as a distribution. A SAP HANA database, like most other modern systems, is a dynamic system that interacts with other dynamic systems (operating system, network, storage systems, application servers, DB clients) and every one of those interactions have influence on the processing state of SAP HANA.

    Assuming the same simple program execution model that could be used for a single threaded computer, say like the Commodore C64, for a DB query execution is a mismatch of concepts.

    The WEB based IDE provides a automated repeated query execution to cater for this (on a minimal basis). For more serious runtime measurements, you would want to have a tool that executes queries several times and allows to look at the distribution of runtimes (incl. MIN/MAX/AVG/MEDIAN/TOP_90_PERCENTILE/WORST_90_PERCENTILE etc.)

    Another point to be clarified here is how the usage of PlanViz relates to the query execution time. PlanViz' main purpose is to provide an insight into how SAP HANA executes a given query.

    It's in fact a trace of that execution. Now, when you want a program to write additional information (about it's own current state) out for later review, then this means the program has to do more work. Even with multithreading and parallel programming, more work often means there is a certain degree of additional time required to organise and finally do this extra work.

    For tracing this is general problem, since usually nobody wants to interfere too much with the actual program execution too much, just because some tracing is activated. Unfortunately, there is no general easy way available that allows "tracing for free".

    This of course applies also to the PlanViz trace.

    When a query is executed in the context of PlanViz the response time will be longer than without PlanViz.

    Now, looking at the numbers you mentioned, it's important to make sure to not compare apples with oranges.

    The SQL console reports things like "query execution time" and "kernel time". In addition to this, you'll find that there is a "time for fetching result".

    For the database, the active computation part is pretty much done, when the result set has been calculated. For the client application, that eventually takes the result and does something hopefully useful for the use with it, this is only part of the response time. The client application also needs to fetch the results.

    Since the SQL console/SAP HANA studio is not meant as a consumption client, but for development and administration only, it doesn't fetch all the results by default, but only a limited number of records.

    That means, for most cases, the total response time in SAP HANA studio's SQL console won't reflect the response time that the application gets.

    (I leave out things like network time here for now to keep this already long comment a bit shorter)

    This is not to say, that fetching of the result means that SAP HANA doesn't have to do anything. The SAP HANA server needs to keep track of the client cursor's position, package the right amount of result records into network packets, etc. Also, more importantly, it needs to perform 'late materialisation'. This means that instead of keeping a computed result set in memory all the time as it would be delivered to the client, SAP HANA 'fills the result set with the actual values' upon fetch time as far as possible.

    When the client fetches the data, the records are completed and send off to the client.

    It should be clear that the more records get fetched, the more work needs to be done and the longer this whole process takes.

    Now, PlanViz, does fetch all the records (I believe that's still the default - it can be configured in the SAP HANA Studio preferences), so that we can get a reasonable representation of the server side work related to the 'fetch' part of the query execution.

    The "preparation time" in PlanViz is shown to provide an easy understanding of how much time SAP HANA would use to prepare (compile & optimise) a given query. This is often not easy to see in the SQL console and for repeated statements this time is completely 'hidden' since the SQL plan cache is used.

    Now, you're fully right to look at the above and not being in a better position to analyse a query's performance. Yet, that is.

    Here are a few points to give some form of guidance:

    • The most important response time is what the end user usually experiences and notices.
    • Look at the final response time distribution first and break it down into components (which query? what part of the query takes most time? is it always the same part of the same query? what is the reason for this part taking so long? does the runtime of this part explain the long runtime or is there a 'gap' ?)
    • Look at the environment in which the query is executed: test or prod. system? system load during the execution?
    • If one problem has been identified, refactor the query/data structure if possible and measure the effect based on this single change.
    • Stop investing time, when the objective has been achieved. E.g. if the query is "good enough" running in 0.02 seconds and you might get it to 0.01 seconds if you'd only invest more times (say two days) - then: don't. Make a note, leave a hint or put it into your idea jar, but there are usually more pressing issues that need attention now and where the 'payout' is larger.

    Personally I am very much aware of the gap we have with SAP HANA related performance analysis and tuning methods. And the huge and growing number of possible stack configurations adds to this difficulty. Having said that, it's even more important to have a robust and clear model of how your end to end performance is put together and work along this model.

    As a reading tip, more about general concepts and approaches, and totally not about SAP HANA, I recommend the Method-R material from Cary Millsap, Karen Morton et. al. (Papers - Papers). It's very Oracle centric when it comes to tools, but it's very general when talking about concepts and strategies.

    Looking at this comment, I hope somebody made it to the last paragraph to pick up the link... 😉

    Cheers,

    Lars

    Add a comment
    10|10000 characters needed characters exceeded

    • SAP HANA doesn't use saved statistics for in memory tables. Instead tables are sampled upon optimisation time (if the information are not already available in internal memory structures).

      There is no way to run queries without having the optimizer use and/or generate those samples.

      Fortunately this sampling works relatively fast as long as the table is in memory, not too large and doesn't have too many partitions.

      Concerning the goal of optimising the variance of the query execution time: I'm not aware of a method that would get you there. Since the actual runtime easily can be influenced by factors that have nothing to do with the query or the involved objects, I'd say it's rather unrealistic to reach this goal. After all, SAP HANA (as many other DBMS) does not provide a guarantee for query processing resources.

  • Posted on Jul 22, 2016 at 05:08 PM

    Hi Abhishek,

    As an SAP employee you could have used internal communities to ask questions of this nature.

    Anyhow, the runtime of a planviz will consider the statistics runtime and other info for planviz. Therefore the exec time can indeed be different than the direct execution from SQL console. Without actually going through the planviz you can only have guesses here.

    BRs,

    Lucas de Oliveira

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.