Modify a SELECT Query on ISU DB tables to improve performance

[Former Member]

Hi Experts,

I have a SELECT query in a Program which is hitting 6 DB tables by means of 5 inner joins.

The outcome is that the program takes an exceptionally long time to execute, the SELECT statement being the main time consumer.

Need your expertise on how to split the Query without affecting functionality -

The Query :

SELECT fkkvkp_{gpart eabl}ablbelnr eabl_{adat eabl}istablart

FROM eabl

INNER JOIN eablg ON eablg_{ablbelnr = eabl}ablbelnr

INNER JOIN egerh ON egerh_{equnr = eabl}equnr

INNER JOIN eastl ON eastl_{logiknr = egerh}logiknr

INNER JOIN ever ON ever_{anlage = eastl}anlage

INNER JOIN fkkvkp ON fkkvkp_{vkont = ever}vkonto

INTO TABLE itab

WHERE eabl~adat GT [date which is (sy-datum - 3 years)]

Thanks in advance,

PD

[Former Member]

Hi,

You need to break your select query. You need values from EABL and business partner name. You can use read statements with binary search to get business partner name.

Select queries could be:

*Get values from EABL

SELECT ablbelnr adat istablart equnr

FROM eabl

into table itab

WHERE adat GT date

*To fetch logical device number from equipment

IF NOT itab is initial.

SELECT equnr logiknr

FROM EGERH

into table it_egerh

for all entries in itab

where equnr = itab-equnr and bis GE sy-datum and ab LE sy-datum.

ENDIF.

*To fetch installation from logical device number

IF it_egerh is not initial.

SELECT anlage logiknr

from eastl

into table it_eastl

for all entries in it_egerh

where logiknr = it_egerh-logiknr and bis GE sy-datum and ab LE sy-datum.

ENDIF.

*To Fetch contract account from installation

IF it_eastl is not initial.

SELECT vertrag anlage vkonto

from ever

into table it_ever

for all entries in it_eastl

where anlage = it_eastl-anlage.

ENDIF.

*To Fetch Business PArtner from Contract Account

IF it_ever is not initial.

select vkont gpart

from fkkvkp

into table it_fkkvkp

for all entries in it_ever

where vkont = it_ever-vkonto.

endif.

Regards,

Avinash

[Former Member]

Hi Avinash,

Thanks for your reply.

Can you tell how I can get the four fields that I need - gpart, ablbelnr, adat and istablart into one table from the above SELECT queries?

Thanks and Regards,

Prajakt

[Former Member]

Hi Prajakt,

I guess you can loop and use modify statement.

Sort it_egerh by equnr.

sort it_eastl by logiknr.

sort it_ever by anlage.

sort it_fkkvkp by vkont.

LOOP at itab into ls_tab.

lv_tabix = sy-tabix.

Read table it_egerh into ls_egerh with key equnr = itab-equnr binary search.

if sy-subrc = 0.

read table it_eastl into ls_eastl with key logkinr = ls_egerh-logiknr binary search.

if sy-subrc = 0.

read table it_ever into ls_ever with key anlage = ls_eastl-anlage binary search.

if sy-subrc = 0.

read table it_fkkvkp into ls_fkkvkp with key vkont = ls_ever-vkonto binary search.

if sy-subrc = 0.

ls_tab-gpart = ls_fkkvkp-gpart.

modify itab from ls_tab index lv_tabix.

endif.

endif.

endif.

endif.

ENDLOOP.

PS - Please check the syntaxs... I just wrote them as free text.

Regards,

Aviansh

[Former Member]

Hi Prajakt

There are a couple of issues with the code provided by Aviansh:

1) Higher Memory consumption by extensive use of internal tables (possible shortdump TSV_NEW_PAGE_ALLOC_FAILED)

2) In many instances multiple SELECT ... FOR ALL ENTRIES... are not faster than a single JOIN statement

3) In the given code the timeslices tables are limited to records active of today, which is not the same as your select (taking into account that you select for the last three years you probably want historical meter/installation relationships as well*)

4) Use of sorted/hashed internal tables instead of standard ones could also improve the runtime (in case you stick to all the internal tables)

Did you create an index on EABL including columns MANDT, ADAT?

Did you check the execution plan of your original JOIN Select statement?

Yep

Jürgen

___

• You should review your selection, because you probably want business partner that was linked to the meter reading at the time of ADAT, while your select doesn't take the specific Contract / Device Installation of the time of ADAT into account.

Example your meter reading is from 16.02.2010

Meter 00001 was in Installation 3000001 between 01.02.2010 and 23.08.2010

Meter 00002 was in Installation 3000001 between 24.08.2010 and 31.12.9999

Installation 3000001 was linked to Account 4000001 between 01.01.2010 and 23.01.2011

Installation 3000001 was linked to Account 4000002 between 24.01.2010 and 31.12.9999

This means with your select returns four lines and you probably want only one.

To achieve that you have to limit all timeslices to the date of EABL-ADAT (selects from EGERH, EASTL, EVER).

___

Update:

Coming back to point one and the memory consumption:

• What are you planning to do with the output of the select statment?

• Did you get a shortdump TSV_NEW_PAGE_ALLOC_FAILED with three years meter reading history?

• Or did you never run on production like volumes yet?

Dependent on this you might want to redesign your program anyway.

Edited by: sattlerj on Jun 24, 2011 10:38 AM

[Former Member]

Hi,

U can break your select query and instead of inner join use SELECT .......FOR ALL ENTRIES......

THIS WILL work and it will take less execution time