Performance issue select from cosp

[Former Member]

Hi

Below select statment giving performance issue.I commented select * ,although getting performance issue .Could u please let

me know How can I improve this select?

SELECT lednr objnr gjahr wrttp versn kstar rkft vrgng  vbund pargb
 beknz twaer perbl wog001 wog002 wog003 wog004 wog005 wog006
wog007 wog008 wog009 wog010 wog011 wog012 timestmp bukrs
INTO TABLE gt_cosp
FROM cosp
FOR ALL ENTRIES IN gt_glt0_t
WHERE bukrs IN tab_bukrs
AND gjahr EQ p_ryear
AND wrttp EQ gc_wrttp_04
AND versn EQ gc_versn_000
AND lednr EQ gc_lednr_00
AND kstar EQ gt_glt0_t-racct.

Moderator Message: Added Code Tags.   Please share more information from the "SQL Trace Details".

Message was edited by: Kesavadas Thekkillath

[yogendra_bhaskar]

Hi Susrikant ,

have u check that gt_glt0_t is not intial , and the parameters with which u are using in where condition are also not initial .

Try preparing a secondary index for table COSP , according to your where condition.

regards ,

Yogendra Bhaskar

[Former Member]

hi suskranth sahooo,

u will select the data as for the sap standard table format.

solved ur problems.

good luck.

[RaymondGiuseppi]

Check activation of index COSP~2 in your system.

Regards,

Raymond

[Former Member]

Hi

Its already activated ..Can we create any other secondary index for this ..pl.. help me if yes how to do it

Thanks

[yuri_ziryukin]

Well, does your index COSP~2 have the following structure?

Index name:
COSP~2

Index fields:

MANDT
BUKRS

GJAHR

WRTTP

VERSN

LEDNR

KSTAR

If yes and it DOES exist on the database level (check that too), then there should not be any performance problems.

[Former Member]

put a condition around this statement.

IF gt_glt0_t[] is not initial.

     Select statement.

ENDIF.

And yes, Index number 2 should be active.

[Former Member]

I think the problem is in the condition:

WHERE bukrs IN tab_bukrs  

If TAB_BUKRS is empty, the SELECT will likely not use the index. Either make sure it has the values you want or fill it with all values from table T001.

Rob

[RaymondGiuseppi]

Or

- if empty range create a single record with a SELECT MIN( bukrs ) MAX( bukrs) INTO (tab_bukrs-low, tab_bukrs-high) FROM T001, tab_bukrs-sign = 'I', tab_bukrs-option = 'BT', APPEND tab_bukrs. (Especially if many company in the system)

- (if no other solution) use a SQL hint to force usage of the index (e.g. %_HINTS ORACLE 'INDEX("COSP~2")' for Oracle)

Regards,

Raymond

[Former Member]

Using a hint to force COSP~2 with no BUKRS likely won't work because BUKRS is the first field in the index. If that index is used with no BUKRS, I don't see how it could help.

Rob

[RaymondGiuseppi]

Oops, i forgot to put an "else" between my two answers (else if not related to empty bukrs table)

[Former Member]

Check execution plan at database level

Regards

Roman