cancel
Showing results for 
Search instead for 
Did you mean: 

MaxDB 7.7 bad join performance

0 Kudos

Hello,

after upgrading from MaxDB 7.6 to MaxDB 7.7 we got performance problems executing a sql query. It is a join over 3 tables, nothing special, 7.6 ran the query in less a second, 7.7 needs about 1 minute 50 sec. to comlpete. Running explain against both versions gives the same strategy, I also did a statistics update after the db upgrade. Are there new parameters in 7.7 relating to joins?

Thanks an best regards

Tiberius

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

Hi there,

that description is a nice story but does not allow anybody to analyze anything.

We don't know the query or the execution plans or the execution times or the database setups.

We don't know anything yet to help you with your request.

If you're a SAP customer, facilitate support for this, as the forum software is too restrictive to do any real analysis.

If you're a NON-SAP MaxDB User, please start with checking the database parameters.

As you wrote the execution strategy is the same, then something else seems to be much slower now (e.g. I/O).

regards,

Lars

0 Kudos

that's the query (a little bit simplified, but still as slow as original):


select  [some values]
from table1 e 
  join table2 l on e.prod=l.prod1  
  join table3 a on a.lagerid=l.lagerid  
where e.suppcode='SuppCode'  

and the strategy:


  E TABLE1SUPPCODEIDX           EQUAL CONDITION FOR INDEX                     1362
     SUPPCODE                            (USED INDEX COLUMN)                
  L  TABLE2PROD1IDX               JOIN VIA INDEXED COLUMN                      140840
     PROD1                                   (USED INDEX COLUMN)                
  A  TABLE3LAGERIDIDX           JOIN VIA INDEXED COLUMN                     203620
     LAGERID                                (USED INDEX COLUMN)                
                                            NO TEMPORARY RESULTS CREATED       
                                            RESULT IS COPIED   , COSTVALUE IS                   516

hope that help

Edited by: Tiberius Kuczera on Oct 2, 2009 12:35 PM

Edited by: Tiberius Kuczera on Oct 2, 2009 12:35 PM

lbreddemann
Active Contributor
0 Kudos

> that's the query (a little bit simplified, but still as slow as original):

> select [some values]

I cannot tell what "some values" would be - that's definitively not enough information!

How should anybody tell anything about this (except the remarkable thing that you obviously don't join via the primary keys here...).

Have you checked the db parameter setup?

Are there any DBanalyzer warnings?

Come on - if you want us to help you, then please give some more information to deal with.

regards,

Lars

0 Kudos

I cannot tell what "some values" would be - that's definitively not enough information!

How should anybody tell anything about this (except the remarkable thing that you obviously don't join via the primary keys here...).

it doesn't matter if I do a select a.* , e.* , l.* or a few columns, or for example select only e.id or a.id... it takes the same time. While executing the query, there is also no io activity on the server, only high cpu utilisation.

best regards

Tiberius

Edited by: Tiberius Kuczera on Oct 4, 2009 12:24 AM

lbreddemann
Active Contributor
0 Kudos

Actually it does matter what columns you select, as this changes the access paths evaluation (e.g. an index-only strategy might become possible with different selected columns).

Anyway - as you wrote, with 7.6 the same execution path was chosen and performance was way better.

This makes me believe that the problem is not the optimizer oder the query execution but some other part of the system.

Have you checked the db parameters with the checktool yet?

Have you enabled the DBAnalyzer?

regards,

Lars

0 Kudos

I found the solution for my problem, the indexed column "e.prod" had null values in some rows, after I removed these rows the query is as fast as on MaxDB 7.6... maybe someone can explain this behavior to me?

lbreddemann
Active Contributor
0 Kudos

> I found the solution for my problem, the indexed column "e.prod" had null values in some rows, after I removed these rows the query is as fast as on MaxDB 7.6... maybe someone can explain this behavior to me?

Hmm... we still don't know enough to give you the right answer to that.

See - in the execution plan you posed, this column index wasn't even used.

Therefore the content of this column would only make a difference to the result set and not to the execution of the query.

Sorry - but it's likely that the improved performance has nothing to do with the nulls.

Or can you reproduce the behaviour by reinserting the null values?

If this is the case, then we are very interested to see the full DDL and the full statement text so that we can reproduce the behavior.

regards,

Lars

0 Kudos

when I copy the rows containing null values back into the table "table1", then the queries become very slow, but only when the rows containing nulls in e.prod are in the result set selected by e.suppcode='SuppCode' , queries for an another e.suppcode without nulls in e.prod are still very fast. It seems, that nulls in a indexed column used by a join are not welcome anymore in 7.7

lbreddemann
Active Contributor
0 Kudos

Ok, now this really got my interest!

Can you provide a backup file of your database so that we can reproduce the behavior?

Or can you give us the complete commands to build-up the tables/indexes/data with which you can reproduce the problem?

Based on your description I made some tests, but I wasn't able to make this problem occur.

regards,

Lars