how to improve this selects?

[Former Member]

Hi gurus, i need some help with some queries.

The actual situation is this:

SELECT objnr rec_objnr1

FROM cobrb

INTO TABLE gt_cobrb

FOR ALL ENTRIES IN gt_aufk_ob

WHERE objnr EQ gt_aufk_ob-objnr AND

aufnr NE ''.

DELETE ADJACENT DUPLICATES FROM gt_cobrb COMPARING rec_objnr1.

SELECT refbt refbn rfpos objnr gjahr sakto perio wkgbtr

FROM cooi

INTO TABLE gt_cooi

FOR ALL ENTRIES IN gt_cobrb

WHERE objnr EQ gt_cobrb-rec_objnr1 AND

gjahr IN s_gjahr AND

bldat IN s_bldat AND

bukrs EQ pa_bukrs.

The access to COOI is very time consuming so i think to use cursor as follows:

DATA: c1 TYPE cursor,

c2 TYPE cursor.

OPEN CURSOR WITH HOLD c1 FOR

SELECT objnr rec_objnr1

FROM cobrb

FOR ALL ENTRIES IN gt_aufk_ob

WHERE objnr EQ gt_aufk_ob-objnr AND

aufnr NE ''.

OPEN CURSOR WITH HOLD c2 FOR

SELECT refbt refbn rfpos objnr gjahr sakto perio wkgbtr

FROM cooi

FOR ALL ENTRIES IN gt_cobrb

WHERE objnr EQ gt_cobrb-rec_objnr1 AND

gjahr IN s_gjahr AND

bldat IN s_bldat AND

bukrs EQ pa_bukrs.

DO.

FETCH NEXT CURSOR c1 INTO TABLE gt_cobrb PACKAGE SIZE 500.

IF sy-subrc NE 0.

EXIT.

ENDIF.

DELETE ADJACENT DUPLICATES FROM gt_cobrb COMPARING rec_objnr1.

DO.

FETCH NEXT CURSOR c2 APPENDING TABLE gt_cooi PACKAGE SIZE 500

IF sy-subrc <> 0.

EXIT.

ENDIF.

ENDDO.

ENDDO.

CLOSE CURSOR: c1, c2.

but the result is not the same since the acces to COOI not respect the for all entries and read the ENTIRE TABLE!!!

What is going on here?! please help!

Edited by: dasdsadasd dasdsadsad on Nov 18, 2011 2:37 PM

[yuri_ziryukin]

>   SELECT objnr rec_objnr1
>   FROM cobrb
>   INTO TABLE gt_cobrb
>   FOR ALL ENTRIES IN gt_aufk_ob
>   WHERE objnr EQ gt_aufk_ob-objnr AND
>                               aufnr NE ''.
> 
>   DELETE ADJACENT DUPLICATES FROM gt_cobrb COMPARING rec_objnr1.
> 

By the way, don't you have to sort gt_cobrb by rec_objnr1 before deleting adjacent duplicates???

The second select from COOI is slow because it's not supported by the index properly.

COOI has primary key:

MANDT	MANDT	CLNT	3	0	Client
REFBT	CO_REFBTYP	NUMC	3	0	Reference document category (conversion exit; c.f. REFBTYP)
REFBN	CO_REFBN	CHAR	10	0	Reference Document Number
RFPOS	CC_RFPOS	NUMC	5	0	Item number of reference document
RFKNT	CC_RFKNT	NUMC	5	0	Account assignment number of reference document
RFTRM	CC_RFTRM	NUMC	5	0	Deadline item of reference document
RFART	CC_RFART	CHAR	1	0	Reference document type
LIFNR	LIFNR	CHAR	10	0	Account Number of Vendor or Creditor
LEDNR	LEDNR	CHAR	2	0	Ledger for Controlling objects
OBJNR	J_OBJNR	CHAR	22	0	Object number
HRKFT	CO_SUBKEY	CHAR	14	0	CO key subnumber
RFORG	AWORG	CHAR	10	0	Reference Organizational Units
RFTYP	AWTYP	CHAR	5	0	Reference Transaction
RFSYS	AWSYS	CHAR	10	0	Logical system of source document

You don't pass many fields that are before OBJNR in your SELECT statement.

And COOI has also a secondary index "1" with fields:

MANDT Client

LEDNR Ledger for Controlling objects

OBJNR Object number

GJAHR Fiscal Year

WRTTP Value Type

VERSN Version

SAKTO Cost element

HRKFT CO key subnumber

VRGNG CO Business Transaction

VBUND Partner Company ID

PARGB Business Partner's Business Area

BEKNZ Debit/credit indicator

TWAER Transaction Currency

In order to effectively utilize this index you definitely need LEDNR provided in your select. Please check if you can get this field into your WHERE clause somehow. Then the second SELECT will be fast.

Regards,

Yuri

Edited by: Yuri Ziryukin on Nov 18, 2011 2:54 PM

[Former Member]

hi yuri, thanks for the answer, but:

1) i don't have to shot de GT_COBRB because the definition is with this type:

ty_gt_cobrb TYPE SORTED TABLE OF ty_ga_cobrb WITH NON-UNIQUE KEY rec_objnr1.

2) what you said is true but the requierement is to improve without change the functional scope and in the program i haven't more key fields, so i have to deal with that.

do you know what the acces with the cursor read the ENTIRE TABLE??? this make me go crazy =/

what do you think about create a new secondary index with my fields from the select?

thanks.

[volker_borowski2]

Hi,

why do you use a cursor with hold?

You do not seem to need it actually (no commits anywhere).

I have never measured, but since a cursor with hold is doing stuff

in addition to be able to survive a commit, I think it must be more expensive than

