SQL-Performance Problem

[Private_Member_19084]

Hi experts,

I've a question.

How can I analyze the performance of an database table and there indizes?

We have the following sql statement:

      SELECT SINGLE * FROM aufk
                   WHERE aufnr EQ is_rm-aufnr
                     AND auart EQ 'PP01' OR auart EQ 'PP02'.

And it seems to be very slow.

If I make the same access (with same aufnr) via SE16 it runs perfect. But in an abap program it is very slow.

It seems, as this problem occurs, since we have activated database compression.

We use oracle 11.

Any ideas, how to check the table, if the indizes are ok?

Thx in advance

[Former Member]

Hi,

I would suggest you to modify the select statement like

SELECT SINGLE * FROM aufk

WHERE aufnr = ...

AND ( auart = 'PP01' OR auart = 'PP02' ).

This brackets may help in getting the statement to be more precise.

Regards,

Hardik Mehta

[yuri_ziryukin]

When I look at the ABAP statement this recommendation seems to be the most relevant one

I'm afraid the original ABAP statement is understood by the system like:

      SELECT SINGLE * FROM aufk
                   WHERE  ( aufnr EQ is_rm-aufnr
                     AND auart EQ 'PP01' ) OR auart EQ 'PP02'.

And in that case the system separately looks for all orders of type PP02 using only a client in one of the indexes or even a full table scan.

[Private_Member_19084]

oh dammit, I am an idiot.

I searched for performance problems and so on, and the problem is so simple.

Sorry that I've overlooked at this.

Many thx for fast help and tips, to everyone

[Former Member]

Hi Christian,

You are using the primary key to select so the primary unique index should be picked up automatically. Still you might want to check that in SQL Trace ( ST05 ). Incase you see that the correct index is not picked up( Though It is quite unlikely) then you can try passing the AUART in a range table using two lines as SIGN = 'I' and OPTION = 'EQ' and LOW as AUART.

Do the same thing via SE16 along with both AUFNR and AUART filled up and SQL trace running on.

Regards,

R

[MariaJooRocha]

Hi,

Use TC ST05.

Regards,

Maria João Rocha

[MariaJooRocha]

Hi,

If you are talking about performance,... avoid "Select *  "

Regards,

Maria João Rocha