Best Practice of how many Joins can we have in a SELECT query

[ekekakos]

My question is simple. How many tables/joins is the best to have in a query. And I am giving the below example

SELECT FROM wrs6
      INNER JOIN wrs1
        ON wrs6~asort = wrs1~asort
      INNER JOIN wrst
        ON wrs1~asort = wrst~asort AND
           wrst~spras = @sy-langu
      INNER JOIN mara AS assort_matkl
        ON wrs6~matkl = assort_matkl~matkl AND
           assort_matkl~lvorm = @abap_false
      LEFT JOIN makt
        ON assort_matkl~matnr = makt~matnr AND
           makt~spras = @sy-langu
      INNER JOIN mvke
        ON assort_matkl~matnr = mvke~matnr AND
           wrs1~vkorg = mvke~vkorg AND
           wrs1~vtweg = mvke~vtweg AND
           wrs6~sstuf = mvke~sstuf
      LEFT JOIN wlk1
        ON wrs6~asort = wlk1~filia AND
           assort_matkl~matnr = wlk1~artnr AND
          wlk1~sstat <> '5'
      FIELDS DISTINCT wrs6~asort AS assortment, wrst~name1 AS assort_description,
             assort_matkl~matnr AS master_material, makt~maktx AS master_description, assort_matkl~meins AS master_uom,
             assort_matkl~matnr AS slave_material, makt~maktx AS slave_description, assort_matkl~meins AS slave_uom,
             wlk1~sstat AS Assortment_status, 'M' AS master_slave_indicator,
             CASE WHEN wlk1~artnr IS NULL THEN @abap_false ELSE @abap_true END AS in_listing,
             CASE WHEN wlk1~artnr IS NULL THEN @abap_false ELSE @abap_true END AS in_out_list
    WHERE wrs6~asort IN @select_options_structure-s_asort AND
          assort_matkl~matnr IN @select_options_structure-s_matnr AND
          assort_matkl~matkl IN @select_options_structure-s_matkl AND
          wrs1~lstfl = @select_options_structure-p_lstfl AND
          NOT EXISTS ( SELECT * FROM zkrh_scmt_mslave WHERE slave = assort_matkl~matnr AND del_flag = @abap_false )<br>

This query has 7 tables. Is it good to 7 tables or to split in 2 queries and use in the 2nd the FAE.

The big concern is how to check it if it returns sy-subrc = 4. I am using the SQL editor of Eclipse but very few use eclipse to write ABAP.

I am looking forward for your answer.

Elias

[matt]

It's best to have as many tables as you need to get the data! Though it can be difficult to debug, it's generally the most efficient way.

FAE is in the vast majority of cases less efficient than joins.

Do put as much as you can into the where clause for performance. The where clause is evaluated first, so that you won't have so much data for the joins. I.e.

• wrst~spras = @sy-langu
• assort_matkl~lvorm = @abap_false
• makt~spras = @sy-langu
• wlk1~sstat <> '5'

should be moved to the where clause.

[BaerbelWinkler]

matthew.billingham

Hi Matt!

Will "makt~spras = @sy-langu" and "wlk1~sstat <> '5'" actually work in the WHERE clause as the tables are included with a LEFT JOIN? Or is this only an issue when LEFT OUTER JOIN is explicitely used which I did run into in the past and which I could only get around with adding the restriction directly in the JOIN-condition.

Apart from that, I now also tend to build rather large SELECT-statements to get as much relevant data in one go. In order to not shoot myself in the foot while doing so, I build them in stages with adding another JOIN only after testing that the SELECT as build thus far still produces the expected results.

In addiiton, I'm careful to properly and consistently format the SELECT to render it as readable and understandable as possible.

Cheers

Bärbel

[Sandra_Rossi]

8b889d0e8e6f4ed39f6c58e35664518f Well seen Bärbel, I agree too.

[matt]

8b889d0e8e6f4ed39f6c58e35664518f Good point. So just move

• wrst~spras = @sy-langu
• assort_matkl~lvorm = @abap_false

To the where clause.

I found this issue a few years ago when a select that should have only produced a small result set was taking too long or even dumping with out of memory.

[Sandra_Rossi]

It's only about code legibility, it's not specific to SQL. You could ask the same kind of question for ABAP (how many lines is it best to have in a method, how many nested loops can we have, can we use regular expressions, etc.)

It highly depends on the target audience you plan to have (what is the knowledge of developers who read the programs).

I don't know if we can define an average audience - here is a list of (stupid) preconceptions:

• Company with software developed by ABAP experts (they use all highly-productive things)
• Custom code developed by ABAP developers from customer team (know many things, they can discuss together to decide what they want to use)
• Custom code developed by big consulting companies for customers (developers are usually quite young and not trained much, possibly avoid ABAP Objects, regular expressions, etc.)

[DominikTylczyn]

Hello elkekakos

To supplement matthew.billingham answer, which I agree with 100%:

FOR ALL ENTRIES is internally translated to standard SQL query - that is described in the 48230 - Parameterization of SELECT ... FOR ALL ENTRIES statement note. Therefore if the internal table has a lot of records, the resulting SQL query might get huge and exceed the length limit of an SQL statement. See the note 13607 - Termination of an ABAP with DBIF_RSQL_INVALID_RSQL Therefore FOR ALL ENTRIES needs to be used with caution not only for performance reasons.

As per SAP Help SELECT - JOIN:

Join expressions bypass SAP buffering. Therefore they should not be applied to buffered tables. Instead it may be a good idea to use the addition FOR ALL ENTRIES in these cases, which can access the table buffer.

That is a factor to consider when deciding JOIN vs FOR ALL ENTRIES. However if I'm not mistaken none of the tables in your join is buffered.

Best regards

Dominik Tylczynski