Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

performance problem between cv04n and zprogramm

Former Member

‎2007 Jun 11 11:25 AM

0 Likes
983

I have a difference in performance between my zprogramm and tcode cv04n.

I use the same SAP function to retrieve the data which is used in CV04n

CV100_DOCUMENT_SEARCH

I have already compared the inputs in both programms with each other and I can't find any differences anymore but still the performance trace shows some significant differences ?? (and the zprogramm is also noticable very slow)

CV04N fetch times ~ 500


  SELECT                                                                                
"OBJEK"                                                                        
  FROM                                                                                
"AUSP"                                                                         
  WHERE                                                                                
"MANDT" = :A0 AND "OBJEK" IN ( :A1 , :A2 , :A3 , :A4 , :A5 ) AND "MAFID" =     
    :A6 AND "KLART" = :A7 AND "ATINN" = :A8 AND "ATWRT" = :A9                                                                                
A0(CH,3)  = 055                                                                    
A1(CH,50) = Z01000000000000001000000001500000                                      
A2(CH,50) = Z01000000000000001000000001600000                                      
A3(CH,50) = Z01000000000000001000000001700000                                      
A4(CH,50) = Z01000000000000001000000001800000                                      
A5(CH,50) = Z01000000000000001000000001900000                                      
A6(CH,1)  = O                                                                      
A7(CH,3)  = 017                                                                    
A8(NU,10) = 0000000148                                                             
A9(CH,30) = E

zprogramm fetch times ~53.000


  SELECT                                                                                
"OBJEK"                                                                          
  FROM                                                                                
"AUSP"                                                                           
  WHERE                                                                                
"MANDT" = :A0 AND "OBJEK" IN ( :A1 , :A2 , :A3 , :A4 , :A5 ) AND "MAFID" =       
    :A6 AND "KLART" = :A7 AND "ATINN" = :A8 AND "ATWRT" LIKE :A9                                                                                
A0(CH,3)  = 055                                                                      
A1(CH,50) = Z01000000000000001000000001000000                                        
A2(CH,50) = Z01000000000000001000000001100000                                        
A3(CH,50) = Z01000000000000001000000001200000                                        
A4(CH,50) = Z01000000000000001000000001300000                                        
A5(CH,50) = Z01000000000000001000000001400000                                        
A6(CH,1)  = O                                                                        
A7(CH,3)  = 017                                                                      
A8(NU,10) = 0000000148                                                               
A9(CH,1)  = E

I can't find any difference in the sql statements which can help me further ?

is there anything I missed here while comparing the 2 programms with each other ?

I'm quite stuck here on how to debug this thing

alle help will be awarded .

kind regards

arthur

7 REPLIES 7
Read only

Former Member

‎2007 Jun 11 11:32 AM

0 Likes
919

Hi Arthur ,

I feel there is a diffrence in the input for both the select statements ,

In the select statement for the standard program OBJEK is being passed as

A1(CH,50) = Z0100000000000000100000000<b>1500000</b>

A2(CH,50) = Z0100000000000000100000000<b>1600000 </b>

A3(CH,50) = Z0100000000000000100000000<b>1700000 </b>

A4(CH,50) = Z0100000000000000100000000<b>1800000</b>

A5(CH,50) = Z0100000000000000100000000<b>1900000 </b>

but for the Zprogram it is

A1(CH,50) = Z0100000000000000100000000<b>1000000 </b>

A2(CH,50) = Z0100000000000000100000000<b>1100000 </b>

A3(CH,50) = Z0100000000000000100000000<b>1200000 </b>

A4(CH,50) = Z0100000000000000100000000<b>1300000</b>

A5(CH,50) = Z0100000000000000100000000<b>1400000 </b>

This must be the reason why a lot more number of records are being retreived.

Plese check in the standard program , whether they are using some conversion exit .

Regards

Arun

Read only

Former Member
In response to Former Member

‎2007 Jun 11 12:27 PM

0 Likes
919

