Skip to Content

ABAP SQL | Client handling in Joins

Hi all,

while reading the HA400 training course script (course version 12) I encountered following passage:

At first I thought: wow, never heard of it, apparently all our joins are badly wrong! But since this passege describes changes as of 7.4, I just tried it in a 7.0 system. I created two tables - head and items, then I filled both of them, but the first in one client, and the second in another client. No matter in which client and whether inner or outer join - I'm getting no data from the other client.

So what does this improvement mean and is it reasonable in systems < 7.4 to add client field to the on-condition?

I could not find any clues in the documentation so I would be very thankful, if one of You experts could put some light on this mysterious improvement.

Best regards

Michał

Add comment
10|10000 characters needed characters exceeded

1 Answer

  • Best Answer
    Posted on Mar 25 at 08:06 AM

    So, if we have this Open SQL code, where tables T1 and T2 are client-dependent:

    SELECT *
    FROM t1 INNER JOIN t2 
    ON t1~keyfield = t2~keyfield
    WHERE t1~attr = ''
    

    In versions < 7.40 maybe the Open SQL SELECT converted to SELECT of the database was only including the client selection (example for client 100):

    SELECT *
    FROM t1 INNER JOIN t2 
    ON t1.keyfield = t2.keyfield
    WHERE t1.client = '100' and t2.client = '100' and     " <====== only here
          t1~attr = ''

    In versions >= 7.40 the join between clients has been added:

    SELECT *
    FROM t1 INNER JOIN t2 
    ON t1.client   = t2.client AND                        " <====== here (new in 7.40)
       t1.keyfield = t2.keyfield
    WHERE t1.client = '100' and t2.client = '100' and     " <====== and here 
          t1~attr = ''
    

    The returned rows are the same for both queries BUT the database may sometimes better optimize the query if the join of the SQL converted to database includes explicitly t1.client = t2.client. It's what is explained in the consulting note 621640 - SELECT with JOIN and automatic client handling:

    • "When you use a SELECT statement with a JOIN statement for two or more tables, the database access is slower than expected."
    • "To assist the database optimizer, you can add a comparison in the 'TAB1~MANDT equals TAB2~MANDT' format to the ON condition."

    So, in this note, SAP proposed eventually to test if this solution in Open SQL (in versions before 7.40) improves the performance:

    SELECT *
    FROM t1 INNER JOIN t2 
    ON t1~client   = t2~client AND             " <====== you may add it (not needed after 7.40)
       t1~keyfield = t2~keyfield
    WHERE t1~attr = ''
    
    Add comment
    10|10000 characters needed characters exceeded

    • Thank You Sandra for Your answer! Actually I was also thinking about it. But I couldn't find that note, so thank You.


      Now I tried to add the MANDT field to the ON condition, since the target systems for which we develop range from 7.0 to the newest ones. But from 7.40 on the use of MANDT field in the ON condition is notified as an error - while (extended) syntax check and in the default variant of Code Inspector.

      Dynamic SQL is no choice, so I think we'll end up ignoring this issue - only customers with older systems can possibly be affected.