topic Re: better SELECT ? INNER JOIN? in Application Development and Automation Discussions

better SELECT ? INNER JOIN?

Former Member — Fri, 31 Aug 2007 09:56:57 GMT

Hi,

Can I get this code to be faster/better?



* get addres
      SELECT SINGLE haus
             INTO   lv_evbs-haus
             FROM   evbs
             WHERE  vstelle = lv_eanl-vstelle.
      IF sy-subrc = 0.
        SELECT SINGLE haus
               INTO   lv_ehauisu-haus
               FROM   ehauisu
               WHERE  haus = lv_evbs-haus.
        IF sy-subrc = 0.
          SELECT SINGLE tplnr
                 INTO   lv_iflot-tplnr
                 FROM   iflot
                 WHERE  tplnr = lv_ehauisu-haus.
          IF sy-subrc = 0.
            SELECT SINGLE adrnr
                   INTO   lv_iloa-adrnr
                   FROM   iloa
                   WHERE  tplnr = lv_iflot-tplnr.
            IF sy-subrc = 0.
              SELECT SINGLE *
                     FROM   adrc
                     WHERE  addrnumber EQ lv_iloa-adrnr.

              IF sy-subrc EQ 0.

                wa_billit-adrc_street      = adrc-street.
                wa_billit-adrc_house_num1  = adrc-house_num1.
                wa_billit-adrc_house_num2  = adrc-house_num2.
                wa_billit-adrc_post_code1  = adrc-post_code1.
                wa_billit-adrc_city1       = adrc-city1.
                wa_billit-evbs_lgzusatz    = evbs-lgzusatz.

              ENDIF."adrc
            ENDIF."iola
          ENDIF."iflot
        ENDIF."ehauisu
      ENDIF."evbs

Re: better SELECT ? INNER JOIN?

Former Member — Fri, 31 Aug 2007 10:02:18 GMT

See this blog,

it'll surely help you to write a smaller query

/people/dushyant.shetty/blog/2007/08/21/tool-for-abap-developers-easy-abap-open-sql-joins

Cheers

Ravish

Reward if the info was useful

Re: better SELECT ? INNER JOIN?

Former Member — Fri, 31 Aug 2007 10:06:41 GMT

HI,

it depends upon you no of data which are getting selected.

if you are selecting huge data then select comes over joins

because internally join statement selects the entire data from both the tables(in the case when you are applying join on 2 tables) then it matches according to ur inner or left/right outer condition

if you have less data then go for join else for select.

thanks

vivekanand

Re: better SELECT ? INNER JOIN?

Former Member — Fri, 31 Aug 2007 10:08:21 GMT

Hi Adibo,

Use join statement inplace of nested select.

Select a_{haus b}haus c_{tplnr d}adrnr e~*

into table ttab

from evbs as a inner join

ehauisu as b on <Coindition>

iflot as c inner join <condition>

iloa as d inner join on <condition>

adrc as e inner join on <condition>

where WHERE a~vstelle = lv_eanl-vstelle.

Reward points if helpful.

Regards,

Hemant

Re: better SELECT ? INNER JOIN?

VXLozano — Fri, 31 Aug 2007 10:18:24 GMT

Nice blog.

About performance, if you search the tables by indexes, you will not get bigger differences.

About the performance of the JOINs, it will rely on the order of the tables selected, the fields compared and their position in the WHERE or the JOIN clauses... And worse, some wannabe-programmer users will not understand your code and will disturb your sleeping time at work asking questions about it.

Try to keep track of this thread on SAPFans, we discussed performance and hints about JOINs deeply after reading some blogs from here (you will find the blogs and their authors linked there too):

http://sapfans.com/forums/viewtopic.php?p=878030

Good luck

Re: better SELECT ? INNER JOIN?

Former Member — Fri, 31 Aug 2007 10:19:49 GMT

it won't give much performance

you can do this by useing FOR ALL ENTRIES OR INNER JOINS

if you use 2 or 3 tables inner joins is ok

more than 3 tables again inner join won't give better performance