a "normal" cursor. As said, never measured, so no idea about the outcome.

Volker

[Former Member]

DELETE ADJACENT DUPLICATES FROM gt_cobrb COMPARING rec_objnr1.
SELECT refbt refbn rfpos objnr gjahr sakto perio wkgbtr
FROM cooi
INTO TABLE gt_cooi
FOR ALL ENTRIES IN gt_cobrb
WHERE objnr EQ gt_cobrb-rec_objnr1 AND
      gjahr IN s_gjahr             AND
      bldat IN s_bldat             AND
      bukrs EQ pa_bukrs.

This sort of question has been asked many times before on the forum. There are two things you need to do:

SORT  gt_cobrb BY rec_objnr1.
DELETE ADJACENT DUPLICATES FROM gt_cobrb COMPARING rec_objnr1

.

Then add LEDNR to the SELECT:

SELECT refbt refbn rfpos objnr gjahr sakto perio wkgbtr
FROM cooi
INTO TABLE gt_cooi
FOR ALL ENTRIES IN gt_cobrb
WHERE lednr EQ'00'                 AND
      objnr EQ gt_cobrb-rec_objnr1 AND
      gjahr IN s_gjahr             AND
      bldat IN s_bldat             AND
      bukrs EQ pa_bukrs.

LEDNR is a fixed value in the domain.

Rob

[yuri_ziryukin]

> Then add LEDNR to the SELECT:
> 
SELECT refbt refbn rfpos objnr gjahr sakto perio wkgbtr
> FROM cooi
> INTO TABLE gt_cooi
> FOR ALL ENTRIES IN gt_cobrb
> WHERE lednr EQ'00'                 AND
>       objnr EQ gt_cobrb-rec_objnr1 AND
>       gjahr IN s_gjahr             AND
>       bldat IN s_bldat             AND
>       bukrs EQ pa_bukrs.
LEDNR is a fixed value in the domain.
> 
> Rob

Well, here is the answer regarding LEDNR.

I checked in my internal test system. All entries of COOI have LEDNR = '00'.

[Former Member]

Yes - it's the only value in the domain.

If the OP does a where used list for the table I think he or she will find that this is how SAP handles it,

Rob

[Former Member]

Hi,

Open cursor, Fetch, Close cursor increase the performace, but in for all entries where condition you are comparing more than one internal table , i mean along with one internal table there are some select options. So, i think this might be the reason to degrade the performace.

SELECT refbt refbn rfpos objnr gjahr sakto perio wkgbtr
FROM cooi
INTO TABLE gt_cooi
FOR ALL ENTRIES IN gt_cobrb
WHERE objnr EQ gt_cobrb-rec_objnr1 AND
gjahr IN s_gjahr AND           " ----------->try to eleminate
bldat IN s_bldat AND            " ----------->
bukrs EQ pa_bukrs.

Hope this will be useful.

Regards,

Phani.

[yuri_ziryukin]

Hi,
> Hope this will be useful.
> 
> Regards,
> Phani.

Well, I think SDN needs an option to deduct points

[ThomasZloch]

Meanwhile, the best answer is to openly challenge the "legends" and misleading information. Actually, the situation is pretty good these days, thanks to the longlasting efforts of Siegfried, Hermann, Rob, and others.

In comparison, take a look at the humble beginnings, especially the "meaningful subject lines"

and here a specific example for a Siegfried treatment, once again one of my favorite subject lines

Thomas

[yuri_ziryukin]

Nice one

[Former Member]

Hi all,

The answer to the question might be simple.

As dasdsadasd dasdsadsad said: the query on COOI is fetching all entries of the table, this gives us the hint that the basic check for not initial is not done.

*IF gt_cobrb IS NOT INITIAL.*
  SELECT refbt refbn rfpos objnr gjahr sakto perio wkgbtr
    FROM cooi
    INTO TABLE gt_cooi
     FOR ALL ENTRIES IN gt_cobrb
  WHERE objnr EQ gt_cobrb-rec_objnr1 AND
               gjahr IN s_gjahr AND
               bldat IN s_bldat AND
               bukrs EQ pa_bukrs.
*ENDIF.*

[VivekG]

Hi,

The performance of your select query on COOI is bad because you neither using primary keys properly nor any primary index has been utilized. The performance of any select query is the best when you utilize as many primary keys as possible. Try to include them by functionally understanding the scenario. Also you can try selecting the records only using primary keys in select query and then delete the records for rest of values.

One more thing which I have observed for open cursor statement is that if you are using for all entries with open cursor and if number records are very large in the internal table used in 'for all entries', then open cursor slows down the select query performance a lot. Kindly check by removing open cursor once.

Please keep us posted about the same.

Thanks,

Vivek

Edited by: guptav_infy on Dec 29, 2011 8:28 AM

[former_member194613]

What do you actually expect from a combination of open cursor - with hold together with a FOR ALL ENTRIES and a DELETE ADJACENT DUPLICATES after every FETCH ....

This combination is more than weird, and I doubt that the result gives anything useful.

The OPEN CURSOR does not improve performance automatically, it is an outdated construct and I have the impression the only fans of it live in this forum. It is rarely use in standard code.

I would only recommend it if you process large amounts of data in blocks of 10.000 and if you update the database (INSERT, UPDATE, DELETE) during the process, i.e. if you use it with hold. I is not clear, whether this is the case here. The package size is too small.

The FOR ALL ENTRIES creates its own blocks (size depend on several system paratemers) and takes care that the result has no duplicates which could originate from the different block sizes. A combination with open cursor blocks and with DELETE ADJACENT becomes quite complicated. I would not recommend it.

=> use a join for the select