select statements taking huge time in production

[Former Member]

Hi Experts,

below select statement taking 3 hours in production.How can I modify it?

please advice me.

select * from dfkkop into corresponding fields of table t_dfkkop

where vtref like 'EPC%' and

( ( augbd = '00000000' and

xragl = 'X' )

or

( augbd ge w_clrfr and

augbd le w_clrto ) ) and

augrd ne '03' and

zwage_type in s_wtype.

regards,

sam.

[former_member181995]

select * from dfkkop into table t_dfkkop"remove corresponding fields of 
where vtref like 'EPC%' and
( ( augbd = '00000000' and
xragl = 'X' )
or
( augbd bt w_clrfr and w_clrto "use between instead of le and ge 
 ) ) and
augrd ne '03' and
zwage_type in s_wtype.

[Former Member]

Hello

Do not use NE in select statement. This much slows working the program.

Instead NE you may to use RANGES with list of values.

[Former Member]

Hi,

In the select statement how can we write RANGES statement for AUGRD ne 03

and How we can modify "VTREF LIKE ' EPC%'

Plese guide me with coding

regrds,

Sam.

[Former Member]

dont use * and corresponding in select query trace thrrough SQL trace st05

[Former Member]

Hi,

The Table DFKKOP contains 142 fields as per provided by SAP.

(A) check whether how many fields are required in the program where you are using the query.if all 142 fields are required

then use '*' else put the field name.

(B) if all the fields are not required fetch only selected fields and create the proper struture of internal table

t_dfkkop and follow the sequence of fetch so that you can remove 'CORRESPONDING FIELDS OF TABLE'

(c) and the last complexity in the select query is 'OR' condition. For rectifying that use BETWEEN OR IN for the field augbd.

<removed_by_moderator>

Thanks,

Bye

Edited by: Julius Bussche on Aug 29, 2008 3:26 PM

[former_member194613]

Two simple questions: How many records are in your dfkkop table?

Which index is supposed to be used for this query?

If the answer on the first question is a large number, and the second answer is 'no index', then you can use the above mentioned recommendations and hope that the time goes down to two and half hours

The change of '*' into a field list help most.

A new index would help of course much much more.

Siegfried

[Former Member]

First, try to find out where the 3 hours is spent, using SE30.

If the bulk of the time is spent in database(I put my money here), then you have to tune the query itself.

select * from dfkkop into corresponding fields of table t_dfkkop

where vtref like 'EPC%' and <----

---

is a performance killer !!!

( ( augbd = '00000000' and

xragl = 'X' )

or

( augbd ge w_clrfr and

augbd le w_clrto ) ) and

augrd ne '03' and <--- too hurts performance

zwage_type in s_wtype.

You will very likely see that the database does a full table scan. Until you rewrite the query to use an appropriate index, your performance problems will remain.

[Former Member]

Hi,

In the select statement how can we write RANGES statement for AUGRD ne 03

and How we can modify "VTREF LIKE ' EPC%'

Please guide me with coding.

regrds,

Sam.

[Former Member]

In the select statement how can we write RANGES statement for AUGRD ne 03

and How we can modify "VTREF LIKE ' EPC%'

Plese guide me with coding

regrds,

Sam.

[former_member194613]

> where vtref like 'EPC%' and <----

---

is a performance killer !!!

... no, there is no performance killer because there is no index ... performance is dead can't be killed anymore!

Without index you can change the SELECT in whatever respect you want it will not improve.

There is no fitting index in the standard is there one in your system, if yes please tell us!

Siegfried