Jun 20, 2013 at 10:49 AM

Bad performance SQL statement in MaxDB

39 Views

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

Attachments

K810025_indices.JPG (24.6 kB)

K810025_execution_plan.JPG (41.8 kB)

K810025_db05.JPG (43.1 kB)