Performance Problem with 7.8

[Former Member]

Hello MaxDB Gurus

We have a strange performance problem with MAXDB 7.8

On our test system we have the same indexes as on our produktion server.

On our production system (MaxDB 7.6) the following command gives us a costresult from 1459.

The performance is OK!

explain select lolohnkopf.stpersonalstammid,stname&', '&stvorname as name,lolohnkopf.lofuerjahr,lolohnkopf.lofuermonat,lohntage.lomonatstag

from lohntage

join lolohnkopf on lohntage.lolohnkopfid = lolohnkopf.lolohnkopfid  and lolohnkopf.stnlstammid = '1416' and lolohnkopf.lofuerjahr = 2011  and lolohnkopf.lofuermonat = 1  

join stpersonalstamm on lolohnkopf.stpersonalstammid = stpersonalstamm.stpersonalstammid

where lotagkz = 'E' 

and not exists   (select lohntageid

       from lohntage as lo2   

       join lolohnkopf as lok2 on lo2.lolohnkopfid = lok2.lolohnkopfid    

       where lok2.stnlstammid = lolohnkopf.stnlstammid     

         and lok2.stpersonalstammid = lolohnkopf.stpersonalstammid     

         and lok2.lofuerjahr = lolohnkopf.lofuerjahr     

         and lok2.lofuermonat = lolohnkopf.lofuermonat     

         and lo2.lomonatstag = lohntage.lomonatstag     

         and lo2.lotagkz not in('E','-','FS'))     

order by lolohnkopf.stpersonalstammid,lolohnkopf.lofuerjahr,lolohnkopf.lofuermonat,lohntage.lomonatstag

Result:

BWALLE    LOLOHNKOPF    LOLOHNKOPFSTNLIDFUERJAFUERMO    EQUAL CONDITION FOR INDEX        105033   

        STNLSTAMMID         (USED INDEX COLUMN)       

        LOFUERJAHR         (USED INDEX COLUMN)       

        LOFUERMONAT         (USED INDEX COLUMN)       

BWALLE    STPERSONALSTAMM    STPERSONALSTAMMID    JOIN VIA KEY COLUMN         11440   

BWALLE    LOHNTAGE    LOHNTAGEKOPFTAGKZDIGITAG    JOIN VIA RANGE OF MULTIPLE INDEXED COL.       1075153   

        LOLOHNKOPFID         (USED INDEX COLUMN)       

        LOTAGKZ         (USED INDEX COLUMN)       

                 NO TEMPORARY RESULTS CREATED       

INTERNAL    TEMPORARY RESULT        EQUAL CONDITION FOR KEY             1   

        LOHNTAGEID         (USED KEY COLUMN)       

            NO STRATEGY NOW (ONLY AT EXECUTION TIME)       

                 RESULT IS COPIED   , COSTVALUE IS          1459   

The same command on our test database (MaxDb 7.8) gives us the following result:

The performance is ugly! (up to 10 minutes!)

    LO2    LOHNTAGEKOPFDIGITAGKZTAG    INDEX SCAN        949085   

            ONLY INDEX ACCESSED       

    LOK2    LOLOHNKOPFID    JOIN VIA KEY COLUMN         97866   

                 NO TEMPORARY RESULTS CREATED       

BWALLE    LOLOHNKOPF    LOLOHNKOPFSTNLIDFUERJAFUERMO    EQUAL CONDITION FOR INDEX         97866   

        STNLSTAMMID         (USED INDEX COLUMN)       

        LOFUERJAHR         (USED INDEX COLUMN)       

        LOFUERMONAT         (USED INDEX COLUMN)       

BWALLE    STPERSONALSTAMM    STPERSONALSTAMMID    JOIN VIA KEY COLUMN         11173   

BWALLE    LOHNTAGE    LOHNTAGEKOPFTAGKZDIGITAG    JOIN VIA RANGE OF MULTIPLE INDEXED COL.        949085   

        LOLOHNKOPFID         (USED INDEX COLUMN)       

        LOTAGKZ         (USED INDEX COLUMN)       

INTERNAL    TEMPORARY RESULT        JOIN VIA RANGE OF MULTIPLE KEY COLUMNS             1   

            TABLE HASHED       

        STNLSTAMMID         (USED COLUMN)       

        STPERSONALSTAMMID         (USED COLUMN)       

        LOFUERJAHR         (USED COLUMN)       

        LOFUERMONAT         (USED COLUMN)       

        LOMONATSTAG         (USED COLUMN)       

                 RESULT IS COPIED   , COSTVALUE IS        588957   

            QUERYREWRITE : APPLIED RULES:       

               SubstituteBushyJoins             1   

               DistinctPushDown             1   

               OptimizePredicates             1   

               PushDownPredicates             1   

               PushDownProjection             1   

               FlattenSubqueries             1   

We have no idear why the execution plan is different and the performance is bad.

On our produktion system we have more records in the needed tables!

Any help welcomed

Regards

Albert

[thorsten_zielke · Accepted solution]

Hi,

this is a known bug in 7.8 which has been fixed per PTS1239179 in MaxDB 7.8.02.24 (version 7.7 and below were not affected).

To work around this, just disable the following QueryRewrite-rule:

"update queryrewriterules set active = 'NO' where  rulename = 'FlattenSubqueries'"

As the FlattenSubqueries rule did not show the expected performance benefit and in addition sometimes would display a wrong result set at selects involving correlated subqueries, we decided to disable that rule automatically starting with 7.8.02.24. Below that version, you either have to update your database or use the above workaround to disable that rule manually.

Thorsten

[Former Member]

Hello Thorsten

Working.

Thank you !!

Best regards

Albert