topic Problems on CDS Views performance in Application Development and Automation Discussions

Problems on CDS Views performance

former_member231903 — Tue, 26 Apr 2016 09:32:29 GMT

Hi Guys,

I'm having problems with CDS View now, I have a table ACDOCA which has 400 million rows, and in the CDS View(say CDS View Main), ACDOCA(only some fields are used) is used to join 3 other tables.

And I found in PlanViz that all 400 million rows are used to do the join, it consumes a log memory and VERY SLOW.

I write a CDS view(CDS View Agg) in which I select the used fields from ACDOCA and do the aggregation, the result is about 1 million rows, then I use CDS View Agg in CDS View Main to replace ACDOCA.

I thought this should solve the performance problem, but it didn't. In PlanViz I still see 400 million in join.

Please help how to avoid doing such weird thing, I don't think there's any benifit in it.

Regards,

Blangero

Re: Problems on CDS Views performance

thomasgauweiler — Tue, 26 Apr 2016 11:01:01 GMT

Hi Blangero,

can you be a bit more concrete? e.g. what is the SELECT statement on top of the view? which data do you really need? Do you have a WHERE condition? If not, then any aggregation would have always to read the complete table.

Can you do the aggregations before joining the other tables? Might be faster ...

Regards, Thomas

Re: Problems on CDS Views performance

former_member231903 — Tue, 26 Apr 2016 11:40:23 GMT

Hi Thomas,

The details are kind of too much and annoying, sorry for me confuse I brought.

But what I did is just what you suggested, do the aggregations before joining the other tables,

I did the aggregation in "CDS View Agg", seen from HANA SQL the aggregation works well and the data amount are much less.

But when I use "CDS View Agg " in my previous CDS View, and seen in PlanViz, the aggregation didn't happen at all...

Re: Problems on CDS Views performance

Former Member — Tue, 26 Apr 2016 19:13:47 GMT

One suggestion Mr Blangero ,

try grouping within the CSD join the tables required and the DB layer will assume the load.

regards