Bad performance with inner join

[stefan_galiger]

Hi,

I have the following SQL-statement with a very bad performance:

SELECT

T_00 ."ERSDA" , T_00 . "AUFNR" , T_00 . "VORNR" , T_00 . "PERNR" ,

T_00 . "RUECK" , T_00 . "AUERU" , T_00 . "STOKZ" , T_00 . "STZHL" ,

T_00 . "ILE01" , T_00 . "ILE03" , T_00 ."ISBD" , T_00 . "ISBZ" ,

T_00 . "IEBD" , T_00 . "IEBZ" , T_00 ."SATZA" , T_00 . "LTXA1" ,

T_00 . "ERNAM" , T_00 . "WERKS" , T_00 ."GMNGA" , T_00 . "ISM01" ,

T_00 . "ISM02" , T_00 . "ISM03" , T_00 ."XMNGA" , T_01 . "ARBPL" ,

T_01 . "AUFNR" , T_01 . "VORNR"

FROM

"AFRU" T_00 INNER JOIN "S022" T_01 ON T_01 . "MANDT" = ? AND T_01 . "AUFNR" =T_00 . "AUFNR"

AND T_01 . "VORNR" = T_00. "VORNR"

WHERE

T_00 . "MANDT" = ? AND T_00 . "WERKS" =? AND T_00 . "ERSDA" = ? AND T_00 ."PERNR" = ?

The indices are:

AFRU~Z01: MANDT, PERNR, BUDAT

AFRU~Z02: MANDT, WERKS, ERSDA, PERNR

S022~A: MANDT, SSOUR, VRSIO, WERKS, ARBPL, KAPAR, MATNR, AUFNR, PLNUM, POSNR, PLNFL, VORNR

It seems, that the indices will not be used.

Have you got an idea, how can I improve the performance from this statement? With a new index, or can I rebuild the statement to archive a better performance. It is very urgent.

Thank you very much for you answers.

Regards,

Stefan G.

[Former Member · Accepted solution]

Hello Stefan,

could you please send the explain plan executed via DatabaseStudio or SQlStudio as follows

or directly in St05.

I would expect that the join will be executed via select on the table AFRU using the index AFRU~Z02

and then accesses the second table S022.

Please send additionally the parameter values of te SQL statement. MaxDb is using a cost based optimizer which always sued the best strategies depending on the values in the where condition.

Which database version are you using?

You will find useful information about performance Analysis in our training about SQL Optimizer and in our Performacne guide section in SDN:

http://maxdb.sap.com/training/ session 16 Part 1 and for join part 2

and

https://wiki.sdn.sap.com/wiki/display/MaxDB/SQL+Optimizer

SELECT

T_00 ."ERSDA" , T_00 . "AUFNR" , T_00 . "VORNR" , T_00 . "PERNR" ,

T_00 . "RUECK" , T_00 . "AUERU" , T_00 . "STOKZ" , T_00 . "STZHL" ,

T_00 . "ILE01" , T_00 . "ILE03" , T_00 ."ISBD" , T_00 . "ISBZ" ,

T_00 . "IEBD" , T_00 . "IEBZ" , T_00 ."SATZA" , T_00 . "LTXA1" ,

T_00 . "ERNAM" , T_00 . "WERKS" , T_00 ."GMNGA" , T_00 . "ISM01" ,

T_00 . "ISM02" , T_00 . "ISM03" , T_00 ."XMNGA" , T_01 . "ARBPL" ,

T_01 . "AUFNR" , T_01 . "VORNR"

FROM

"AFRU" T_00 INNER JOIN "S022" T_01 ON T_01 . "MANDT" = ? AND T_01 . "AUFNR" =T_00 . "AUFNR"

AND T_01 . "VORNR" = T_00. "VORNR"

WHERE

T_00 . "MANDT" = '<value>' AND T_00 . "WERKS" = '<value>' AND T_00 . "ERSDA" = '<value>' AND T_00 ."PERNR" = '<value>'

[stefan_galiger · Accepted solution]

Hello Christiane,

I've made two examples, one time with "BETWEEN" in the sql statement and one time without.

The database version is 7.8.02.31.

Thank you for your answer.

Regards,

Stefan Galiger

[Former Member · Accepted solution]

Hello Stefan,

ok now it is clear. The second statement has an equal condition on ERSDA -> so the index AFRU~z02 can be used and this execution is fast.

