08-31-2007 10:56 AM
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
08-31-2007 11:33 AM
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 <b>FOR ALL ENTRIES</b> 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 <b>READ</b> 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.
08-31-2007 11:02 AM
Hi
<b>
See this blog,</b>
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
<i>
Reward if the info was useful</i>
08-31-2007 11:06 AM
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
08-31-2007 11:08 AM
Hi Adibo,
Use join statement inplace of nested select.
Select ahaus bhaus ctplnr dadrnr 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
08-31-2007 11:18 AM
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
08-31-2007 11:19 AM
Hi
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
08-31-2007 11:33 AM
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 <b>FOR ALL ENTRIES</b> 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 <b>READ</b> 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.
08-31-2007 11:46 AM
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?
08-31-2007 11:48 AM
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
08-31-2007 11:40 AM
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
08-31-2007 12:28 PM
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.
08-31-2007 12:47 PM
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.
08-31-2007 1:37 PM
08-31-2007 3:03 PM
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.
08-31-2007 3:10 PM
Remember to execute each multiple times and take the lowest from each.
Rob