Bad performance SQL statement in MaxDB

[stefan_galiger]

Hi,

Tranaction ke30 has a very bad performance.

Following SQL statement runs very badly. Our database is Maxdb, version 7.8.02.31.

SELECT
  T_00 . "BESKZ" , T_00 . "BUKRS" , T_00 . "BZIRK" , T_00 . "KAUFN" ,
  T_00 . "KNDNR" , T_00 . "KVGR1" , T_01 . "GJAHR" , T_01 . "PERDE" ,
  T_00 . "PLIKZ" , T_00 . "PRCTR" , T_00 . "VBUND" , T_01 . "VRGAR" ,
  T_00 . "VTWEG" , T_00 . "WERKS" , T_00 . "WWLWE" , T_00 . "WWPH1" ,
  T_00 . "WWPH2" , T_00 . "WWPH3" , T_00 . "WWSGF" , T_00 . "WWVB" ,
  T_00 . "WWWKV" , SUM( T_01 . "VV010" ) "VV010" ,
  SUM( T_01 . "VV020" ) "VV020" , SUM( T_01 . "VV050" ) "VV050" ,
  SUM( T_01 . "VV060" ) "VV060" , SUM( T_01 . "VV061" ) "VV061" ,
  COUNT(*) "RECCOUNT"
FROM
  "K810025" T_00 INNER JOIN "K810026" T_01 ON T_00 . "TRKEYNR" = T_01 . "TRKEYNR
  "
WHERE
  T_01 . "GJAHR" = ? AND T_01 . "PERDE" = ? AND T_00 . "PLIKZ" = ? AND ( T_01 .
  "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" =
  ? AND T_01 . "PERDE" = ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 .
  "PERDE" = ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR T_01 . "VRGAR"
  = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 . "PERDE" = ? AND
  T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 .
  "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" =
  ? AND T_01 . "PERDE" = ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 .
  "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 . "PERDE" = ? AND T_00 . "PLIKZ"
  = ? AND ( T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 .
  "PERDE" BETWEEN ? AND ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 .
  "PERDE" BETWEEN ? AND ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 .
  "PERDE" BETWEEN ? AND ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 .
  "PERDE" BETWEEN ? AND ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 .
  "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 . "PERDE" BETWEEN ? AND ? AND
  T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 .
  "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" =
  ? AND T_01 . "PERDE" BETWEEN ? AND ? AND T_00 . "PLIKZ" = ? AND ( T_01 .
  "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ?
  OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 . "PERDE" = ? AND T_00
  . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 .
  "VRGAR" = ? )
GROUP BY
  T_00 . "BESKZ" , T_00 . "BUKRS" , T_00 . "BZIRK" , T_00 . "KAUFN" ,
  T_00 . "KNDNR" , T_00 . "KVGR1" , T_01 . "GJAHR" , T_01 . "PERDE" ,
  T_00 . "PLIKZ" , T_00 . "PRCTR" , T_00 . "VBUND" , T_01 . "VRGAR" ,
  T_00 . "VTWEG" , T_00 . "WERKS" , T_00 . "WWLWE" , T_00 . "WWPH1" ,
  T_00 . "WWPH2" , T_00 . "WWPH3" , T_00 . "WWSGF" , T_00 . "WWVB" ,
  T_00 . "WWWKV"

In some cases we start the report with different selection, and in this case the statement has following where clause, but the same bad performance:

WHERE
  T_00 . "BUKRS" = ? AND T_00 . "PRCTR" = ? AND T_00 . "WERKS" = ? AND ( T_01 .
  "GJAHR" = ? AND T_01 . "PERDE" = ? AND T_00 . "PLIKZ" = ? AND ( T_01 .
  "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" =
  ? AND T_01 . "PERDE" = ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 .
  "PERDE" = ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR T_01 . "VRGAR"
  = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 . "PERDE" = ? AND
  T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 .
  "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" =
  ? AND T_01 . "PERDE" = ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 .
  "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 . "PERDE" = ? AND T_00 . "PLIKZ"
  = ? AND ( T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 .
  "PERDE" BETWEEN ? AND ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 .
  "PERDE" BETWEEN ? AND ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 .
  "PERDE" BETWEEN ? AND ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 .
  "PERDE" BETWEEN ? AND ? AND T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR
  T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 .
  "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 . "PERDE" BETWEEN ? AND ? AND
  T_00 . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 .
  "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" =
  ? AND T_01 . "PERDE" BETWEEN ? AND ? AND T_00 . "PLIKZ" = ? AND ( T_01 .
  "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ?
  OR T_01 . "VRGAR" = ? ) OR T_01 . "GJAHR" = ? AND T_01 . "PERDE" = ? AND T_00
  . "PLIKZ" = ? AND ( T_01 . "VRGAR" = ? OR T_01 . "VRGAR" = ? OR T_01 .
  "VRGAR" = ? ) )
GROUP BY
  T_00 . "BESKZ" , T_00 . "BUKRS" , T_00 . "BZIRK" , T_00 . "KAUFN" ,
  T_00 . "KNDNR" , T_00 . "KVGR1" , T_01 . "GJAHR" , T_01 . "PERDE" ,
  T_00 . "PLIKZ" , T_00 . "PRCTR" , T_00 . "VBUND" , T_01 . "VRGAR" ,
  T_00 . "VTWEG" , T_00 . "WERKS" , T_00 . "WWGCX" , T_00 . "WWLWE" ,
  T_00 . "WWPH1" , T_00 . "WWPH2" , T_00 . "WWPH3" , T_00 . "WWSGF" ,
  T_00 . "WWVB" , T_00 . "WWWKV"

Execution plans, indices are attached.

K810026 hasn't got an index.

Have you got an idea, how can I improve the performance from this statement? Which index should I create?

Many thanks for your help.

Regards,

Stefan

[Former Member]

Hello Stefan,

explain üplan shows that the Optimizer starts with the Table T00 with a table scan. But the where condition of the first SQL statement shows that we have local predicatedon table T01 -> GJHAR and PERDE

both columns are part of the primary key but they are second and third key field.

I would create an additional index on table K810026 with the columns GJAHR,PERDE

The second statement has local predicates on table T00 -> BUKRS,PRCTR,WERKS

The index information you attached has no index which can be used.

I would create an additional index on table K810025 on the columns BUKRS,PRCTR,WERKS.

Please notice if you are a SAP Entrerprise Support customer SAP offers an service for free to optimize your SQL performance. See note 1007129 for more detailes.

Regards, Christiane

[stefan_galiger]

Hello Christiane,

I've created the index for T01 -> GJAHR, PERDE, activated it and created new statistics.

But the index won't be used.

Can you help me?

Thanks a lot.

Regards, Stefan

[Former Member]

Hi Stefan,

please send the explain plan .

Regards, Christiane

[Former Member]

Hi Stefan,

did you created the second index as well?

K810025 on the columns BUKRS,PRCTR,WERKS.

We have a join of tables and with the second index the local predicates will be used to reduce the number of records which have to be read to get the result.

Regards, Christiane

[Former Member]

Hi Stefan,

is TRKEYNR a key field in both tables?

You do only mention K810025 in your screenshots, what about K810026?

Could you post SE11 for both tables?

Did you try to create an index on K810026-TRKEYNR?

Best regards

Christian

[stefan_galiger]

Hi Christian,

TRKEYNR is a key field in both tables and we haven't got indices on K810025-TRKEYNR ord K810026-TRKEYNR. I have attached se11 from both tables.

K810025:

K810026:

db05 for K810026:

Should I create indices for both tables with TRKEYNR?

Thank you for your answer.

Regards,

Stefan