Skip to Content
Author's profile photo Kamal Mehta

Performance Improvement in BW Extractors – Setting up SQL Trace

Introduction: Extracting data is one of the most important areas in SAPBW. Any delay can cause delay in Business Reporting. Here I would try to explain the various issues that we normally have while extracting data from source system and what can be done in order to avoid those.

Would take some practical scenarios to explain the same.


Identify Expensive SQL statements on the source system during the processing of the extractor:


(I) Issue: We were experiencing lot of performance issues with respect to the extractor 0FI_GL_14 ultimately resulting in delaying in reporting. We checked the Job log in SM37 in source system but were not able to get complete information.

During a ST12 trace on extractor 0FI_GL_14 job we identify an expensive SQL statement on the table FLAGLFLEXA.  We noticed the SQL statement is performing an expensive HASH JOIN due to an inappropriate index access on the table FLAGLFLEXA as explained below.

      

       /wp-content/uploads/2013/09/1_289146.jpg

Recommendation: By doing this we were able to identify the Expensive SQL statement and were able to improve the same by creating secondary index on the FAGLFLEXA with the required fields i.e. RBUKS, RYEAR and POPER as these were not already involved. This certainly did improve the performance of the extractor.

(II) Similarly for one of other Z extractor we were facing the same performance issue. To extract 100,000 of records it was approximately taking more than 2 hours.

Issue: Data source taking more than 2 hours to extract 1000 of records resulting in delay in reporting.

Again by running the trace for the extractor job we detected a long running access to the table PRPS caused by a particular line of the code as shown below:

/wp-content/uploads/2013/09/2_289147.jpg

Note: The more the access to the database the worst the performance would be.

The code that is responsible for this access is shown below:


SELECT SINGLE * FROM prps WHERE objnr EQ lt_data-objnr.

This access causes the following access path where we can see that a full table scan is performed on the table PRPS.

/wp-content/uploads/2013/09/3_289178.jpg

We can see SQL ID 1f1xgnbhbx1tz does not have a suitable index to use and therefore must perform a full table scan.

From the below we can see below that the field OBJNR is a very selective field

/wp-content/uploads/2013/09/4_289179.jpg

When this SQL is running on the system it can be responsible for 20% of buffer gets on a hourly basis on the system as shown below.

/wp-content/uploads/2013/09/5_289180.jpg

Recommendation: (I) Select only necessary fields instead of select *.

                  (II) Created a Secondary Index on the Table PRPS with the field OBJNR.

Here, I am stressing the importance of setting up Traces in Extractor jobs to identify various sources of important and hidden information to work on the Extractor code.

Thanks for reading.


Assigned tags

      28 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Anshu Lilhori
      Anshu Lilhori

      Good piece of information kamal.

      Thanks for sharing.

      Regards,

      AL

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Thanks Anshu.

      Author's profile photo Former Member
      Former Member

      Nice Blog .. 🙂

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Hi Naveen ,

      Thanks..

      Regards

      Kamal

      Author's profile photo Former Member
      Former Member

      Nice to know. Thanks Kamal for sharing a good Blog.

      Regards

      Mahesh

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Thanks for your comments Mahesh.

      Author's profile photo Former Member
      Former Member

      Hi Kamal,

      Its really helpful 😆 😆 😆 , Especially for huge volume of records.Next time onwards i will consider your point , when ever data load taking so much time 😉 😉 .

      Thanks For Sharing 😉 😉

      Regards,

      Giri

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Hi Giri,

      Thanks for your comments. It does help and one get to know where exactly need to work on.

      Can you rate it .

      Author's profile photo Former Member
      Former Member

      Nice piece of work.

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Hi Anshul,

      Thanks for your comments .

      Can you rate it .

      Regards

      Kamal

      Author's profile photo Martin Grob
      Martin Grob

      Good work kamal

      Martin

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Thanks Martin for your valuable comments.

      Regards

      Kamal

      Author's profile photo Former Member
      Former Member

      Very Nice document.

      Regards,

      Sushant

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Thanks.

      Can you please rate it.

      Regards

      Kamal

      Author's profile photo Prashanth konduru
      Prashanth konduru

      Good one...  Thanks for sharing Kamal.

      Book marked it.

      Cheers 🙂

      KP

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Thanks Prashanth for your comments.

      Regards

      Kamal

      Author's profile photo Former Member
      Former Member

      Very helpful...

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Thanks Chetan....

      Can you rate it .......

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      This is excellent blog. It's really informative Kamal 🙂 .

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Thanks Suman for your valuable comments....

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Excellent Blog Kamal...... 🙂

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Thanks Satendra for your comments.

      Can you rate it....

      Author's profile photo Ravi Chandra
      Ravi Chandra

      Good Blog kamal.

      Thanx for sharing....

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Thanks for your comments........

      Author's profile photo Former Member
      Former Member

      Good piece of information with detailed explanation through example.

      Thanks for sharing.

      Regards,

      Sangita

      Author's profile photo Kamal Mehta
      Kamal Mehta
      Blog Post Author

      Thanks Sangita.

      Can you rate it ..

      Author's profile photo Former Member
      Former Member

      Hi,

      Do you have any idea to improve the performance of 0fi_gl_60 extractor?

      Author's profile photo Loed Despuig
      Loed Despuig

      Hi,

      Can you please make a screenshot of your current INDEXES in FAGLFLEXA table? Did you only edit the index of FAGLFLEXA table? Or did you also adjust other tables to improve the performance of 0FI_GL_14 extractor?

      Thank you.

      Loed