topic Re: Efficient Sql Query in Application Development and Automation Discussions

Efficient Sql Query

Former Member — Fri, 25 Feb 2005 16:36:06 GMT

Hi All,

I want to extract header details from table1 and according to that some deatiled fields from table2.

So without using Innerjoin and nested Sql statements what are the rules to write an efficient sql query?

Thank you,

Kelly.

Re: Efficient Sql Query

RichHeilman — Fri, 25 Feb 2005 16:40:56 GMT

My first choice would be an interjoin, what since you are looking for something else, maybe you could select you header data into itab a, then do a "select for all entries in itab a" from the second table into itab b.

Regards,

Rich Heilman

Re: Efficient Sql Query

RichHeilman — Fri, 25 Feb 2005 16:50:04 GMT

Here is an example, i still think that an inner join would be more efficient.



report zrich_0004
       no standard page heading.

data: begin of imara occurs 0,
      matnr type mara-matnr,
      mtart type mara-mtart,
      end of imara.

data: begin of imarc occurs 0,
      matnr type marc-matnr,
      werks type marc-werks,
      end of imarc.

* Selection Screen
selection-screen begin of block b1 with frame title text-001 .
select-options: s_matnr for imara-matnr .
selection-screen end of block b1.

start-of-selection.

  select * into corresponding fields of table imara
           from mara
                   where matnr in s_matnr.

  sort imara ascending by matnr.

  check not imara[] is initial.

  select * into corresponding fields of table imarc
           from marc
             for all entries in imara
                   where matnr = imara-matnr.


  loop at imara.
    write:/ imara-matnr, imara-mtart.
    loop at imarc where matnr = imara-matnr.
      write:/ imarc-matnr, imarc-werks.
    endloop.
  endloop.

Regards,

Rich Heilman

Re: Efficient Sql Query

Former Member — Fri, 25 Feb 2005 17:03:04 GMT

If these are SAP tables, you can search for an existing data dictionary join using SE84 - Repository Browser. Is there some reason why a join is not good for your requirements?

Re: Efficient Sql Query

Former Member — Mon, 28 Feb 2005 05:22:59 GMT

Hi Kelly,

Have you considered the option of using Extract datasets / field-groups ?

Regards,

Anand Mandalika.

Re: Efficient Sql Query

Former Member — Mon, 28 Feb 2005 06:19:30 GMT

REPORT yanswer .

copy this code and run.

*it is recomended that if you want to extract fields from multiple

*table and there is primary and foeign key relationship b/w tables then

*joins are the best solutions (performance wise).

*here is an other solution.

*we have two table scarr which have following columns.

*carrid (primary key).

*carrname

*currcode

*url

*we have another table spfly which have following columns.

*carrid (foreign key).

*connid

*cityfrom

*cityto.

TABLES: scarr,spfli.

DATA wa_scarr TYPE scarr.

DATA wa_spfli TYPE spfli.

SELECT carrid carrname currcode FROM scarr

INTO CORRESPONDING FIELDS OF wa_scarr.

SELECT carrid connid cityfrom cityto FROM spfli

INTO CORRESPONDING FIELDS OF wa_spfli

WHERE carrid = wa_scarr-carrid.

IF sy-subrc = 0.

WRITE: / wa_scarr-carrid, wa_scarr-carrname, wa_scarr-currcode,

wa_spfli-cityfrom,

wa_spfli-cityto.

ENDIF.

ENDSELECT.

Re: Efficient Sql Query

Former Member — Mon, 28 Feb 2005 06:34:49 GMT

Hi Kelly,

In your case, the most efficient sql query is to use 'FOR ALL ENTRIES.

Ideally Inner join is more efficient when compared to 'FOR ALL ENTRIES' only if there the number of DB tables in the INNER JOIN is less that or equal to 3.

If you want to fetch from say 4 or 5 tables which have primary and foreign key relationship, its better not to go with INNER JOIN bcoz the number of tuples will be more and its not advisable to use it.

hope this gives you added info.

Regards,

Santhosh.

Re: Efficient Sql Query

Former Member — Mon, 28 Feb 2005 07:54:10 GMT

REPORT yanswer .

*here spfli is parent table sflight table.

*spfli has composit primary key on carrid and connid

*and sflight has foreign key on carrid and connid.

TABLES: spfli, sflight.

PARAMETERS pa_carr TYPE spfli-carrid.

PARAMETERS pa_conn TYPE spfli-connid.

DATA wa_sflight TYPE sflight.

SELECT fldate price seatsmax seatsocc FROM sflight

INTO CORRESPONDING FIELDS OF wa_sflight

WHERE carrid = pa_carr AND connid = pa_conn.

WRITE : / wa_sflight-fldate, wa_sflight-price,

wa_sflight-seatsmax, wa_sflight-seatsocc.

ENDSELECT.

Re: Efficient Sql Query

Former Member — Mon, 28 Feb 2005 09:47:13 GMT

REPORT yanswer .

*here spfli is parent table sflight table, spfli has carrid, connid, *cityfrom and cityto column.

*spfli has composit primary key on carrid and connid

