cancel
Showing results for 
Search instead for 
Did you mean: 

BODS -Joining the tables from Oracle DB and SQL server DB is taking every long

former_member700133
Discoverer
0 Kudos

Hi,

I'm using BODS 4.2 SP14 working on joining two tables form different databases which is taking very long to give the result.

I am trying to join a table from Source oracle DB with millions of records and another table which is in my staging SQL server DB which has only 10 records.

when I try to join these two tables in BODS it taking very long time to get the data from Oracle table for those 10 records.

I have tried different optimization techniques but still with no use.

Input
Table(SQL server - 10 records)
Table(Oracle DB - millions of records)

Querytransform(Join)

Output
Table(SQL server) - should be populated with the data from oracle table for those 10 records in SQL server Table.

Could you please help me with this.

lenastodal
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

Thank you for visiting SAP Community to get answers to your questions. I am here to help you to get the most out of it.

First of all, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers (if you haven't done so already), as it provides tips for preparing questions that draw responses from our members.
For example you:
- can outline what steps you took to find answers (and why they weren't helpful)
- can share screenshots of what you've seen/done
- can use a descriptive subject line

Please also make sure you're using all appropriate tags, so the right experts can find your question. Overall, the more details you provide, the more likely it is that members will be able to assist you. Should you wish, you can revise your question by selecting Actions, then Edit (although once someone answers your question, you'll lose the ability to edit the question - but if that happens, you can leave more details in a comment).

Finally, if you're hoping to connect with readers, please consider adding a picture to your profile. Here's how you do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS. By personalizing your profile with a photo of you, you encourage readers to respond.

Best,
Lena (SAP Community Moderator)

Julian_Riegel
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi - could you also share a screenshot of the optimized sql of your dataflow?

Julian

Accepted Solutions (0)

Answers (1)

Answers (1)

werner_daehn
Active Contributor
0 Kudos

Yes, the display optimized SQL would help to understand what is going on.

If the two tables are joined directly in a query, then setting the join rank of each and hinting to cache the small but not the large one, would help.

Here a step-by-step receipt:

https://wiki.scn.sap.com/wiki/display/EIM/Joining+tables+in+the+engine