I just got from 2 performance traces one of the 2000 individual select statements in each trace and took an example from both . I'm sure that the first record is also somewhere in the second trace. here an overview of the traces of the first 5 records


       32 AUSP       REOPEN             0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000000000000' , 'Z01000000000000001000000000100000' 
   56.659 AUSP       FETCH       5   1403                                                                                
8 AUSP       REOPEN             0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000000500000' , 'Z01000000000000001000000000600000' 
   53.797 AUSP       FETCH       5   1403                                                                                
8 AUSP       REOPEN             0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000001000000' , 'Z01000000000000001000000001100000' 
   55.809 AUSP       FETCH       5   1403                                                                                
7 AUSP       REOPEN             0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000001500000' , 'Z01000000000000001000000001600000' 
   55.346 AUSP       FETCH       5   1403                                                                                
7 AUSP       REOPEN             0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000002000000' , 'Z01000000000000001000000002100000' 
   53.287 AUSP       FETCH       5   1403                                                                                
7 AUSP       REOPEN             0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000002500000' , 'Z01000000000000001000000002600000' 
   52.883 AUSP       FETCH       4   1403                                                                                



        9 AUSP       OPEN               0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000000000000' , 'Z01000000000000001000000000100000' ,
    1.989 AUSP       FETCH       5   1403                                                                                
8 AUSP       REOPEN             0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000000500000' , 'Z01000000000000001000000000600000' ,
      532 AUSP       FETCH       5   1403                                                                                
7 AUSP       REOPEN             0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000001000000' , 'Z01000000000000001000000001100000' ,
      494 AUSP       FETCH       5   1403                                                                                
7 AUSP       REOPEN             0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000001500000' , 'Z01000000000000001000000001600000' ,
      485 AUSP       FETCH       5   1403                                                                                
7 AUSP       REOPEN             0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000002000000' , 'Z01000000000000001000000002100000' ,
      491 AUSP       FETCH       5   1403                                                                                
7 AUSP       REOPEN             0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000002500000' , 'Z01000000000000001000000002600000' ,
      490 AUSP       FETCH       4   1403                                                                                
7 AUSP       REOPEN             0 SELECT WHERE "MANDT" = '055' AND "OBJEK" IN ( 'Z01000000000000001000000003000000' , 'Z01000000000000001000000003100000' ,

I noticed one difference in the performance trace is that my zprogramm doesn't have the prepare statement for ausp. I don't know if this is significant and or why the standard sap function is not triggering prepare in the z programm and does in cv04n ??

Message was edited by:

A. de Smidt

Read only

Former Member

‎2007 Jun 11 1:33 PM

0 Likes
919

If you use the EXPLAIN function in SQL Trace it will show you which index is being used on the read.

For the trace you show it looks like the two programs are using different indexes.

Another cause might be due to the state of the database buffers. This can happen in DEV where there is not much activity, so the first time you read the data, it is slow as the buffer gets filled, the next time maybe faster. I'd suggest doing each test at least 3 times.

Read only

Former Member
In response to Former Member

‎2007 Jun 11 2:04 PM

0 Likes
919

nop the programms have run multiple times next to each other and the result is consistent

Read only

Former Member

‎2007 Jun 11 3:03 PM

0 Likes
919

Would you please post the actual code you are using instead of the results of the explain?

Thanks

Rob

Read only

Former Member

‎2007 Jun 11 3:06 PM

0 Likes
919

Hi,

Looking at your original post, there does seem to be one difference in the code:

CV04N has ATWRT = ...

Your program has ATWRT LIKE ..

This might be enough to stop the program from using the 'best' index.

Look in ST05 to see which indexes are being used.

Read only

Former Member
In response to Former Member

‎2007 Jun 11 3:13 PM

0 Likes
919

yep I also discovered that difference when I looked in the explain button. strange how you're eyes can deceive you'r self many times over.

I traced the problem back to the field SLCODE which is passed in ccomw. the z programm assumed LIKE always for char fields.

I saw in the explain code that indexN1 was used where indexN3 was used by cv04n

thank for the help and point are rewarded for the help