Performance problem(ANEA/ANEP table) in Select statement

[Former Member]

Hi

I am using below select statement to fetch data.

Does the below where statement have performance issue?

can you Pls suggest.

1)In select of ANEP table, i am not using all the Key field in where condition. will it have performance problem?

2)does the order of where condition should be same as in table, if any one field order change also will have effect performance

SELECT bukrs                            
         anln1                           
         anln2                           
         afabe                           
         gjahr                         
         peraf                          
         lnran                          
         bzdat                           
         bwasl                         
         belnr                          
         buzei                          
         anbtr                        
         lnsan                          
    FROM anep
    INTO TABLE o_anep
    FOR ALL ENTRIES IN i_anla
   WHERE bukrs = i_anla-bukrs           
     AND anln1 = i_anla-anln1           
     AND anln2 = i_anla-anln2           
     AND afabe IN s_afabe               
     AND bzdat =< p_date                 
     AND bwasl IN s_bwasl.               


  SELECT bukrs       
         anln1       
         anln2       
         gjahr       
         lnran        
         afabe       
         aufwv        
         nafal    
         safal        
         aafal       
         erlbt     
         aufwl       
         nafav     
         aafav      
         invzv    
         invzl       
    FROM anea
    INTO TABLE o_anea
    FOR ALL ENTRIES IN o_anep
   WHERE bukrs = o_anep-bukrs     
     AND anln1 = o_anep-anln1     
     AND anln2 = o_anep-anln2     
     AND gjahr = o_anep-gjahr     
     AND lnran = o_anep-lnran    
     AND afabe = o_anep-afabe.

Moderator message:

Edited by: Thomas Zloch on Aug 9, 2011 9:37 AM

[Former Member]

Hi,

1. If you use all the keys or if you use particular keys, it wont be having the performance issue. when you execute the Huge volume of data in a one go it will give the performance issue.

For not getting the dump in the program you can use SELECT ENDSELECT or you can use Package into your code.

2. yes obviously, the order should be same as the table,and if it is not it might affect your performance.

And small thing, try to write the fields in a single line, you can save some time. When you write the field name one by one, the compiler will read line by line in your code. if you have atleast 5 to 6 fields in a line,the compiler can easily read the fields at one go.

And please let me know if you have anything further.

Thanks and Regards,

Ahamed Anish S.

[Former Member]

Hi Anish

Thanks for your reply.

And small thing, try to write the fields in a single line, you can save some time.

You mean to say fields in where condition also should be same line?

pls confirm.

Regards

Chandra

[Former Member]

1. Yes. If you have only a few primary keys in youe WHERE condition that does affect the performance. But some times requirement itself may be in that way. We may not be knowing all the primary keys to given them in WHER conditon. If you know the values, then provide them without fail.

2. Yes. It's better to always follow the sequence in WHERE condition and even in the fields being fetched.

One important point is, whenever you use FOR ALL ENTRIES IN, please make sure that the itab IS NOT INITIAL i.e. the itab must have been filled in. So, place the same conditin before both the SELECT queries like:

IF i_anla[] IS NOT INITIAL.
SELECT bukrs                            
         anln1                           
         anln2                           
         afabe                           
         gjahr                         
         peraf                          
         lnran                          
         bzdat                           
         bwasl                         
         belnr                          
         buzei                          
         anbtr                        
         lnsan                          
    FROM anep
    INTO TABLE o_anep
    FOR ALL ENTRIES IN i_anla
   WHERE bukrs = i_anla-bukrs           
     AND anln1 = i_anla-anln1           
     AND anln2 = i_anla-anln2           
     AND afabe IN s_afabe               
     AND bzdat =< p_date                 
     AND bwasl IN s_bwasl.               
ENDIF. 
 
IF o_anep[] IS NOT INITIAL.
  SELECT bukrs       
         anln1       
         anln2       
         gjahr       
         lnran        
         afabe       
         aufwv        
         nafal    
         safal        
         aafal       
         erlbt     
         aufwl       
         nafav     
         aafav      
         invzv    
         invzl       
    FROM anea
    INTO TABLE o_anea
    FOR ALL ENTRIES IN o_anep
   WHERE bukrs = o_anep-bukrs     
     AND anln1 = o_anep-anln1     
     AND anln2 = o_anep-anln2     
     AND gjahr = o_anep-gjahr     
     AND lnran = o_anep-lnran    
     AND afabe = o_anep-afabe.
ENDIF.