Join In BODS job

[Former Member]

Hi All,

I am new to BODS tool. I am trying to read data from two databases (Hana and MS SQL Server)and do the inner join in Query transformation (From tab). These tables are huge in size and I don't need all the fields from Tables. So I am using SQL block to write custom selects for both tables with some where conditions . These select queries are working fine and takes less time to execute when I checked in the their respective consoles.

But the BODS job is taking lot of time to execute this job. The log of query transformation shows that it is going for a Cartesian join and then doing filter of join condition. The row count keeps on growing as it is a Cartesian join and I have to kill the job.

How Can I make this join to run faster . Is there any other option ?

Regards,

Suman.

[former_member200473]

Hi Suman,

you can stage anyone db using "data_transfer Transform" and then you use inner join.

This will definetly give a big impact to perfomance.

Shiva Sahu

[Former Member]

Hi Suman,

Choose 'Yes' as cache method for both tables in the From tab of Query transformation. It should solve your problem.

Cheers,

Bruce

[former_member186897]

Hi Suman,

I would suggest you to create a database link since you are using two different database servers and make sure that your DF is full pushdown. Inner join shall not take much time if it is full push down.

You may refer this article on how to create a database link in sql server. URL

Another article on performance optimization. URL

Hope this would be helpful.

Regards,

MSA

[former_member186897]

Using SQL is one way but You can also directly join these tables with whatever where clause you have in your job. kindly go through the article on the database link in my last reply. you need to configure this first which will maximize the full pushdonw operations no matter if they are different database servers else joining on big tables will consume lot of time.