BusinessObjects Board

Left outer Join taking longer than expected

dataservices performance
#1

Hello,
I have a DF with two source tables into a merge. Then this merge goes to another merge with another source table and then finally to the output.

Table A has 240416 rows
Table B has 41126 rows.

JOIN condition
Table A left outer join Table B on A.column1 = B.column4

Table C has 600 rows.

But the graph takes almost 50 minutes to run. Its the first part of the graph that has the issue, as i tested by removing Table C and running the graph with the same 50 min execution time. All i intend to do is to replace a column in A if i find a match in B.

Any suggestions on how to quicken the execution.

BOB_US (BOB member since 2006-03-16)

#2

In the job monitor, how many rows does it indicate for the join, is it creating a Cartesian product? If so, you could try modifying the join rank.

Another option might be to use just table A, use the lookup_ext function, and cache table B.

thesnow :us: (BOB member since 2011-08-10)

#3

Within the monitor i only see the target table and the query with a row count of 240416

BOB_US (BOB member since 2006-03-16)

#4

If the join is pushed down to the database then it could be a lack of statistics that results in the DBMS making a less than optimal query plan. I’ve seen Oracle do this.

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

#5

Jim,
I am using sql server as the under lying DB engine. Please let me know, if there is a way to shorten the execution time. The main production tables run into million records and that would exponentially increase the time.

BOB_US (BOB member since 2006-03-16)

#6

Start by having your DBA gather statistics on the tables.

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

#7

Please post the optimized query for the DF.

Cheers.
Shaz

Shazin :india: (BOB member since 2011-07-19)