Read-only Alert

SAP Community Groups will be in read-only mode from 2AM EST/ 8AM CEST on Saturday April 1st until 2:15PM EST/8:15PM CEST Saturday April 1st. During this time no engagement activities will be available on SAP Community Groups.

Jun 20, 2013 at 10:49 AM
# Bad performance SQL statement in MaxDB

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