Performance issue in accessing SAP IQ data from SAP HANA

[Former Member]

Hi Experts,

We have been struggling to bring data from SAP Sybase IQ in SAP HANA within permissible time limit.

For ex: It takes around 2 min and 15 sec to get 13 Million records from SAP IQ in HANA consisting fields like

Year, Month, Date, ShipTo, Amt. & Qty. Same model takes around 5 sec for said records if source is

HANA instead of IQ.

Also doing raw data preview on IQ virtual table in HANA bring data in microseconds, however if we drag the

Attributes in Analysis tab it takes more than 3 minutes.

Please suggest best/recommended approach for optimal performance to get IQ data in HANA.

Regards

Randhir Jha

[Former Member]

Hi All,

Thanks for your response.

Actually execution time mentioned in my original post is from our Development box whose hardware configuration is low.

When we tried executing same query in Test IQ/HANA box(High hardware configuration from Dev), it took around 55 sec in HANA and 52 sec in IQ for around 1.4 billion of records which is pretty fast.

But based on our business requirement we had to bring it down under 5 seconds. Hence to further optimize, we tried creating index based on Index Advice in IQ but that did not helped.

Finally we decided to reduce the table size by aggregating the data based on date and push it to HANA. This should reduce the table size to  ~20% of original size. We are still working on this approach.

Regards

Randhir Jha

[javier_barthe]

Perhaps some quick action in order to see if there is some performance issue on SQL code sended to IQ its activating index advisor on IQ side and after executing check sp_iqindexadvice output in order to look after some recommendation on IQ table. If there is you could perhaps apply it and then execute again the query.

Hope Helps.

Javier.

[lucas_oliveira]

Hello Randhir,

Just saying 'this is not fast enough' is usually not helpful. Execution plans from the IQ side might show what's missing for you to tune up your scenario.

Anyhow, assuming your issue is happening only when you use SDA I have a few observations on your description:

- Retrieving 13M records from IQ does not seem to be a good idea. sending those over the wire will take time for sure. Maybe you're not aggregating data at the source and obviously joining such a huge amount of data can take time. Thus, aggregation and filtering at the source (if possible) will surely help. Indexing the join columns should also help. More info on IQ indexing/tuning here.

- Data preview usually gets the TOP 1000 results, so there's a clear difference in materialized data volume.

Other than that I guess the iq space would have more inputs on that. Thus, I'm moving this discussion there.

BRs,

Lucas de Oliveira

[Former Member]

Hi Lucas,

Thanks for your reply and thanks for moving it to the right group. I have executed same query in both HANA/IQ side. One thing is clear that out of the total execution time very small percentage of time around 5% is used for transferring from one system to other. Secondly, I am aggregating Amount and Quantity based on Year, Month, Date & ShipTo & wherever possible we are applying relevant filters but there are certain scenarios where in we need complete 2 years of data for comparison. As suggested, will explore other options like index/tuning.

Regards

Randhir Jha

[remi_astier]

Hello Randhir,

Are the lines previewed sorted ?

Could you quickly check that the "top 1000" clause is actually pushed down to IQ ?

It would be funny if 13 millions rows are transferred to HANA, only to have 1000 sent to the studio.

[markmumy]

I would also ask for the HANA version/revision, the IQ version, the network connectivity between the machines, ODBC configuration information for IQ and SDA, an explain plan in HANA showing what HANA ran and what SQL was sent to IQ, the SQL trace output from IQ (-zr and -zo options) showing the code that IQ received and ran, and an HTML query plan in IQ for the SQL that HANA sent to it.

The main issue is filtering.  Since the query hits HANA first, we need to determine if HANA is passing the filter and TOP down to IQ or not.  Assuming that it is, we then need to look at IQ for what's being executed.  If it isn't being passed to IQ, then having the HANA info should help identify possible tuning options.

Mark