so better to go for for all entries

reward if usefull

Re: better SELECT ? INNER JOIN?

Former Member — Fri, 31 Aug 2007 10:33:44 GMT

Hello Adibo,

In most of the cases innerjoin will give more performance.

There are certain situations where even inner join also results in poor performance. Ex. If you are joining more than 3 tables... then performance issue.

In such cases like, if you need to join 5 tables... first, inner join 3 tables then select the data from remaining tables FOR ALL ENTRIES into another table. now you can get all the fields into one internal table using severl ways.

And one more thing is never use select single statement in loop.

In those situations get the data into internal table at atime and read that internal table using READ STATEMENT.

Never write statements like select * from marc... or select single * from marc.... This will get the values of all fields from the database table. Mention what ever the fields you want like : select matnr werks from mara... or select single matnr werks from mara...

For more Tips.. Goto TCode SE30 and there click on the button Tips & Tricks or press F6. You will get good Tips.

Reward If Helpful.

Regards

Sasidhar Reddy Matli.

Re: better SELECT ? INNER JOIN?

Former Member — Fri, 31 Aug 2007 10:40:04 GMT

hey where is u r into < table>

in this particular select query....

SELECT SINGLE *

FROM adrc

WHERE addrnumber EQ lv_iloa-adrnr.

reward points if helpful

Re: better SELECT ? INNER JOIN?

Former Member — Fri, 31 Aug 2007 10:46:49 GMT

Hi Sasidhar Reddy Matli ,

You said:

And one more thing is never use select single statement in loop.

In those situations get the data into internal table at atime and read that internal table using READ STATEMENT.

But if database table has 10.000.000 records, then is better to use select single in LOOP then to get the data into internal table?

Re: better SELECT ? INNER JOIN?

seshatalpasai_madala — Fri, 31 Aug 2007 10:48:38 GMT

Hi,

In such a case its better to use PACKAGE SIZE addition along with SELECT ENDSELECT. So that you dont run out of memory and time.

SELECT.... PACKAGE SIZE 100.

ENDSELECT.

Regards,

Sesh

Re: better SELECT ? INNER JOIN?

Former Member — Fri, 31 Aug 2007 11:28:38 GMT

Hi,

Never go for nested select or inner join..........it will be a performance issue.It will be better to use for all entries statement.........here selection will be based on the master internal table........database hit will be reduced................

Regards,

Sree.

Re: better SELECT ? INNER JOIN?

VXLozano — Fri, 31 Aug 2007 11:47:51 GMT

Again... read this thread (and much more important, the SDN blogs linked there) about performance of LOOP-SELECT vs FOR ALL ENTRIES vs INNER JOIN

http://sapfans.com/forums/viewtopic.php?p=878030

The LOOP-SELECT SINGLE will rule over the other two in some cases. When the SELECT uses the table key or a database index, the performance can ("can", not "is") be much better than any JOIN or FOR ALL ENTRIES.

Also, for those things you must care about your database tuning.

My advice would be "try it by yourself" for each case/server.

Re: better SELECT ? INNER JOIN?

Former Member — Fri, 31 Aug 2007 12:37:33 GMT

Tnx all !

I will reply when I have a solution!

Adibo..:)

Re: better SELECT ? INNER JOIN?

Former Member — Fri, 31 Aug 2007 14:03:08 GMT

I did some test with INNER JOIN!!

Here are the results:

Runtime: 42.779 microseconds


TABLES: ever, eanl, euitrans, euiinstln, evbs, ehauisu, iflot,
        iloa, adrc, ettifn, dberdl.
   TYPES: BEGIN OF ls_billit,
            euitrans_ext_ui     TYPE euitrans-ext_ui,
            adrc_street         TYPE adrc-street,
            adrc_house_num1     TYPE adrc-house_num1,
            adrc_house_num2     TYPE adrc-house_num2,
            adrc_post_code1     TYPE adrc-post_code1,
            adrc_city1          TYPE adrc-city1,
            evbs_lgzusatz       TYPE evbs-lgzusatz,
