on 12-04-2015 10:21 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
96 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.