This website is not affiliated with, sponsored by, or approved by SAP AG.

Performance/memory issues with KONV

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, Gothmog, YuriT

Performance/memory issues with KONV

Postby stiili on Mon Jul 31, 2006 6:35 am

Hello,

problem is this: I need to get all the lines from KONV, so that the condition VBRK-KNUMV EQ KONV-KNUMV is true. These lines should be appended to table ZKONV. This wouldn't be a problem otherwise, but KONV has around 30 million lines and VBRK has around 150 000 lines. I can only test this once a day, so developing is kinda slow. I'm just wondering what do you think about this solution, and if it is bad could you give some hints? So far i've only experienced very long running times or dumps because out of memory.

Code: Select all
DATA: it_vbrk TYPE SORTED TABLE OF vbrk WITH UNIQUE KEY knumv,
      wa_zkonv TYPE zkonv.

SELECT knumv
FROM vbrk
INTO table it_vbrk.

SELECT mandt knumv kposn stunr zaehk kschl kdatu kstat kwert
FROM konv
INTO (wa_zkonv-mandt, wa_zkonv-knumv, wa_zkonv-kposn,                                              wa_zkonv-stunr, wa_zkonv-zaehk, wa_zkonv-kschl,
wa_zkonv-kdatu, wa_zkonv-kstat,wa_zkonv-kwert)
FOR ALL ENTRIES IN it_vbrk
WHERE knumv EQ it_vbrk-knumv AND
      kwert NE 0.

IF sy-subrc eq 0.
*do the appending
ENDIF.

CLEAR: wa_zkonv.

ENDSELECT.


stiili
 
Posts: 3
Joined: Mon Apr 24, 2006 3:01 am

Hmmm

Postby Dr Sidewalk on Mon Jul 31, 2006 6:53 am

Why not join them ?.

To a degree the number of records does not matter if you're accessing purely using the table keys, which it looks like you are.

By nesting the SQL statements you're asking for fun, but then that depends on how many records you are expecting to be returned. Could you tell us what the count is that you're expecting. Also, what version of SAP and what database are you using. The use of the 'FOR ALL ENTRIES IN' clause led me to ask what database you're using because DB2 deteriates badly using this SQL clause.

Even if you continue to bring back a single record at a time and append it you might want to think about still using a single joined SQL statement.

If your itab is too big to process then there's another alternative. Simply run the joined SQL query and insert the returned values into a table with the appropriate table, then extract record a time and output the report lines. This'll keep you itab small. But of course you may not have encountered this problem. :]

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."
Dr Sidewalk
 
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Postby stiili on Mon Jul 31, 2006 7:01 am

First of all, table KONV is a cluster table so i assumed it can't be joined?

I don't know the actual size of expected result set, but it will be several millions. I'd guess it is around 5-10 millions.

I am using SAP Enterprise 4.7 with Microsoft SQL Server 2000.

And by the way, thanks for the quick answer.
stiili
 
Posts: 3
Joined: Mon Apr 24, 2006 3:01 am

Postby SLKorolev on Mon Jul 31, 2006 7:27 am

Yes you cannot use joins to access KONV. If you cannot restrict result set then you should divide select from KONV somehow . For example, divide it_vbrk into 500-record chunks and then select KONV inside loop for each 500 record chunk.
Best regards, Sergey Korolev
SLKorolev
 
Posts: 607
Joined: Wed Dec 18, 2002 1:05 am
Location: Russia

Postby hognoxious on Mon Jul 31, 2006 7:31 am

I was going to tell you to lose the nested selects, but on closer examination there don't seem to be any (in which case maybe you should indent your code a bit better and/or comment it more?).

The SELECT - ENDESLECT is still a bad idea though. Better to select into an itab directly.

I'd also check the size of your first table - on some systems it can be slower to read 50K than to do 5K ten times. Search on FOR ALL, there's been a few discussions about it here.

The answer is generally "it depends".
First he pinches my sig, Now he's cribbing my posts too!
hognoxious
 
Posts: 1399
Joined: Thu Dec 04, 2003 8:16 am
Location: Stuck in a pagoda with Tricia Toyoda


Return to ABAP

Who is online

Users browsing this forum: Google [Bot] and 7 guests



This website is not affiliated with, sponsored by, or approved by SAP AG.