topic Re: SQL-Performance Problem in Application Development and Automation Discussions

SQL-Performance Problem

Private_Member_19084 — Wed, 10 Oct 2012 15:18:36 GMT

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

Re: SQL-Performance Problem

Former Member — Wed, 10 Oct 2012 15:27:27 GMT

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

Re: SQL-Performance Problem

Former Member — Wed, 10 Oct 2012 16:37:11 GMT

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,

Re: SQL-Performance Problem

MariaJooRocha — Wed, 10 Oct 2012 16:39:46 GMT

Hi,

Use TC ST05.

Regards,

Maria João Rocha

Re: SQL-Performance Problem

MariaJooRocha — Wed, 10 Oct 2012 16:40:50 GMT

Hi,

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

Regards,

Maria João Rocha

Re: SQL-Performance Problem

yuri_ziryukin — Thu, 11 Oct 2012 06:18:03 GMT

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.

Re: SQL-Performance Problem

Private_Member_19084 — Thu, 11 Oct 2012 07:06:39 GMT

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