Skip to Content
Author's profile photo Mohammad Shahanshah Ansari

Advantage of Join Ranks in BODS

What is Join Rank?

You can use join rank to control the order in which sources (tables or files) are joined in a dataflow. The highest ranked source is accessed first to construct the join.

 

Best Practices for Join Ranks:

  • Define the join rank in the Query editor.
  • For an inner join between two tables, in the Query editor assign a higher join rank value to the larger table and, if possible, cache the smaller table.

Default, Max and Min values in Join Rank:

Default value for Join Rank is 0. Max and Min value could be any non negative number.

Consider you have tables T1, T2 and T3 with Join Ranks as 10, 20 and 30 then table T3 has highest join rank and therefore T3 will act as a driving table.

Performance Improvement:

 

Controlling join order can often have a huge effect on the performance of producing the join result. Join ordering is relevant only in cases where the Data Services engine performs the join. In cases where the code is pushed down to the database, the database server determines how a join is performed.

Where Join Rank to be used?

 

When code is not full push down and sources are with huge records then join rank may be considered. The Data Services Optimizer considers join rank and uses the source with the highest join rank as the left source.  Join Rank is very useful in cases where DS optimizer is not being able to resolve the most efficient execution plan automatically. If join rank value is higher that means that particular table is driving the join.

You can print a trace message to the Monitor log file which allows you to see the order in which the Data Services Optimizer performs the joins. This information may help you to identify ways to improve the performance. To add the trace, select Optimized Data Flow in the Trace tab of the “Execution Properties” dialog.

You can read my next article here where Join Rank is implemented, step by step. 

Assigned tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Very Useful.

      highest rank - is min value or max ?

      Sample:

      rank

      10

      20

      30


      highest rank is 10 OR 30 ?

      Author's profile photo Mohammad Ansari
      Mohammad Ansari
      Blog Post Author

      Hello Oleg,

      Max value is the highest rank. In your case highest rank is 30.

      Author's profile photo Mohammad Ansari
      Mohammad Ansari
      Blog Post Author

      Oleg, you have raised a good point. I shall update the article with this info as well. Thanks !

      Author's profile photo Former Member
      Former Member

      The issue arose from the last article. You wrote that in the first table has the highest rank, although the value is worth Rank = 10 in the screenshot

      Author's profile photo Mohammad Ansari
      Mohammad Ansari
      Blog Post Author

      oh, i c..i guess it must be a mistake in hurry. i have updated that article too. sorry for the confusion.

      Author's profile photo Former Member
      Former Member

      Hi Ansari,

      I understood what is join rank

      But as u have mentioned that by setting Optimized Data Flow in the Trace tab of the "Execution Properties"dialog ,we can see join rank.

      But I am not able to see any logs which is showing any join ranks.

      Kindly help. I have to check which tables are joining at first level and which are at second level.

      Author's profile photo Former Member
      Former Member

      Helpful...Thanks.

      Author's profile photo Former Member
      Former Member

      so the driving table must have high joining rank ideally right (driving table usually being smaller)?

      Author's profile photo Dirk Venken
      Dirk Venken

      Not necessarily, all depends on the join logic.

      Assume a huge main input table. Logic is lookup and lookup table is relatively small. In that case, cache the lookup table, and the main table will be driving.

      Author's profile photo Swetha N
      Swetha N

      Hi,

      waiting for your next article on a real example on the performance gain from this setting.

      Author's profile photo Mohammad Ansari
      Mohammad Ansari
      Blog Post Author

      Thanks for reminding, Swetha N. I will post something on this soon. 

      Author's profile photo Mohammad Ansari
      Mohammad Ansari
      Blog Post Author

      Swetha N

      Here you go https://blogs.sap.com/2016/11/28/implementing-join-ranks-in-data-services/