BusinessObjects Board

DOP distinct order by not working on SAP DS 4.2 SP2

dataservices performance
#1

Hi,

We want to migrate from BODS 3.2 to SAP DS 4.2 and one specific DF is running >5 hours SAP DS 4.2 and 1 hour in BODS 3.2.

The DF is complicated.
DOP = 4
reads 200 mln rows, performs distinct/order by (via DT to enable pushdown) and a filter which results in 27 mln rows.
The 27 mln rows are used as source to perform a lookup_ext on a table of 29 mln row. The outer_join results in more records, so the lkp function is using the max logic as well. This lookup_ext runs as a separate process.

In BODS3.2, because of the DOP 4, the order_by/distinct rows are spilt into 4 threads and 4 al_engines is performing the heavy lookup. The CPU en Mem (including pcache) is optimal utilized and the work is done within 1 hour. The IPCSource/Target for the distinct_order_by is shown in the log.

– BODS3.2 behavior DOP 4 - split and used in lookup_ext
IPCSource1_q_distinct_rows-OrderBy1 STOP 49566328
Round_Robin_Split STOP 49566328
q_distinct_rows-OrderBy1 STOP 12391600
q_distinct_rows-OrderBy15 STOP 12391600
q_distinct_rows-OrderBy16 STOP 12391600
q_distinct_rows-OrderBy17 STOP 12391528
IPCTarget1_q_distinct_rows-OrderBy1 STOP 49566328

In SAP DS 4.2 the distinct/order by is not generating the IPCSource/Target.
So it has decided a different optimization which is causing chaos on the server. The CPU is not utilized (only iowaits). There is only 1 al_engine active which is consuming 20% of CPU. This job runs forever, no progress and only iowaits.

– SAP DS 4.2 behavior DOP 4 - split but no IPCSource/Target !!!
q_distinct_rows STOP 49566328
Round_Robin_Split STOP 49566328
q_distinct_rows-OrderBy1 STOP 12391600
q_distinct_rows-OrderBy15 STOP 12391600
q_distinct_rows-OrderBy16 STOP 12391600
q_distinct_rows-OrderBy17 STOP 12391528

OS: linux 32 GB 4 CPU
SAP DS 4.2 SP02 P1
Database: Oracle 11g exadata.

Does anyone has else experienced problems with DOP in relation to lookup_ext using large lookup tables in SAP DS 4.2?

We cannot upgrade if this problem is not resolved and after investigating this issue for a week now, I cannot find a solution…

Gr.
Marcel

mtelling :netherlands: (BOB member since 2006-06-23)

#2

For your information.

There are performance issues in SAP DS 4.2 on linux for large pcache data sets and with lookup_ext (in combination with pcache).

This is acknowledged by SAP and there are investigating the issues.

mtelling :netherlands: (BOB member since 2006-06-23)

#3

Because you are using DOP > 1 the Dataflow will create four instances of the data for your lookup_ext() (except in the situation explained below).

I would never put that many rows in a lookup_ext() unless you set it to NO_CACHE, just for memory reasons. With NO_CACHE you would be performing 27 million selects against the database and that doesn’t sound like it would be very fast.

In DS 4.x it will try to push the lookups down into the database along with your source table. Sometimes this works well and sometimes it doesn’t. If it pushes it down to the database then you need to make sure there are indexes to support it.

Look at the Display Optimized SQL to see if the lookup is being pushed down. Given that you are showing four instances of the q_distinct transform it doesn’t sound like much is being pushed down to the database.

eganjp :us: (BOB member since 2007-09-12)