topic Re: Nested SELECT FROM BSEG - performance in Application Development and Automation Discussions

Nested SELECT FROM BSEG - performance

former_member185943 — Thu, 18 Sep 2008 08:00:38 GMT

Hello!

I have a very simple program with 2 nested SELECTs, but with huge amount of data, and as the worst thing, on a cluster table BSEG with non-key access. I need to get the first set of data based on a certain criteria, and then the second set based on the first set. I can't use index tables such as BSID etc. Here is my program:

  SELECT bukrs belnr gjahr FROM bseg INTO ls_bseg1
     WHERE bukrs =  '0100' AND
           projk NE space AND
           ( koart = 'S' OR koart = 'A' ).
    SELECT bukrs belnr gjahr buzei rebzg rebzz dmbtr FROM bseg INTO ls_bseg2
           WHERE bukrs = ls_bseg1-bukrs AND
                 belnr = ls_bseg1-belnr AND
                 gjahr = ls_bseg1-gjahr AND
                 koart = 'K' AND
                 augdt > p_augdt.
      PERFORM write_line USING ls_bseg2.
    ENDSELECT.
  ENDSELECT.

Could anyone offer an advice on better performance? I will also try with internal tables, but I expect a short dump because of memory occupancy. I also have in mind using file on app server.

Thanks!

Regards,

Igor

Re: Nested SELECT FROM BSEG - performance

Former Member — Thu, 18 Sep 2008 08:02:50 GMT

Hi Igor,

Instead of NESTED you write FOR ALL ENTRIES and check the performance.

Thanks

Sudharshan

Re: Nested SELECT FROM BSEG - performance

Former Member — Thu, 18 Sep 2008 08:12:52 GMT

Hi Igor,

Check whether this query gives you the same result as expected in your nested statement.

SELECT bukrs belnr gjahr buzei rebzg rebzz dmbtr

FROM bseg INTO ls_bseg2

WHERE bukrs = '0100' AND

( koart = 'S' OR koart = 'A' OR koart = 'K' ) AND

augdt > p_augdt AND

projk NE SPACE.

Thanks

Sudharshan

Re: Nested SELECT FROM BSEG - performance

former_member185943 — Thu, 18 Sep 2008 08:14:17 GMT

Sudharshan,

that's what I meant when I said that I'll try with internal tables. Thanks!

I just realized that I can't do nested SELECTs on the same table - I must use cursors. So the code is:

  OPEN CURSOR l_curs_bseg1 FOR SELECT bukrs belnr gjahr FROM bseg
   WHERE bukrs =  '0100' AND
         projk NE space AND
         ( koart = 'S' OR koart = 'A' ).
  DO.
    FETCH NEXT CURSOR l_curs_bseg1 INTO ls_bseg1.
    IF sy-subrc NE 0.
      CLOSE CURSOR l_curs_bseg1.
      EXIT.
    ENDIF.
    OPEN CURSOR l_curs_bseg2 FOR SELECT bukrs belnr gjahr buzei rebzg rebzz dmbtr FROM bseg
           WHERE bukrs = ls_bseg1-bukrs AND
                 belnr = ls_bseg1-belnr AND
                 gjahr = ls_bseg1-gjahr AND
                 koart = 'K' AND
                 augdt > p_augdt.
    DO.
      FETCH NEXT CURSOR l_curs_bseg2  INTO ls_bseg2.
      IF sy-subrc NE 0.
        CLOSE CURSOR l_curs_bseg2.
        EXIT.
      ENDIF.
      PERFORM write_line USING ls_bseg2.
    ENDDO.
  ENDDO.

but the question is the same.

Igor

P.S. Why can't I edit my first post?

Edited by: Igor Barbaric on Sep 18, 2008 10:19 AM

Sorry, one more correction in code.

Re: Nested SELECT FROM BSEG - performance

ThomasZloch — Thu, 18 Sep 2008 08:15:28 GMT

Based on your selection criteria for the outer select I don't see a way to speed it up. You obviously want all S- and A-type document items containing any PSP element...even if you could create a secondary index for PROJK it would not help much.

So don't additionally waste memory space by using internal tables or for all entries, and let this bugger work in the background, probably gaining a top spot in the next early watch report.

If this is a really critical report, you might want to look into using some sort of exit at time of document posting, in order to fill an info structure with the relevant data, which can then be analysed quickly. Maybe there is even some FI standard functionality allowing for that.

Thomas

Re: Nested SELECT FROM BSEG - performance

Former Member — Thu, 18 Sep 2008 08:18:31 GMT

i think FOR ALL ENTRIES is the best way to increase the performance .....

use this and check performance.

Re: Nested SELECT FROM BSEG - performance

ThomasZloch — Thu, 18 Sep 2008 08:20:37 GMT


> P.S. Why can't I edit my first post?

Because there were already replies to it. Can't change history

Re: Nested SELECT FROM BSEG - performance

former_member185943 — Thu, 18 Sep 2008 08:22:34 GMT

Thanks, Thomas!

What do you think about a text file on app server as storage for the first SELECT's result, and then doing second SELECT by reading the file? Doesn't sound promising, but just a thought...

Igor

Edited by: Igor Barbaric on Sep 18, 2008 10:23 AM

Can't change history? Fair enough!

Re: Nested SELECT FROM BSEG - performance

ThomasZloch — Thu, 18 Sep 2008 08:32:43 GMT

Well, having a program that actually serves pure reporting purposes create a file on the app server is quite unusual. If your "open cursor" solution works, why not use that one. One more thought, have a look also at the "subquery" technique for select statements.

Thomas

Re: Nested SELECT FROM BSEG - performance

former_member182371 — Thu, 18 Sep 2008 20:08:07 GMT

Hi,

have a look at this (the key here is field PROJK and accessing table BSEG by key fields):

1.- http://sap.ittoolbox.com/groups/technical-functional/sap-dev/alternative-for-table-bseg-and-bsim-1530213

2.- http://www.sapnet.ru/viewtopic.php?t=458

3.- http://www.thaisapclub.com/forums/showthread.php?t=766&page=2

it´s just another option.

Best regards.

Edited by: Pablo Casamayor on Sep 18, 2008 10:32 PM

Re: Nested SELECT FROM BSEG - performance

Former Member — Thu, 18 Sep 2008 20:48:21 GMT

Igor - what you really need is a secondary index table where the primary key is the project number and the table entries include the FI document number. I doubt if such a beast exists, but you might look for it.

Otherwise, run it in the background. Not all problems have elegant solutions.

Rob

On second though - you are using NE in the WHERE for PROJK, so even if you could find a secondary index table, it wouldn't help.

Run it in the background.

Edited by: Rob Burbank on Oct 1, 2008 4:42 PM