09-18-2008 9:00 AM
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
09-18-2008 9:08 PM
Hi,
have a look at this (the key here is field PROJK and accessing table BSEG by key fields):
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
09-18-2008 9:02 AM
Hi Igor,
Instead of NESTED you write FOR ALL ENTRIES and check the performance.
Thanks
Sudharshan
09-18-2008 9:14 AM
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.
09-18-2008 9:20 AM
>
> P.S. Why can't I edit my first post?
Because there were already replies to it. Can't change history
09-18-2008 9:12 AM
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
09-18-2008 9:15 AM
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
09-18-2008 9:22 AM
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!
09-18-2008 9:32 AM
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
09-18-2008 9:18 AM
i think FOR ALL ENTRIES is the best way to increase the performance .....
use this and check performance.
09-18-2008 9:08 PM
Hi,
have a look at this (the key here is field PROJK and accessing table BSEG by key fields):
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
09-18-2008 9:48 PM
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