HANA SQL slow when selecting timestamp

Joery — Fri, 10 Feb 2017 18:04:01 GMT

Dear HANA enthusiasts,

I am getting more and more familiar with native HANA developments and the best practices for it due to the many blogs and useful answers to some questions asked before.

Here we are again and this time everything is working correctly with limited data but very slow when using more data. When using a lot of data the query that is executed will even drain all system resources. After analysis of the logic I noticed some strange behaviors.

The queries seem to be sowed down when using 'UNION' in SQL scripts or table functions instead of 'FULL OUTER JOIN' with the same behavior (yes I started using table functions instead of Scripted views 🙂 )
When selecting a TIMESTAMP field the execution time rises drastically.

I have reworked my resulting view (which has a lot of hierarchically structured subviews) into a smal sample that can be executed in a single SQL statement.

When executing this and not taking the timestamp field into account the execution time is about 2 seconds for about 12000 records.

Then when also taking the timestamp into consideration it takes about 1minute 20seconds for the same amount of data.

Like this it is not usable since in the end on a real system there will be 1000 or more times the amount of records. And as far as I am aware HANA should not have any trouble with this.

Additionally I also tried to remove one of the NVARCHAR(255) fields while leaving out the timestamp as well and the difference is marginal.

And for the sake of completeness I also want to add that we are currently working on HANA revision 97.00.

Did anyone have similar experience or possibly know a solution for this abnormal slowdown?

Thanks in advance,

Joery

Re: HANA SQL slow when selecting timestamp

Joery — Mon, 13 Feb 2017 09:08:12 GMT

In the meantime I went deeper into the selection hierarchy and found a lower level where there are aggregations on the timestamp fields

When leaving these aggregations out the performance increases greatly. However the aggregations seem to be required to get the correct result.

SELECT 
        PRODUCT.PRODUCT_CODE AS MATERIALNUMBER,
        ASSORTMENT.USER_ID AS CUSTOMERNUMBER,
        MIN( LIFECYCLE.ORDERFROMDATE ) AS ORDERFROMDATE,
 	MAX( LIFECYCLE.PHASEOUTDATE ) AS PHASEOUTDATE,
 	MAX( LIFECYCLE.DROPDATE ) AS DROPDATE,
 	PRODUCT.IS_ACCESSORY AS ISACCESSORY

Question Re: HANA SQL slow when selecting timestamp in Technology Q&A

HANA SQL slow when selecting timestamp

Re: HANA SQL slow when selecting timestamp