Skip to Content
0
Former Member
Apr 15, 2009 at 03:39 PM

PERFORMANCE IMPROVEMENT for a DB view

681 Views

Hi,

There is around 300000 entries with MDBS and we are having very slow access and low performance.

Following is the query.

ima61v internal table does have only single entry in a sample run.

SELECT wemng menge wepos elikz umrez
             umren matnr werks lgort pstyp retpo            
        FROM  mdbs
        INTO (mdbs-wemng, mdbs-menge, mdbs-wepos, mdbs-elikz,
              mdbs-umrez, mdbs-umren, mdbs-matnr, mdbs-werks,
              mdbs-lgort, mdbs-pstyp, mdbs-retpo)          
        WHERE matnr  EQ ima61v-matnr
          AND werks  EQ ima61v-werks                        
          AND loekz  EQ space
          AND elikz  EQ space
          AND bstyp  IN ('F', 'L').

The following is the ST05 analysis.

Executions - 1

Identical Duration - 0

Records - 0

Time/exec - 21,766,348

Rec/exec. - 0

AvgTime/R. - 21,766,348

MinTime/R. - 21,766,348

Obj. type - MDBS

The SQL explain is as follows.

SELECT STATEMENT ( Estimated Costs = 7 , Estimated #Rows = 1 )

6 TABLE ACCESS BY INDEX ROWID EKET                          
         ( Estim. Costs = 3 , Estim. #Rows = 1 )                                                                                
5 NESTED LOOPS                                          
             ( Estim. Costs = 7 , Estim. #Rows = 1 )                                                                                
3 INLIST ITERATOR                                                                                
2 TABLE ACCESS BY INDEX ROWID EKPO              
                     ( Estim. Costs = 4 , Estim. #Rows = 1 )                                                                                
1 INDEX RANGE SCAN EKPO~1                   
                         ( Estim. Costs = 3 , Estim. #Rows = 1 )   
                         Search Columns: 6                                                                                
4 INDEX RANGE SCAN EKET~0                           
                 ( Estim. Costs = 2 , Estim. #Rows = 1 )           
                 Search Columns: 3

1.The tables are not going for full scan.

2. DB stats are up to date.

3. All indexes show in SQL explain are available at DB

Apart from all these what else we can check to identify what is the problem? if we change the variant for multiple mateirals and if we go for b/g execution it is taking more than 30 min to execute.

and also let me know how to resolve the issue.

Thanks in Advance.

Praneeth