*            ettifn_string3      TYPE ettifn-string3,
* Type factuur vertalen naar bepaalde teksten vandaar type string
            ettifn_string3(3),
            erdk_opbel          TYPE erdk-opbel,
            erdk_bldat          TYPE erdk-bldat,
*           erdk_ergrd          TYPE erdk-ergrd,
* Type factuur vertalen naar bepaalde teksten vandaar type string
            erdk_ergrd          TYPE string,
            erdk_abwbl          TYPE erdk-abwbl,  "verzamelvactuurnummer
            dberdl_ab           TYPE dberdl-ab,
            dberdl_bis          TYPE dberdl-bis,  "13
            erdz_hvorg          TYPE erdz-hvorg,
            erdz_tvorg          TYPE erdz-tvorg,
            erdz_i_abrmenge(31) TYPE c,"erdz-i_abrmenge,
            erdz_massbill       TYPE erdz-massbill,
            erdz_preisbtr(17)   TYPE c,"erdz-preisbtr,
            erdz_nettobtr(13)   TYPE c, "erdz-nettobtr,
            erdz_stprz          TYPE erdz-stprz,
            erdz_belzart        TYPE erdz-belzart,
       END OF ls_billit.

TYPES: tt_billit TYPE TABLE OF ls_billit.

DATA: lt_billit TYPE TABLE OF ls_billit,
      wa_billit TYPE  ls_billit.

DATA: lt_billit_0 TYPE TABLE OF ls_billit,
      wa_billit_0 TYPE  ls_billit.

DATA: lt_billit_down TYPE TABLE OF ls_billit,
      wa_billit_down_0 TYPE  ls_billit,
      wa_billit_down TYPE  ls_billit.

DATA: it_dberdl TYPE TABLE OF dberdl,
      wa_dberdl TYPE dberdl.

DATA: it_erdz TYPE TABLE OF erdz,
      wa_erdz TYPE erdz.

DATA: it_erdz_0 TYPE TABLE OF erdz,
      wa_erdz_0 TYPE erdz. 

do 150 times.

   SELECT adrc~street
           adrc~house_num1
           adrc~house_num2
           adrc~post_code1
           adrc~city1
           evbs~lgzusatz
    INTO  (wa_billit-adrc_street,
           wa_billit-adrc_house_num1,
           wa_billit-adrc_house_num2,
           wa_billit-adrc_post_code1,
           wa_billit-adrc_city1,
           wa_billit-evbs_lgzusatz)
    FROM
    evbs
    INNER JOIN iflot ON
               iflot~tplnr = evbs~haus
    INNER JOIN iloa ON
               iloa~tplnr = iflot~tplnr
    INNER JOIN adrc ON
               adrc~addrnumber = iloa~adrnr
    WHERE vstelle = '0000000631'.
    ENDSELECT.

enddo.

Runtime: 36.411 microseconds


   TYPES: BEGIN OF ls_billit,
            euitrans_ext_ui     TYPE euitrans-ext_ui,
            adrc_street         TYPE adrc-street,
            adrc_house_num1     TYPE adrc-house_num1,
            adrc_house_num2     TYPE adrc-house_num2,
            adrc_post_code1     TYPE adrc-post_code1,
            adrc_city1          TYPE adrc-city1,
            evbs_lgzusatz       TYPE evbs-lgzusatz,
*            ettifn_string3      TYPE ettifn-string3,
* Type factuur vertalen naar bepaalde teksten vandaar type string
            ettifn_string3(3),
            erdk_opbel          TYPE erdk-opbel,
            erdk_bldat          TYPE erdk-bldat,
