Performance issue in production for dfkkko and dfkkop

[former_member206205]

Hi,

I am facing a performance issue when fetching vkont and gpart from dfkkop table.

Iam confused with the following options.

Please suggest me to get better performance for large volumes of data in production.

Option 1:

Using Inner Join:

  SELECT b~opbel

         b~augst

         b~gpart

         b~vkont

         b~bldat

         INTO TABLE it_dfkkop

         FROM dfkkko AS a INNER JOIN dfkkop AS b

         ON a~opbel = b~opbel

         WHERE a~bldat IN s_date

         AND   b~augst = ' '.

Option 2:

For all entries:

  SELECT opbel

         bldat

         FROM dfkkko

         INTO TABLE it_dfkkko

         WHERE bldat IN s_date.

  CHECK it_dfkkko[] IS NOT INITIAL.

  SORT it_dfkkko BY opbel.

SELECT opbel

         augst

         gpart

         vkont

         bldat

         FROM dfkkop

         INTO TABLE it_dfkkop

         FOR ALL ENTRIES IN it_dfkkko

         WHERE opbel = it_dfkkko-opbel

         AND   augst = ' '.

Thanks & Regards

Jayasree

[raymond_giuseppi]

First execute a SQL Trace (ST05)

Regards,

Raymond

[former_member206205]

Raymond,

I dont have much data in Dev.

But for Inner join its taking 81,827 sec for dfkkko and for 'for all entries' 296 for dfkkko and 776 for dfkkop.

Regards

Jayasree

[raymond_giuseppi]

I hope you have miore data on quality system than in DEV.

- Selection on dfkkko - AFAIK there is no index on BLDAT

- Selection on dfkkop - There is an index on AUGST (DFKKOP~1)

- No database view found on my system which could be useful.

Assuming that most positions are cleared, you could try to reverse the order in the JOIN or the FOR ALL ENTRIES, selecting first from dfkkop ?

Regards,

Raymond

[former_member206205]

Earlier it was

*  SELECT AUGST

*         GPART

*         VKONT

*         FROM DFKKOP

*         INTO TABLE IT_DFKKOP_TMP

*         WHERE BLDAT IN S_DATE AND

*               AUGST = SPACE.      

This is taking much time.

We do have a index for BLDAT in DFKKKO.

Regards

Vijayasree

[Former Member]

Try to create a secondary index for the fields you use in the where condition and use that secondary index in the select statement, which will improve the performance.

[former_member206205]

I do have a secondary index on BLDAT in DFKKKO.

My question is from the above two options (For all entries / Inner Join ) which one is the better in order to improve performance?

[Former Member]

Moderator message - discussed many, many times. Please search.

Locked.

Rob