Strange INNER JOIN behaviour

[Phillip_Morgan]

Hello all,

We are re-engineering some programs to make them more readable, fix their naming conventions, and more importantly, improve their performance.

Good thing is that there is a lot to do (select inside loops, repeated selects, bad selects...), so we are pretty sure that our work, if well done, will give good gains in performance.

Here's the curiosity:

The following is a join from the old program:

  SELECT vbak~vbeln vbak~fmbdat vbak~vbtyp
         vbap~posnr vbap~matnr vbap~werks
         vbap~klmeng vbap~meins INTO TABLE wi_sel_order
               FROM vbak INNER JOIN vbap ON
                    vbak~vbeln = vbap~vbeln
                          WHERE vbak~auart IN s_auart AND
                                vbak~vdatu IN s_vdatu AND
                                vbak~fmbdat IN s_fmbdat AND
                                vbak~vbeln IN s_vbeln AND
                                vbak~vkorg IN s_vkorg AND
                                vbak~vtweg IN s_vtweg AND
                                vbak~spart IN s_spart AND
                                vbap~werks IN s_werks AND
                                vbap~matnr IN s_matnr AND
                                vbap~pstyv IN s_pstyv AND
                                vbap~abgru IN s_abgru.

In the hopes of improving its runtime we re-arranged the where clause a bit:

  SELECT vbak~vbeln vbak~fmbdat vbak~vbtyp
         vbap~posnr vbap~matnr vbap~werks vbap~klmeng vbap~meins
  INTO TABLE gt_sel_order
  FROM vbak INNER JOIN vbap ON ( vbak~vbeln = vbap~vbeln )
  WHERE vbap~matnr IN so_matnr AND
        vbak~vkorg IN so_vkorg AND
        vbak~vtweg IN so_vtweg AND
        vbak~spart IN so_spart AND
        vbak~auart IN so_auart AND
        vbak~vbeln IN so_vbeln AND
        vbap~werks IN so_werks AND
        vbak~vdatu IN so_vdatu AND
        vbak~fmbdat IN so_mbdat AND
        vbap~pstyv IN so_pstyv AND
        vbap~abgru IN so_abgru.

This second join, is faster, but also returns a few more records than the original (40-50 entries).

We tested an identical query in Quick Viewer and it returns the same number of entries our modified version does.

This conforts us, but we cannot explain it.

Any idea as to why this happens?

[pokrakam]

Curious. Please run both with SQL trace (ST05) and compare the SELECT statements generated at the DB end.

[Phillip_Morgan]

Good idea. Had not thought of that option. I'll try it.

Thanks!

[Sandra_Rossi]

"This second join is faster": impossible, both queries are identical. Or, if it's faster, it's simply because they don't have the same WHERE selections, obviously.

[raymond_giuseppi]

Did you compare the content of the SO_xxx and the S_xxx selection ranges, the SQL are similar so there must be some difference in selection criteria, are some of those built from code?

[Phillip_Morgan]

Raymond,

I had looked at the ranges -both from variants- but I think I found the culprit. This went unnoticed at first.

The same select-option on "reason for rejection": both were empty, but one had an equals sign (find only empty fields) and the other had no equals sign (find all). I did not find the same difference the developer told me about but that can be due to the date interval used. Anyways, both programs turn up the same number of entries.

Takes me back to my first years in SAP when they used to tell us:"SAP is always right, keep looking".

Merci!

[ThomasZloch]

I'm puzzled that resorting the order of the where-criteria would make any significant difference.

That's one of those "urban myths" in my experience.

What is the gain in %, and did you run both variants several times, discarding the first results (buffering effects) ?