performance of a select statement

[Former Member]

Hi,

The below given select statement is causing a performance issue.

SELECT qmnum qmart

INTO TABLE p_rel_notif

FROM viqmelst AS f

WHERE objnr IN p_interval AND

stat = s_03constant-stat1 AND

NOT EXISTS ( SELECT objnr FROM jest

WHERE objnr = f~objnr AND

stat = s_03constant-stat2 AND

inact = space ) AND

• Exclude Deletion Flag Status

NOT EXISTS ( SELECT objnr FROM jest

WHERE objnr = f~objnr AND

stat = s_03constant-stat3 AND

inact = space ) AND

qmart IN p_qmart AND

• bukrs IN s_bukrs. "21/06/2005 by vmar

iwerk IN s_iwerk. "21/06/2005 by vmar

Given below is the analysis from ST05 transaction.

SELECT

T_00 . "QMNUM" , T_00 . "QMART"

FROM

"VIQMELST" T_00

WHERE

T_00 . "MANDT" = :A0 AND T_00 . "OBJNR" BETWEEN :A1 AND :A2 AND T_00 . "STAT" = :A3 AND NOT EXISTS ( SELECT T_100 . "OBJNR"

FROM

"JEST" T_100

WHERE

T_100 . "MANDT" = :A4 AND T_100 . "OBJNR" = T_00 . "OBJNR" AND T_100 . "STAT" = :A5 AND T_100 . "INACT" = :A6 ) AND NOTEXISTS ( SELECT T_200 . "OBJNR"

FROM

"JEST" T_200

WHERE

T_200 . "MANDT" = :A7 AND T_200 . "OBJNR" = T_00 . "OBJNR" AND T_200 . "STAT" = :A8 AND T_200 . "INACT" = :A9 ) AND T_00 . "QMART" = :A10 AND T_00 . "IWERK" IN ( :A11 , :A12 , :A13 , :A14 , :A15 , :A16 )&

Execution Plan

SELECT STATEMENT ( Estimated Costs = 28 , Estimated #Rows = 0 )

15 FILTER

14 NESTED LOOPS ( Estim. Costs = 28 , Estim. #Rows = 1 ) Estim. Bytes: 104

12 NESTED LOOPS ( Estim. Costs = 28 , Estim. #Rows = 1 ) Estim. Bytes: 89

9 NESTED LOOPS ( Estim. Costs = 27 , Estim. #Rows = 1 )

Estim. Bytes: 58

6 TABLE ACCESS BY INDEX ROWID JEST

( Estim. Costs = 27 , Estim. #Rows = 1 )

Estim. Bytes: 27

5 INDEX RANGE SCAN JEST~0

( Estim. Costs = 267 , Estim. #Rows = 9 )

Search Columns: 3

2 TABLE ACCESS BY INDEX ROWID JEST

( Estim. Costs = 1 , Estim. #Rows = 1 )

Estim. Bytes: 27

1 INDEX UNIQUE SCAN JEST~0

( Estim. Costs = 2 , Estim. #Rows = 1 )

Search Columns: 3

4 TABLE ACCESS BY INDEX ROWID JEST

( Estim. Costs = 1 , Estim. #Rows = 1 )

Estim. Bytes: 27

3 INDEX UNIQUE SCAN JEST~0

( Estim. Costs = 2 , Estim. #Rows = 1 )

Search Columns: 3

8 TABLE ACCESS BY INDEX ROWID QMEL

( Estim. Costs = 1 , Estim. #Rows = 1 )

Estim. Bytes: 31

7 INDEX RANGE SCAN QMEL~I

( Estim. Costs = 1 , Estim. #Rows = 1 )

Search Columns: 2

11 TABLE ACCESS BY INDEX ROWID QMIH

( Estim. Costs = 1 , Estim. #Rows = 1 )

Estim. Bytes: 31

10 INDEX UNIQUE SCAN QMIH~0 Search Columns: 2

13 INDEX UNIQUE SCAN ILOA~0 Search Columns: 2 Estim. Bytes: 15

Can anyone tell me how to check the performance of this select statement?

[Former Member]

Hi,

before you change the SQL statement: Do you have current table statistics on JEST , QMIH and QMEL

? The row estimates seem a little bit strange.

You can think of replacing NOT EXISTS with NOT IN but be aware of the table sizes in the "outer" and "inner" query:

• if your outer query is "big" and the inner query is "small", not in is generally more

efficient then NOT EXISTS.

• If your outer query is "small" and the inner query is "big" -- a NOT EXISTS can be

quite efficient.

I would do an SQL trace to measure the access. You can set a trace filter (username, tables involved , etc)

and check the runtime and other stuff:

Here are Siegfrieds Blogs:

bye

yk

[Former Member]

I got the impression from recent postings that you ignore my answers and also the information which is in the answers, so I can save my time. I don't know hhow often the SQL Trace was recommended to you.

[Former Member]

Hi Siegfried,

oops - me too, I guess.

If one would invest some time in reading the blogs and other docs at SDN and would PRACTICE it afterwards.

Unfortunatley , there is no FAST = TRUE parameter and we have to say: There is only this hard way to learn.

bye

yk

[Former Member]

Hi Siegfried/yukon,

Your answers are really very helpful to me.Please dont stop answering to my threads.

Thanks.

[Former Member]

OK guys get realistic.

NOT is not a good thing to have in your selects, unless the field checked is part of an index and starts the index...

Possibilities to mitigate this.

Get a list of the keys you are contemplating looking at ( get scope)

Check other table for hits in scope.

Delete from your original scope (internal table of keys) the subset that was found to exist in the sub table.

Finally select the fields you need (read only needed fields) passing your reduced scope table (containing only keys / index fields).

This could speed things up by an order of magnitude.

Have fun.

[Former Member]

Do not use nested select twice with NOT EXISTS condition.

Instead use select to fetch into one internal table.

Then use the selected entries in FOR ALL ENTRIES to query second table.

[Former Member]

Hi,

this is not the solution for the root cause: Insufficient execution plan due to invalid statistics or improper use of the NOT EXISTS:

DO 100 TIMES.

  write: NOT EXISTS / NOT IN is not always evil.
  write: FAE is not always good.

ENDDO.

First, you have to speed up the access to the tables, then you can think of moving some processing to

the application server if it makes sense. I.e. if your application server is the bottleneck you want to keep the processing in the database.

Be aware that FAE just combines the internal table entries into OR conditions (or UNIONS) depending on rsdb parameters (see SAP Note 48230) what will kill you if you have a lot of entries in the "inner" table.

It makes perfectly sense to express a SELECT problem in less statements as possible, because the database can optimize the access in a way that you can't. What you try to do is to be smarter than the optimizer of the database - this MAY work in 1% of all cases, but in 99% you will loose.

bye

yk