Need help with this SELECT against BSIS

[former_member210148]

I have a Web Dynpro app that calls an RFC to do some asynchronous processing to avoid the 1-minute time-out I'd otherwise run into.  The RFC is a bunch of SELECT statements against BSIS that are all very similar to each other with slight differences.  I write the data out to a zTable.  Once the RFC ends and the Action fires back in my WDA application, I read the data in from my zTable and continue processing.

The problem I'm running into is that the SELECT statement just "hangs", and I think it's probably due to the large numbers of parameters in my WHERE...IN internal table.  But I'm also not sure about the >= and <= I have in this statement, nor am I sure I've created the best index.  So, I'm going to provide as much information up front as I can, and I'm hoping that someone out there can give me some ideas on how to get this -- and the related -- BSIS queries running in a somewhat-timely fashion.

Here's the query:

SELECT hkont

          geber

          budat

          shkzg

          dmbtr

          fipos

     FROM bsis

     INTO CORRESPONDING FIELDS OF TABLE lt_bsis_cash_in

     WHERE geber IN lt_geber     AND

           bukrs = 'PUR'         AND

           hkont >= '0000400000' AND

           hkont <= '0000499999' AND

           budat IN lt_budat.

When I enter a range of WBS Elements in my WDA app, I get the corresponding GEBER values, sort and de-dupe, and pass it into this query via internal table LT_GEBER.  At 1,000 entries, LT_GEBER has 984 entries.  At 2,000 entries, LT_GEBER has 1,948.  Both of these run pretty quickly (a minute or so).  But when I go up to 2,500 entries and LT_GEBER has 2,440 entries, the query just "hangs" during processing.  In every instance, LT_BUDAT has "I BT 19920101 20150214" (so I'm selecting on budat between 1/1/92 and 2/14/15).  We just have one company code here, so the BUKRS = 'PUR' could probably be removed.

As you might have guessed, my other queries are different in the ranges of HKONT that I'm selecting.  In the example above, our "Cash In" records are in the range of 400000-499999.

When the query runs with 1,000 or 2,000 WBS Elements selected, ST05 looks like this:

4 FILTER

  Filter Predicates

  3 INLIST ITERATOR

    2 TABLE ACCESS BY INDEX ROWID BSIS

      1 INDEX RANGE SCAN BSIS~Z4

Index Z4, which I built for these queries, looks like this (I've added #Distinct values based on a sampling of 653,408 rows; the entire BSIS table has just over 65,000,000 records):

MANDT (1)

BUKRS (1)
GEBER (12,895)

HKONT (1,532)

BUDAT (2,791)

So at this point, I'm not sure what to do.  A better index?  Restructuring the query to do less in the WHERE clause and do more by looping through the table and deleting records that don't match my criteria there?  Something else?

This is all on ECC 6.0.  If you need additional information, please let me know.  Any information folks can provide is GREATLY appreciated.  Thanks so much for your time.

Dave

[thanga_prakash]

Hello Dave,

Try with the below options.

1) Create index in following sequence and use the same sequence in WHERE clause in the SELECT query i.e., Try creating the index in the sequence it is appearing in the tables and use it in the SELECT query.

Index:

MANDT

BUKRS

HKONT

BUDAT

GEBER

Query:

SELECT hkont

              budat

              shkzg

              dmbtr

              fipos

              geber

     FROM bsis

     INTO CORRESPONDING FIELDS OF TABLE lt_bsis_cash_in

     WHERE bukrs = 'PUR'         AND

           hkont >= '0000400000' AND

           hkont <= '0000499999' AND

           budat IN lt_budat     AND

           geber IN lt_geber.

2) If Option 1 doesn't give good performance then try this, use PACKAGE SIZE in the select query. Here it retrieves 500 records at once and then you can append those entries to the final internal table.

SELECT hkont

              budat

              shkzg

              dmbtr

              fipos

             geber

     FROM bsis

     INTO CORRESPONDING FIELDS OF TABLE lt_bsis_cash_in PACKAGE SIZE 500

     WHERE bukrs = 'PUR'         AND

           hkont >= '0000400000' AND

           hkont <= '0000499999' AND

           budat IN lt_budat     AND

           geber IN lt_geber.

APPEND LINES OF lt_bsis_cash_in TO lt_final. "lt_final is of the type lt_bsis_cash_in

ENDSELECT.

3) If above option 2 also not giving good performance use CURSOR method to fetch data from table.

Refer to the below link on how to use the CURSOR and how it is useful during fetching of huge data.

SELECT Statements and CURSOR statement - Performance Analysis. - ABAP Development - SCN Wiki

Regards,

TP

[former_member210148]

First, thank you Thanga with taking the time to respond.  I must admit I'm quite disappointed in the lack of response to my posting (89 views -- 1 response), but I digress...

I did try each of your suggestions, but none of them really made a difference.  I read elsewhere that the PACKAGE SIZE suggestion really has to do with memory issues, and the CURSOR/FETCH option has to do more with positioning in the table; neither, from what I read elsewhere, was really a fix for performance.

