topic Typical problem: Inner join - 5 tables in Application Development and Automation Discussions

Typical problem: Inner join - 5 tables

Marcal_Oliveras — Thu, 05 Feb 2009 12:14:00 GMT

Hi,

I'm improving a program performance changing a lot of SELECT SINGLES of the old version for INNER JOINS and this kind of changes.

The comparing performance test shows that my program is much more faster in the 95% of the cases that I need. But when the amount of data increases (Because they expand the DAYS parameter to select data). My code performance decreasces too much.

How can I improve that?


  SELECT zshz012~zz0010 zshz012~zz0012 crmm_prpadm_h~guid
        INTO TABLE gt_data
        FROM zshz012 JOIN crmm_prpadm_h ON
        zshz012~parent_guid EQ crmm_prpadm_h~guid
        WHERE
          crmm_prpadm_h~prp_type    EQ gk_tom_pre AND
          crmm_prpadm_h~prp_status  EQ gk_sta_act AND
          zshz012~zz0012            IN so_date.


  IF NOT gt_data[] IS INITIAL.
    SELECT a~product_ref_guid c~zz0012 b~parent_guid
       INTO TABLE gt_roturas
       FROM  ( ( crmm_prp_prod_i AS a JOIN
                 crmm_prpadm_i   AS b
                     ON a~parent_guid = b~guid ) JOIN
                 zsiz012_new AS c
                     ON a~guid EQ c~guid )
       FOR ALL ENTRIES IN gt_data
       WHERE
         b~parent_guid = gt_data-guid.
  ENDIF.

Edited by: Marshal on Feb 5, 2009 1:25 PM

Re: Typical problem: Inner join - 5 tables

Former Member — Thu, 05 Feb 2009 12:50:23 GMT

Hi Marshal,

Do you want to write the query with joins?

or else you can have a look.

SELECT zshz012-zz0010 zshz012-zz0012

INTO TABLE gt_data

FROM zshz012

where zshz012-zz0012 IN so_date.

check not gt_data[] is initial.

select crmm_prpadm_h-guid into table gt_data2

from crmm_prpadm_h

for all entries in gt_data

where crmm_prpadm_h~prp_type EQ gk_tom_pre AND

crmm_prpadm_h~prp_status EQ gk_sta_act.

this is the way u can do it for 1st two joins.

so on and so forth.

Hope this helps.

thanx.

Re: Typical problem: Inner join - 5 tables

Marcal_Oliveras — Thu, 05 Feb 2009 12:54:54 GMT

Thanks Pritha but I don't think that your code improve my performance. You only separated the inner join into 2 select statements

Re: Typical problem: Inner join - 5 tables

Former Member — Thu, 05 Feb 2009 12:58:10 GMT

Yes, you are right. i separated the select statements which enhances the performance.

if you write all separate 4 queries with different selects and "for all entries", you can definitely enhance the code performance.

Thanx

Re: Typical problem: Inner join - 5 tables

Former Member — Fri, 06 Feb 2009 20:07:50 GMT

sorry I don't have the time to look into the details, but how you separate the SELECTs is a bad idea,

you should try to have a join first and then you can add the FOR ALL ENTRIES.

With five table there is some analysis necessary but usually it is possible to find a good solution!