topic Re: Performance issue select from cosp in Application Development and Automation Discussions

Performance issue select from cosp

Former Member — Tue, 26 Jun 2012 10:57:15 GMT

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

Re: Performance issue select from cosp

yogendra_bhaskar — Tue, 26 Jun 2012 11:05:56 GMT

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

Re: Performance issue select from cosp

Former Member — Tue, 26 Jun 2012 11:29:05 GMT

hi suskranth sahooo,

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

solved ur problems.

good luck.

Re: Performance issue select from cosp

RaymondGiuseppi — Tue, 26 Jun 2012 11:42:22 GMT

Check activation of index COSP~2 in your system.

Regards,

Raymond

Re: Performance issue select from cosp

Former Member — Tue, 26 Jun 2012 11:52:37 GMT

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

Thanks

Re: Performance issue select from cosp

Former Member — Tue, 26 Jun 2012 12:25:39 GMT

put a condition around this statement.

IF gt_glt0_t[] is not initial.

Select statement.

ENDIF.

And yes, Index number 2 should be active.

Re: Performance issue select from cosp

yuri_ziryukin — Tue, 26 Jun 2012 12:27:31 GMT

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.

Re: Performance issue select from cosp

Former Member — Tue, 26 Jun 2012 13:50:48 GMT

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

Re: Performance issue select from cosp

RaymondGiuseppi — Tue, 26 Jun 2012 14:02:32 GMT

- 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

Re: Performance issue select from cosp

Former Member — Tue, 26 Jun 2012 14:18:50 GMT

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

Re: Performance issue select from cosp

RaymondGiuseppi — Tue, 26 Jun 2012 14:32:17 GMT

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

Re: Performance issue select from cosp

Former Member — Wed, 27 Jun 2012 07:39:27 GMT

Check execution plan at database level

Regards

Roman