So what I ended up doing (I don't believe in simply responding with "problem solved" as that benefits no one) is removing the HKONT check from the SELECT statement.  Because my other queries were differently only in this aspect, I was able to whittle 6 separate BSIS queries down to 2 (the other SELECT did use different GEBER values).  I then looped through the resulting internal table, assigning to a field symbol, and used a simple IF statement to determine the type of record it was (Cash In, Cash Out, Expense, or Liabilities).  I append the field symbol directly to separate internal tables for each of my record types.

Running against a BSIS table of just over 65 million records, I can complete these queries in just over 9 minutes (doing this in an RFC called asynchronously from my Web Dynpro application).  That's what I consider a "cold" run (i.e. nothing cached by the database).  Subsequent runs can be anywhere from 21 seconds to just over a minute if my GEBER values don't change drastically.

Thanks again, Thanga, for your response.  It was much appreciated.

[thanga_prakash]

Hello Dave,

Welcome Thanks a lot for sharing the answer. You gave me a chance to recollect my skills on improving the performance of a query.

Keep posting queries in SCN!!!

Cheers,

Thanga

[yuri_ziryukin]

Hello Dave,

if I take a look at your statement I realize that only 3 fields from your SELECT clause are missing in your index:

shkzg 

dmbtr

fipos

If you add these fields to your index Z4 at the end (I know it will significantly increase the index size), you will be able to avoid "TABLE ACCESS" in the execution plan and it will save you a lot of time.

If you anyway removed HKONT from the WHERE clause, you can rebuild your index in the following order:

MANDT

BUKRS
GEBER

BUDAT

HKONT

SHKZG

DMBTR

FIPOS

I think this will really make all your queries quite fast.

Cheers,

  Yuri

[former_member210148]

Yuri,

I'm not seeing a table access in the ST05 trace.  And I don't believe adding those 3 additional fields will improve anything at all because I'm just selecting them.  They're not part of the WHERE clause.  If they were, then yes, I could see where adding them to the index would help.  But I don't see a need to add them to the index -- and creating a significantly larger index for a table that already has over 65 million records -- when they're just being selected and not part of the WHERE.

Dave

[yuri_ziryukin]

Well Dave

Here is from your original post:

4 FILTER

  Filter Predicates

  3 INLIST ITERATOR

    2 TABLE ACCESS BY INDEX ROWID BSIS

      1 INDEX RANGE SCAN BSIS~Z4

Do you see the line I have marked?

You could just give it a try. But if you decide not to believe me, well your choice.

Good luck,

  Yuri

[former_member210148]

Yuri,

My apologies -- I read "table access" but thought "table scan".

That "TABLE ACCESS" is completely normal.  "1 INDEX RANGE SCAN BSIS~Z4" tells me it's using index Z4 to get to the data based on the fields in my WHERE clause.  "2 TABLE ACCESS BY INDEX ROWID BSIS" means that the ROWID is taken from the index to access the record in the table directly.  Again, that's completely normal and tells me my index is being used to directly access records in the table.

However, for sake of argument, I did modify my index in our sandbox environment by adding the 3 additional fields.  As I expected, I saw no decrease in run time.

Two honest questions for you:

1.  Why do you feel adding those 3 fields to the index would make a difference when they aren't part of the WHERE clause?

2.  Why do you feel "table access" can be avoided?  I don't believe that it can -- I mean, all that's saying is the table is being accessed to get the data.  You can't bypass that.

[yuri_ziryukin]

Hello Dave,

did you take a new ST05 trace? How does the access path look like?

The answer to your question is pretty simple. By moving fields that you are selecting into the index you are avoiding table access. DB does not need to read the table block anymore because it can get all the fields directly from index blocks.

This is a relatively widely used technique to improve the performance of select statements. I assume even better result one can get in the case of highly fragmented tables.

It's not my "feeling" as you mentioned, it's rather a knowledge based on understanding of how database works and supported by years of performance optimizations in my job as SAP Support employee.

Cheers,

  Yuri

[former_member210148]

Yuri,

Okay, I see what you're saying.  I tend to challenge posters on their directions because SCN is full of false information; what you say makes sense, so I went back and tried it again.

I updated my index to include all the fields.  I set up another trace on ST05 and ran the application.  The results were the same:  1 INDEX RANGE SCAN, 2 TABLE ACCESS, 3 INLIST ITERATOR, 4 FILTER.  When I click on the index for the range scan, it does select the index I changed, but it only shows the original 3 fields that are part of my WHERE clause.  However, I did delete the old index, then recreated the new one via the Database Utility when I updated it to include all the fields.

Any idea why it still might be using the Table Access?

Thanks!

Dave

[yuri_ziryukin]

This is weird, Dave.

Can you post here the complete output of the EXPLAIN including information about the index. Please also don't forget to refresh DB statistics for BSIS table after you make changes to the index. You can do that with DB20 transaction.

I don't see any reasons for the table access to be honest.

Cheers,

  Yuri

[former_member210148]

Yuri,

Thank you so much for mentioning the need to refresh DB statistics.  Not being a DBA, I had no idea this needed to be done.  Once I did this, I was able to re-run my application with your suggested index values, and table access was indeed eliminated:

SELECT STATEMENT ( Estimated Costs = 391 , Estimated #Rows = 24,550 )

       3 FILTER

         Filter Predicates

           2 INLIST ITERATOR

               1 INDEX RANGE SCAN BSIS~Z4

                 ( Estim. Costs = 390 , Estim. #Rows = 24,550 )

                 Search Columns: 4

                 Estim. CPU-Costs = 12,871,332 Estim. IO-Costs = 390

                 Access Predicates

I had no idea that the database (we use Oracle) would skip the table access if all the fields were in the index, but that makes total sense to me.  Thanks so much for your help!  Also, please note that my posts meant no offense -- as I said, they were honest questions.  I've found that I sometimes need to challenge posters on their advice because one thing I've learned in my 10 years of doing ABAP is that you can find a lot of wrong advice here.  I like to push back a bit when someone posts information that I'm not clear on so I can learn the "why".

This information will definitely help in many ways, and I'll pass it on to our other developers as well.

Thanks Yuri!

[yuri_ziryukin]

You are welcome