Application Development 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
Showing results for 
Search instead for 
Did you mean: 

Performance issues for tables FAGLFLEXA and BSIS

Former Member
0 Kudos
2,234

Hello Guys,

I have a major performance issue with one of my report. I have to fetch the data from huge tables FAGLFLEXA and BSIS tables. I have followed all the best coding practices to the best I can. I have created secondary indexes for both these tables. Also I have removed even for all entries condition with the ranges. Then also report end with TIME_OUT error which is set for 1 hour.

Worst part is table FAGLFLEXA returns 15,143,682 (around 1.5 crore) records in my internal table after the select query gets executed. Now using so much data I need to get into BSIS which again takes hell lot of time. My code looks like this:

QUERY ON FAGLFLEXA table

:

select ryear
            docnr
            rbukrs
    from faglflexa
    into table i_flexa_u
    where  ryear  in s_gjahr
       and docnr  in s_belnr
       and rldnr  eq '0L'
       and rbukrs in s_bukrs
       and racct  in s_glact
       and prctr  in s_prctr
    %_hints db6 'INDEX("FAGLFLEXA" "FAGLFLEXA~8")'.    " Secondary index created in data base table

QUERY ON BSIS table:

select bukrs
            hkont  
            augdt  
            augbl  
            zuonr  
            gjahr  
            belnr  
            buzei 
            budat 
            bldat 
            blart  
            shkzg 
            dmbtr 
            kostl  
            projk  
            prctr  
            xstov  
         into table i_bsis
         from bsis
         where bukrs in   s_bukrs
          and    hkont  in   s_glact
          and     gjahr  in   s_gjahr
          and      belnr in   r_belnr   " Range created for entries in FAGLFLEXA to avoid FOR ALL ENTRIES          
          and    budat  in   s_budat
          and      blart  in   s_blart
          and    xstov  in   r_xstov
    %_hints db6 'INDEX("BSIS" "BSIS~2")'.    " Secondary index created for BSIS table

After this I have selects on table BSAS , then BKPF anf finally on BSEG.

I know that these huge table will always create issues, but after discussing with functional consultant the final verdict was that it is mandatory to use these tables only. Now with this huge huge data how to proceed and save performance.

Please revert with some positive response.

Thanks

Harjeet

7 REPLIES 7

Former Member
0 Kudos
279

Hello Friends,

Did I asked a very tough question ?

I don't understand that its been more then 4 Hours I posted the thread and no one yet responded to it. Is it like the performance tuning which I have done is perfect and no more tuning possibilities are there?

I request to at least respond to the post as this is <removed by moderator> giving TIME_OUT runtime error. I need to resolve this issue <removed by moderator>.

<removed by moderator>

Thanks,

Harjeet Grover

Edited by: Thomas Zloch on Aug 28, 2011 11:20 PM

0 Kudos
279

It's Sunday, people are doing something else than waiting for your issues to drop in. Also, this is not your personal helpdesk, nobody is obliged to reply, actually with such posts you are reducing the chances that anybody would feel inclined to help you.

All issues have the same priority here.

Please do not offer points.

Thomas

[Rules of engagement|http://wiki.sdn.sap.com/wiki/display/HOME/RulesofEngagement]

0 Kudos
279

Hello Thomas,

Apologies. Actually speaking we are in a situation that we even forget weekdays and weekends. I didn't noticed that its actually Sunday.

Moreover I haven't written anything offensive but I still agree to your point. I will wait in that case for another 20 Hours

Please guys do reply and help me with the possible solutions.

Thanks,

Harjeet

Former Member
0 Kudos
279

Harjeet,

It has been always a debatable topic to use FAE or Ranges or INNER JOIN for such cases. The convention I have been following is that if the data expected out of 1st table if too large (which is the case here), I always use INNER JOINS.

>> I suppose you are not (generally) filling S_BELNR on selection screen and therefore taking lot of time because it is the main Filtering field. Therefore even if using index, it is not actually helping much.

>> I believe you should not have created your OWN indexes on such larger tables mainly because you have lot of selection fields which are already part of primary key and SAP standard indexes. Please Delete your manual indexes and if possible Gather the statistics of both these tables with the help of basis team.

>> You have to decide that which parameter you are actually filling (at least most of the times) among s_belnr and S_glact and make your query based on that decision to know that you want to use index Primary index OR index 2 and change order of selection query fields accordingly.

>> Make your query like


***if you are filling S_EBLNR , use...
select f~f1 f~F2 B~F1 B~F2 .....
       into table i_flexa_u
	   from faglflexa AS F INNER JOIN BSIS AS B
         ON  f~BUKRS = b~bukrs
		 and f~BELNR = b~BELNR
		 and f~GJAHR = b~GJAHR
		 and f~BUZEI = b~BUZEI
		 and f~racct = b~hkont
       where f~ryear  in s_gjahr
		 and f~docnr  in s_belnr
	     and f~rldnr  eq '0L'
		 and f~rbukrs in s_bukrs
		 and f~racct  in s_glact
         and f~prctr  in s_prctr
		 and b~budat  in s_budat
         and b~blart  in s_blart
         and b~xstov  in r_xstov.
	
***if you are filling S_GLACT , use...
select f~f1 f~F2 B~F1 B~F2 .....
       into table i_flexa_u
	   from faglflexa AS F INNER JOIN BSIS AS B
         ON  f~BUKRS = b~bukrs
		 and f~BELNR = b~BELNR
		 and f~GJAHR = b~GJAHR
		 and f~BUZEI = b~BUZEI
		 and f~racct = b~hkont
       where f~rldnr  eq '0L' 
		 and f~rbukrs in s_bukrs 
	     and f~racct  in s_glact
		 and f~ryear  in s_gjahr
		 and f~docnr  in s_belnr
         and f~prctr  in s_prctr
		 and b~budat  in s_budat
         and b~blart  in s_blart
         and b~xstov  in r_xstov.	

BR,

Diwakar

0 Kudos
279

Hello Diwakar,

I have checked everything related to performance but nothing is working out since the entries that fetches after the select query are almost 30 Million records. And obviously with 30 Million records data processing will be slow.

So to move ahead I have proposed a background job to run this report, but in that case user wants to select the user defined layout.

How to proceed with user defined layout without getting the output initially. I mean I need to select the layout with the column details in my selection screen. Is it possible to provide the layout option with selecting the number of columns in selection screen.

I know that we can select the layout with a parameter in selection screen but that doesn't facilitate the column selection. I need that option too?

Is it possible? If yes How?

Thanks,

Harjeet

0 Kudos
279

Hello Guys,

So is it possible to make some changes like the way I explained. In case there is any doubt please reply, I need to provide some solution to the user. Nothing is happening to the report since if it runs in background , user does not want all the columns in the output. We need to restrict them?

Thanks,

Harjeet

0 Kudos
279

Hi

You can try to use a solution like the report for tax RFUMSV00: here there are some pushbuttons to generate the ALV (I suppose you're speaking about ALV) layout.

When that button is pushed, the report called itself, but using a dummy selection, so the user can create a layout.

Max