tuning an SQL query on a view

[Former Member]

Hi

I have an SQL that has poor performance when querying a 3 table view.

I have tried to tune the view by adding an additional index but the EXPLAIN plan does not change. In fact, the plan is ONLY using fields specified in the the views join conditions and not the WHERE clause.

I have created an index EKET~Z1 with the column for table (T003)

EINDT

and I have also tried another index EKKO~Z2 with the columns for table T001

BSART

BSTYP

EKORG

The query is as follows:

SQL Statement                                                                                
SELECT                                                                     
    "MANDT" , "EBELN" , "EBELP" , "ETENR" , "EKKO_LOEKZ" , "BSTYP" ,         
    "BSART" , "BUKRS" , "EKORG" , "EKGRP" , "STATU" , "AEDAT" , "ERNAM" ,    
    "BEDAT" , "LIFNR" , "ADRNR" , "WAERS" , "EKPO_LOEKZ" , "EKPO_BSTYP" ,    
    "TXZ01" , "MATNR" , "EMATN" , "WERKS" , "MATKL" , "IDNLF" , "MENGE" ,    
    "MEINS" , "NETPR" , "NETWR" , "PSTYP" , "KNTTP" , "WEPOS" , "ANFNR" ,    
    "PRDAT" , "KONNR" , "KTPNR" , "ELIKZ" , "EREKZ" , "EINDT" , "SLFDT" ,    
    "LPEIN" , "EKET_MENGE" , "EKET_WEMNG" , "BANFN" , "BNFPO" , "MBDAT" ,    
    "WADAT" , "WEBRE" , "FRGRL" , "ZZKONNR" , "ZZKTPNR" , "MAHNZ" ,          
    "MAHN1" , "MAHN2" , "MAHN3" , "INFNR"                                    
  FROM                                                                       
    "ZPO_DETAIL"                                                             
  WHERE                                                                      
    "MANDT" = ? AND "EKORG" = ? AND "BSTYP" = ? AND "BSART" = ? AND          
    "WERKS" = ? AND "EINDT" < ? AND "EKPO_LOEKZ" <> ? AND "ELIKZ" <> ?       
  ORDER BY                                                                   
    "EBELN" , "EBELP" , "ETENR"                                              

VIEW DETAILS

CREATE VIEW ZPO_DETAIL

(MANDT,

EBELN,

EBELP,

ETENR,

EKKO_LOEKZ,

BSTYP,

BSART,

BUKRS,

EKORG,

EKGRP,

STATU,

AEDAT,

ERNAM,

BEDAT,

LIFNR,

ADRNR,

WAERS,

EKPO_LOEKZ,

EKPO_BSTYP,

TXZ01,

MATNR,

EMATN,

WERKS,

MATKL,

IDNLF,

MENGE,

MEINS,

NETPR,

NETWR,

PSTYP,

KNTTP,

WEPOS,

ANFNR,

PRDAT,

KONNR,

KTPNR,

ELIKZ,

EREKZ,

EINDT,

SLFDT,

LPEIN,

EKET_MENGE,

EKET_WEMNG,

BANFN,

BNFPO,

MBDAT,

WADAT,

WEBRE,

FRGRL,

ZZKONNR,

ZZKTPNR,

MAHNZ,

MAHN1,

MAHN2,

MAHN3 )

AS SELECT

T0003."MANDT",

T0003."EBELN",

T0003."EBELP",

T0001."ETENR",

T0002."LOEKZ",

T0002."BSTYP",

T0002."BSART",

T0002."BUKRS",

T0002."EKORG",

T0002."EKGRP",

T0002."STATU",

T0002."AEDAT",

T0002."ERNAM",

T0002."BEDAT",

T0002."LIFNR",

T0002."ADRNR",

T0002."WAERS",

T0003."LOEKZ",

T0003."BSTYP",

T0003."TXZ01",

T0003."MATNR",

T0003."EMATN",

T0003."WERKS",

T0003."MATKL",

T0003."IDNLF",

T0003."MENGE",

T0003."MEINS",

T0003."NETPR",

T0003."NETWR",

T0003."PSTYP",

T0003."KNTTP",

T0003."WEPOS",

T0003."ANFNR",

T0003."PRDAT",

T0003."KONNR",

T0003."KTPNR",

T0003."ELIKZ",

T0003."EREKZ",

T0001."EINDT",

T0001."SLFDT",

T0001."LPEIN",

T0001."MENGE",

T0001."WEMNG",

T0001."BANFN",

T0001."BNFPO",

T0001."MBDAT",

T0001."WADAT",

T0003."WEBRE",

T0002."FRGRL",

T0003."ZZKONNR",

