cancel
Showing results for 
Search instead for 
Did you mean: 

Query Operation - SAP table pull - Performance issue

Former Member
0 Kudos

Hi,

I am doing an simple operation where i am mapping fields form EDIDS-SAP table to a temp table , Please note that i have not used any filters in the operation.

Its taking more time and i have tried increasing array fetch but still no luck ..Is there any other points i need to consider and try ?

Thanks,

Arun.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Dirk & Surya,

My DS version is 4.1 and i dont have an option to use abap data flow so going with the normal data flow.

And i am extracting only 8 columns out of the total ones but still its taking close to 45 mins ..

Any leads pls.

Thanks,

AJ.

former_member187605
Active Contributor
0 Kudos

How many records?

I assume you only have to a full extract once for an initial load. Afterwards, you wil have to extract the delta only. Data volumes will be much smaller, elapsed time much shorter.

Former Member
0 Kudos

But how do we differentiate between full extract and delta , As new IDOC's would be generated all the time and old records would be obsolete.

I dont think this delta logic would work for me

former_member187605
Active Contributor
0 Kudos

You use CREDAT (and CRETIM) columns in a where-clause.

Suppose you build a daily job, then every day you can load the IDOCs generated the day before:

  • define a global variable and initialsie it in a script: $G_Yesterday = sysdate()-1;
  • define your where-clause as CREDAT = $G_Yesterday

In DS 4.2 the condition is pushed to the underlying database. That means only yesterday's data will be sent over to DS, which will speed up your process enormously.

Former Member
0 Kudos

Thanks Dirk . I hope this one will works in my case.

Cheers,

AJ.

Former Member
0 Kudos

Hi,

I  would like to know how it works in the back end ? I assume that the moment i pull the EDIDS table in designer it will have all the records on it and based on the where condition i apply it will be filter out the records or when ever i execute the query only the EDIDS will get filled with the records ?

If EDIDS only getting filled when the query is executed , I  already have all the IDOC numbers in a table and i can use that IDOC numbers in EDIDS table where clause to pull the relevant fields ? Rather than going with the date option in the global variable !!

Thanks,

AJ.

Message was edited by: Arun Joseph

former_member187605
Active Contributor
0 Kudos

If all goes well, only the records filtered out with the where-clause are pulled into DS.

In Designer, open your data flow, then select Validation > Display Optimized SQL.. to view the code that will be generated at runtime.

Former Member
0 Kudos

Please correct me if the below fact is wrong.

Is there any hard and fast rule like we should not have any joins to the SAP Table directly , Rather than we should have a query transform to pull only the required fields from SAP table to a temp table and then make a join.

Thanks,

AJ.

Former Member
0 Kudos

Hi,

you can use RFC_READ_TABLE to read using certain where clauses. Even BODS use the same functionality with BODS_RFC_READ_TABLE , these two functions are same.

In the former one you can filter the records from sap end where as in latter one DS will pull entire data into DS and then filter it which will be error prone as the volume grows huge.

If the records in the table are in lakhs, then the pull time may go more than 30 minutes where the entire job will fail.so better not to go with entire data pull;

Thanks

Phaneendranadh

former_member187605
Active Contributor
0 Kudos

Joins are not pushed down to ECC when used in a normal data flow.

If you need joins on ECC tables, you'll have to include them in an ABAP data flow.

Former Member
0 Kudos

Hi ,

Do you have sample document to refer how to get this FM called in BODS ?

Thanks,

AJ.

Former Member
0 Kudos

Hi Dirk,

ABAP Data flow is out of my options , Is there any way i can get this done in a optimized way ?

Thanks

AJ.

former_member187605
Active Contributor
0 Kudos

You can only try to extract as few records (and as few columns) as possible in separate dataflows and load into tables in a staging area. Then join those tables.

Former Member
0 Kudos

Arun,

I know you have already mentioned that ABAP Dataflows are not an option for you but scenarios like you mentioned are exactly where ABAP DFs need to be used. Not only is your development time significantly lower (Because it is a straightforward where clause pushed down to ECC) but also you will achieve performance which you cannot match with regular dataflows.

I haven't used the 'RFC_READ_TABLE' function myself but below is a link to the SAP supplement. Page 95 describes how to invoke a RFC function in general.

http://help.sap.com/businessobject/product_guides/sbods42/en/ds_42_sap_en.pdf

Good luck,

Chaitanya

Former Member
0 Kudos

that's a huge table and i sssume it has 25 columns. did you try using ABAP data flow ? can you see how many records this table has in your SAP system ?

Thanks

former_member187605
Active Contributor
0 Kudos

Which DS version? Are you using ABAP or normal data flow?

You can reduce the load volume (and as such increase the speed) by removing columns you won't need from the extract, if any.