*           erdk_ergrd          TYPE erdk-ergrd,
* Type factuur vertalen naar bepaalde teksten vandaar type string
            erdk_ergrd          TYPE string,
            erdk_abwbl          TYPE erdk-abwbl,  "verzamelvactuurnummer
            dberdl_ab           TYPE dberdl-ab,
            dberdl_bis          TYPE dberdl-bis,  "13
            erdz_hvorg          TYPE erdz-hvorg,
            erdz_tvorg          TYPE erdz-tvorg,
            erdz_i_abrmenge(31) TYPE c,"erdz-i_abrmenge,
            erdz_massbill       TYPE erdz-massbill,
            erdz_preisbtr(17)   TYPE c,"erdz-preisbtr,
            erdz_nettobtr(13)   TYPE c, "erdz-nettobtr,
            erdz_stprz          TYPE erdz-stprz,
            erdz_belzart        TYPE erdz-belzart,
       END OF ls_billit.

TYPES: tt_billit TYPE TABLE OF ls_billit.

DATA: lt_billit TYPE TABLE OF ls_billit,
      wa_billit TYPE  ls_billit.

DATA: lt_billit_0 TYPE TABLE OF ls_billit,
      wa_billit_0 TYPE  ls_billit.

DATA: lt_billit_down TYPE TABLE OF ls_billit,
      wa_billit_down_0 TYPE  ls_billit,
      wa_billit_down TYPE  ls_billit.

DATA: it_dberdl TYPE TABLE OF dberdl,
      wa_dberdl TYPE dberdl.

DATA: it_erdz TYPE TABLE OF erdz,
      wa_erdz TYPE erdz.

DATA: it_erdz_0 TYPE TABLE OF erdz,
      wa_erdz_0 TYPE erdz. 
  DATA: lv_ever-anlage        TYPE ever-anlage,
        lv_ettifn-string3     TYPE ettifn-string3,
        lv_eanl-anlage        TYPE eanl-anlage,
        lv_eanl-vstelle       TYPE eanl-vstelle,
        lv_euiinstln-int_ui   TYPE euiinstln-int_ui,
        lv_evbs-haus          TYPE evbs-haus,
        lv_ehauisu-haus       TYPE ehauisu-haus,
        lv_iflot-tplnr        TYPE iflot-tplnr,
        lv_iloa-adrnr         TYPE iloa-adrnr.

do 150 times.

* adresgegevens ophalen
    SELECT SINGLE haus
           INTO   lv_evbs-haus
           FROM   evbs
           WHERE  vstelle = '0000000631'.
    IF sy-subrc = 0.
* Deze stap nodig, omdat we HAUS al beschikbaat hebben in de tabel EVBS
* dus waarom ook nog tabel ehauisu benaderen?
*      SELECT SINGLE haus
*             INTO   lv_ehauisu-haus
*             FROM   ehauisu
*             WHERE  haus = lv_evbs-haus.
*      IF sy-subrc = 0.
      SELECT SINGLE tplnr
             INTO   lv_iflot-tplnr
             FROM   iflot
             WHERE  tplnr = lv_evbs-haus.
      IF sy-subrc = 0.
        SELECT SINGLE adrnr
               INTO   lv_iloa-adrnr
               FROM   iloa
               WHERE  tplnr = lv_iflot-tplnr.
        IF sy-subrc = 0.
* Nu we adresnummer hebben gaan we het adres ophalen
          SELECT SINGLE *
                 FROM   adrc
                 WHERE  addrnumber EQ lv_iloa-adrnr.

          IF sy-subrc EQ 0.

            wa_billit-adrc_street      = adrc-street.
            wa_billit-adrc_house_num1  = adrc-house_num1.
            wa_billit-adrc_house_num2  = adrc-house_num2.
            wa_billit-adrc_post_code1  = adrc-post_code1.
            wa_billit-adrc_city1       = adrc-city1.
            wa_billit-evbs_lgzusatz    = evbs-lgzusatz.

          ENDIF."adrc
        ENDIF."iola
      ENDIF."iflot
*      ENDIF."ehauisu
    ENDIF."evbs

enddo.

So SELECT SINGLE is here FASTER then INNER JOIN?!

I'll try with FOR ALL ENTERIES

Adibo.

Re: better SELECT ? INNER JOIN?

Former Member — Fri, 31 Aug 2007 14:10:48 GMT

Remember to execute each multiple times and take the lowest from each.

Rob