*and sflight has foreign key on carrid and connid and has columns *fldate price seatsmax, seatsocc columns.

TABLES: spfli, sflight.

PARAMETERS pa_carr TYPE spfli-carrid.

PARAMETERS pa_conn TYPE spfli-connid.

DATA wa_sflight TYPE sflight.

DATA wa_spfli TYPE spfli.

SELECT SINGLE cityfrom cityto FROM spfli

INTO CORRESPONDING FIELDS OF wa_spfli

WHERE carrid = pa_carr AND connid = pa_conn.

SELECT fldate price seatsmax seatsocc FROM sflight

INTO CORRESPONDING FIELDS OF wa_sflight

WHERE carrid = pa_carr AND connid = pa_conn.

WRITE : / wa_sflight-fldate, wa_sflight-price,

wa_sflight-seatsmax, wa_sflight-seatsocc, wa_spfli-cityfrom,

wa_spfli-cityto.

ENDSELECT.

Re: Efficient Sql Query

Former Member — Mon, 28 Feb 2005 10:34:30 GMT

Hi,

YOu need to check whether your table, which is used in "FOR ALL ENTRIES", is empty or not. It is always a good practice to check the table for intially condition.

Eg : if not itab[] is initial.

select <f1>

<f2>

. ....

into <tab> from <dbtab>

for all entries in itab

where ....

endif.

This will avoid unnecessary processing of select if the table is blank.

Regards,

Vara

Re: Efficient Sql Query

Former Member — Mon, 28 Feb 2005 16:12:09 GMT

Hi All ,

Thank you for all the replies... You all were a great help...

But I have a doubt. My thinking about InnerJoin was that it will cause overhead in the database.. will take more time. Isn't it true.. ?

Kelly

Re: Efficient Sql Query

RichHeilman — Mon, 28 Feb 2005 16:16:04 GMT

Depends on how you code it and a number of other factors. I use them all the time, i've found that performance is better than doing it any other way.

Regards,

Rich Heilman

Re: Efficient Sql Query

Former Member — Thu, 10 Mar 2005 21:38:59 GMT

Hi ,

I would like to know if I had the choice of Innerjoin and extract statements.. which one is more efficient?

Kelly

Re: Efficient Sql Query

Former Member — Thu, 10 Mar 2005 22:26:54 GMT

An innerjoin would be the most efficient way to go. Also more supportable. The extract statement is an older technology that is not used much anymore. Today's ABAPers will understand joins much better than the extract statement.

Re: Efficient Sql Query

ChristianFi — Fri, 11 Mar 2005 07:36:14 GMT

Since extract is a way to handle large data that has already been retrieved from database the question should be: is it more efficient to keep the data in an internal table or to use an extract.

Usually (especially in R2 times) Extract could handle larger amounts of data than internal tables. The data was internally transferred from memory to a kind of dataset (or swap file), sorted and could be retrieved via loop. So memory consumption of the process remained rather low.

Nowadays the meomry management of Operating Systems usually have a more sophisticated and in build memory managment which does the swapping when memory becomes sparse. So in most of the cases Extract (no matter if you want to join different tables or not) should be the second choice. Of course, you can still run into limits of memory but then I prefer rather to redesign the program by reading packages, etc than using extracts.

Christian

Re: Efficient Sql Query

Former Member — Fri, 11 Mar 2005 19:56:53 GMT

Hi All,

You all been great help... I think 'for all entries' is the best way .. ..

thanx

kelly

Re: Efficient Sql Query

Former Member — Sat, 12 Mar 2005 00:45:03 GMT

Hi Kelly,

just a few days ago I have done some ABAP tuning in select over large tables - I still think that inner join is the best way, since the database engine has most accurate information on how to optimize the select (temporary sorting, using indices or not etc.). Be aware that "for all entries" statement will actually open Select statement for every single row in the internal table over which you iterate.

Inner join will open the database cursor only once, so you save some time on the select overhead.

The best way to decide is to use transaction ST05, switch on the SQL trace, and run your program. You will see actual times for each database access.

Regards,

Dawood.

Re: Efficient Sql Query

sergey_korolev — Sat, 12 Mar 2005 21:28:49 GMT

Another small tip, Kelly: try to avoid INTO CORRESPONDING FIELDS OF clause. Certainly it is shorter in writing than accurate specification of all the fields you need, but it cause unnecessary move operations.

Re: Efficient Sql Query

sergey_korolev — Sat, 12 Mar 2005 21:38:17 GMT

Hi Dawood,

Actually, for all entries does not open select for every single row, it processes groups of rows at a time. I used to look into trace list in ST05. Seems that it depends on query buffer size though I do not know for sure.

Re: Efficient Sql Query

Former Member — Sat, 12 Mar 2005 22:29:07 GMT

Hi Sergei,

you are probably right, in my case tables were very large and the system has probably splitted the iteration to many groups. I think the limit is 64kB for the Select statement.

The ABAP documentation says:

"The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set."

It is not explained any further, but from the last quoted sentence it seems that there is an extra load on the application-database interface communication (transferring more data than needed) and some extra load on application server itself (comparing and eliminating unnecessary lines).

Regards,

Dawood.