The first statement has an between condition on ERSDA - MaxDb cannot use the complete index AFRU~z02. The optimizer can use only these columns to the between - means of index AFRU~Z02 columes MANDT,WERKS,ERSDA but not the PERSNR anymore. So the optimizer evaluates if the aacess via another index can be used and evaluates index AFRU~Z01 -> here optimizer uses columes Mandt and PERSNR - looks like the PERSNR reducs the number of rows to be read more then reading index AFRU~z02

How to optimize this statement:

Create an 3. index AFRU~Z03 via the columes PERNR,WERKS,ERSDA

The explain must be as follows

AFRU~Z03      using columns PERSNR,WERKS,ERSDA then accessing the table S022

MANDT must not be part of this index because mandt is always the first key column and MaxDb uses the key columns as seperator in the index - so you always have the key column in the index as well. ( maxDb ios not working qwith rowids). Ok PERSNR seems to be the column with the most distince values (my experieice) put it as the  first index column. Werks is the second equal condition in the stamtement - so put it as second index column, and at last the column with the between condition.

This is the reason why I recommend to create the index with PERSNR,WERKS,ERSDA

The explain will look as follows:

AFRU~Z03

PERSNR

WERKS

ERSDA

MANDT ( use key column)

join via key range to S022 but only using column MANDT.

Ok to optimize the join transition for both statements we have to have a closer look on the join conditions.

Mandt =

AUFNR =

VORNR =

There seems to be no index on S022 with the columns AUFNR,VORNR

Mandt don't need to be part of the index as well.

So please create both indexes as described in my answer - and I'm interesting in the result  ..... could the runtime of both statements be speed up?

Regards, Christiane

[stefan_galiger · Accepted solution]

Hi Christiane,

thank you very much for your answer. I am not a specialist in maxdb, so I want to be sure, that I understood it well.

1) I have to set an index AFRU~Z03: PERSNR, WERKS, ERSDA

2) I have to set an index S022~Z01: AUFNR, VORNR

Shall I also change the select statement? If yes, then how?

Thank you again

Regards,

Stefan

[Former Member · Accepted solution]

Hello Stefafn,

no you don't need to change the SQL statement. The optimizer is checking only the where condition to find the bnest strategy. the qualification is important finding the best index.

Just to be sure you sent the output of index S022~Z01 which is created correctly, but the index definition you sent of AFRU~z03 does not correspond to the index structure which I recommend.

The screen shot shows a different index structure than recommended:

Shown index structure: MANDT,WERKS,ERSDA,PERNR

recommended index structure PERNR,WERS,ERSDA

Notice even when the shown index shows the same columns the order of the index fields is important.

So please create an addtional index with the following order -> PERNR,WERKS,ERSDA

Please do not remove the other indexes which are existing n the table.

Regards, Christiane

[stefan_galiger · Accepted solution]

Hello Christiane,

I've set the indices and it works greatfully. The response time is under 3 seconds.

Thank you very much for your help. It helps me a lot.

Regards,

Stefan

[Former Member · Accepted solution]

fine - with the recordings of the Optimizer expert sessions you'll find more information about how to optimize SQL statements -> http://maxdb.sap.com/training/

Regards, Christiane

[former_member184473]

Hello Stefan,

Are the statistics (note 927882) and parameters (note 1111426) updated?

Regards,

Eduardo Rezende

[stefan_galiger]

Hello Eduardo,

thanks for your answer. The statistics are actual and the parameters are set like suggested in note 1111426.

Have you got any other idea?

Thanks

Regards,

Stefan Galiger

[former_member206552]

Hi Stefan

You can try and do an re-org of the table and index and see if this will help

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70cf010d-0b01-2d10-86a2-a0636c39a...

http://help.sap.com/saphelp_nw04/helpdata/en/1b/4e8f0d38a8f4419436d608a36b6581/frameset.htm

or you can have an look at the execution plan for the query and see where the most overhead and optimizer from there.

Best Regards

Marius

[stefan_galiger]

Hi Marius,

our database is MaxDB, not Oracle.

The execution plan shows me, that the most expensive statement is the inner join statement above.

Thank you for you help.

Regards

Stefan

[Former Member]

Hello Stefan,         

please check my last answer and post the required info please.

The explain output is the important one to check if the optimizer is executing the Inner join correctly.

Thanks,

Christiane