T0003."ZZKTPNR",

T0003."MAHNZ",

T0003."MAHN1",

T0003."MAHN2",

T0003."MAHN3"

FROM

SAPCEP."EKET" T0001,

SAPCEP."EKKO" T0002,

SAPCEP."EKPO" T0003

WHERE

T0002."MANDT" = T0003."MANDT" AND

T0002."EBELN" = T0003."EBELN" AND

T0003."MANDT" = T0001."MANDT" AND

T0003."EBELN" = T0001."EBELN" AND

T0003."EBELP" = T0001."EBELP"

EXPLAIN PLAN

-

---

Execution Plan for SQL Optimizer
                                                                                TABLENAME     COLUMN OR INDEX                STRATEGY                                     PAGECOUNT                                                                                
T0003                                                             RANGE CONDITION FOR KEY         15758     
                         MANDT                                         (USED KEY COLUMN)    
                              
T0001                                                             JOIN VIA MULTIPLE KEY COLUMNS  8632     
                         MANDT                                          (USED KEY COLUMN)                                  
                         EBELN                                           (USED KEY COLUMN)     
                             
T0002                                                             JOIN VIA MULTIPLE KEY COLUMNS   40109     
                         MANDT                                          (USED KEY COLUMN)                                  
                         EBELN                                           (USED KEY COLUMN)                                  
                         EBELP                                           (USED KEY COLUMN)                                                                                
NO TEMPORARY RESULTS CREATED                    
SHOW                                                               RESULT IS COPIED   , COSTVALUE IS   31186   
SHOW                                                          QUERYREWRITE - APPLIED RULES:                           
                   SHOW                                                             MergeFromSelectOrView                          1                                                                                

The statistics are up-to-date.

Any tips would be welcome.

Thanks

Doug

[lbreddemann]

> I have created an index EKET~Z1 with the column for table (T003)

> EINDT

>

> and I have also tried another index EKKO~Z2 with the columns for table T001

> BSART

> BSTYP

> EKORG

>

SQL Statement                                                                
>                                                                              
>   SELECT                                                                     
>     "MANDT" , "EBELN" , "EBELP" , "ETENR" , "EKKO_LOEKZ" , "BSTYP" ,         

>   FROM                                                                       
>     "ZPO_DETAIL"                                                             
>   WHERE                                                                      
>     "MANDT" = ? AND "EKORG" = ? AND "BSTYP" = ? AND "BSART" = ? AND          
>     "WERKS" = ? AND "EINDT" < ? AND "EKPO_LOEKZ" <> ? AND "ELIKZ" <> ?       
>   ORDER BY                                                                   
>     "EBELN" , "EBELP" , "ETENR"                                              
> 

> EXPLAIN PLAN

> -

---

>

> Execution Plan for SQL Optimizer
>                                                                                
> TABLENAME     COLUMN OR INDEX                STRATEGY                                     PAGECOUNT      
>                                                                                
> T0003                                                             RANGE CONDITION FOR KEY         15758     
>                          MANDT                                         (USED KEY COLUMN)    
>                               
> T0001                                                             JOIN VIA MULTIPLE KEY COLUMNS  8632     
>                          MANDT                                          (USED KEY COLUMN)                                  
>                          EBELN                                           (USED KEY COLUMN)     
>                              
> T0002                                                             JOIN VIA MULTIPLE KEY COLUMNS   40109     
>                          MANDT                                          (USED KEY COLUMN)                                  
>                          EBELN                                           (USED KEY COLUMN)                                  
>                          EBELP                                           (USED KEY COLUMN)      
>                             
>                                                                          NO TEMPORARY RESULTS CREATED                    
> SHOW                                                               RESULT IS COPIED   , COSTVALUE IS   31186   
> SHOW                                                          QUERYREWRITE - APPLIED RULES:                           
>                    SHOW                                                             MergeFromSelectOrView                          1     
>                                                                                
> 

> The statistics are up-to-date.

> Any tips would be welcome.

Hi Doug,

here we go...

What MaxDB version are you using? Did you check whether the DB parameters all comply to the recommendations?

If so, what are the cardinalities of the columns you indexed (a.k.a. how many different values do each of them contain?)

If the specification of BSART, BSTYP and EKORG does not reduce the number of rows to be retrieved to a large extent, then the indexaccess is likely to produce additional work.

Anyhow, the join order looks a bit odd here - this might be the problem caused by having set OPTIMIZE_OPERATOR_JOIN_COSTFUNC = YES in a MaxDB version between 7.6.04 and 7.6.06.

So make sure it's set to NO (see note 814704 MaxDB Version 7.6 parameter settings for OLTP/BW).

regards,

Lars