HANA Query Optimizer seems not working with IQ Virtual Table through SDA

[Former Member]

Hi experts,

I am working in a project with SAP HANA and hot-cold data management where cold data are stored in Sybase IQ. Hana recognizes IQ tables through Smart Data Access.

1. I wrapped my virtual table "Z_DS_BUDGET_SDA" in a Calculation View "Z_DS_BUDGET_COLD"

2. I set the engine for the wrapper calculation to "SQL Engine"

3. I added the wrapper calculation to an existing calculation view to manage both hot and cold data together

The problem is the following: even without retrieving any data from cold table in IQ, the HANA Query Optimizer seems no longer working when I add the "Z_DS_BUDGET_COLD" wrapper calculation and the execution time of the main query goes from about 3 seconds to 1 minute and 40 seconds

We are currently using SAP HANA SP09

Based on what I found on SDN, I tried to run an "explain plan" for both cases with the following results:

- Before adding virtual table to the CV I have an optimized plan with 231 steps

- After adding the virtual table the plan contains 678 steps

I also tried to create statistics for the virtual table to help the index engine working properly but it doesn't seems to change the output

CREATE STATISTICS ON "ZSAP_HBI"."Z_DS_BUDGET_SDA" TYPE HISTOGRAM;

Do you have any suggestion?

Thanks

[lbreddemann]

Hmm... doesn't sound right.

Can you please post a simplified example for this model here, so that we can understand how you integrate both data sources in the calc. view?

Also, you might want to use PlanViz to get more detailed information on the actual execution.

Cheers,

Lars

[Former Member]

Hi Lars,

I attached some files to explain the integration we made. Basically I have done this in 3 steps:

1. I wrapped the SDA virtual table in the first calculation view (CV1)
2. The second view joins hot and cold data with an Union step (CV2)
3. Finally I attached CV2 to the main calculation view (CV3)

Manuel

[Former Member]

To better understand what's happening with the SDA I have generated the two planViz.. see as an attachment

Thank you guys for your attention

Manuel

[Former Member]

Hi guys,

I have investigated in planViz and I found an interesting thig: it seems that when I attach the SDA datasource HANA can't use the "shared subplan" anymore and then the planViz swell from 231 to 678 steps.

How do you think about that? There are some known issues regarding SDA and "shared subplan"?

Manuel

[Former Member]

Hello

Are all your calculation views set to use SQL engine?  Mixing this option in nested views won't work.

When using SDA, a bit of remodelling is often necessary to get the optimiser to push-down filters to the remote source.  Trial and error is the name of the game!

Michael

[Former Member]

Thank you for the answer Michael!

Yes all calculations are set to SQLEngine, but some of them have the Data Category property set to CUBE or DIMENSION.. do you think this can represent a problem?

Based on your experience, what kind of rework is usually needed to have SDA working properly? What can I try to do to move toward the solution?

I really appreciate your help

Manuel