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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.