select rows from db table with offset and package size

[Former Member]

Hello,

consider you have a table with many lines (e.g. 10^9) and you are interested in the lines 9.000.000-9.100.000. I search for an optimized variant of the following code:

n_start = 9000000.

n_end = 9100000.

SELECT * FROM ztest_0001 INTO wa_ztest_0001.

IF sy-dbcnt >= n_start AND sy-dbcnt <= n_end.

APPEND wa_ztest_0001 TO it_ztest_0001.

ENDIF.

IF sy-dbcnt > n_end.

exit.

ENDIF.

ENDSELECT.

Unfortunately this code example is very inefficient because of the sy-dbcnt query. If n_start becomes greater you can wait hours for the result set.

Does someone know a more efficient way to handle this?

Thanks in advance.

Daniel

[Former Member]

Hi Daniel,

When you see the entries in a table, they are shown based on sort order of the key (i.e you see a sorted list of records based on key).

So, if you are interested in records from 9.000.000-9.100.000,check the key for 9.000.000.

Then you can give in ur select <b>key >= key of 9.000.000 and key <= key of 9.100.000.</b>

Regards,

Raj

[former_member186741]

this is quite an unusual requirement.

You could do a select into a table and use the 'up to n rows' clause. Then you could delete all the internal rows preceding the 9 million... or copy lines 9 mill to 9.1 mill to another table and then process that table. or process on the origional table with a 'loop at table t from 9000000'.

You could also use the 'package' addition, and just ignorre the first n packages until you get to the one you are actually interested in:

do 100 times. " or whatever the correct iteration is

SELECT * INTO TABLE itab PACKAGE SIZE 100000 FROM scarr.

ENDSELECT.

enddo.

[former_member184495]

hi daniel,

is this what you meant...?

SELECT * FROM ztest_0001 INTO table wa_ztest_0001.

loop at wa_ztest_0001.

if sy-tabix >= n_start AND sy-dbcnt <= n_end.

APPEND wa_ztest_0001 TO it_ztest_0001.

ENDIF.

IF sy-tabix > n_end.

exit.

ENDIF.

endloop.

if this satisfies please reward with points,

cheers,

Aditya.

[Former Member]

This is an odd sort of thing to have to do. Database position is not relevant in most cases. Can you let us know your requirements? So far, I don't see any way other than what you're doing already.

Rob

Message was edited by: Rob Burbank

[Former Member]

Hello,

first thank you for your answers.

@Rajasekhar Dinavahi: I forgot to say that the keyfields can not be used for the selection. There can be up to 8 key columns so this solution doesn't help.

@Neil Woodruff and aditya: I know the way to read the data in to an internal table and loop over it. This will work for small tables. But if the amount of data you want to put in an internal table gets to big, you get a memory failure. Consider you want to put 1.000.000.000 entries in an internal table (what can happen in our case).

@Rob Burbank: Unfortunately I agree with you, I don't think there's a way to solve this efficiently. I hope the requirements above help you a bit, otherwise let me know!

Thanks in advance.

Greetings,

Daniel

[Former Member]

OK then - I think Raj's answer is the way to go. You may have eight key fields, but you can start at the first one and use it; if that's not selective enough, use the first two and so on. It would help to know the makeup of the first few key fields along with the number of records.

Rob

[christian_wohlfahrt]

Hi Daniel!

It's possible with a small change of your program flow. Currently your selects are done in the parallel sessions, but when the select is done in the calling program (divided in several parts), it's no problem. You would need to give the result as table (or import) parameter to the parallel session - which can skip this select.

It is done with 'OPEN CURSOR', 'FETCH', 'CLOSE CURSOR' statements. Fetch has an extension for package size and remembers actual position = end of last select. Have a look in the docu of the ABAP-statements - I could copy this help, but this would be quite long with all the examples.

Regards,

Christian

[former_member186741]

Daniel,

using the package size option you will only be getting the 100000 rows at a time not the full monty 9 mill. This will cause no memory problem.

Have just read one of your replies and package size seems ideal for your requirement. You will be able to process in bite sized chunks.

Another improvement to target the last chunk would be to only select the key fields into a table with your primary select to get your required 100,00 entries (using package and acting on the last package as discussed). Then do the actual select on all the fields using the key table as the base for 'for all entries'.

How about adding a record number field to your table and creating an index on this?

Message was edited by: Neil Woodruff

[Former Member]

Hi Daniel,

Tell me , how do you know that only records from 9.000.000-9.100.000 are useful to you? Becauz you saw the table contents. System while displaying the records follows a sort pattern on key fields (even if the key is composite...).

So, you can specify the key values while selection.

Regards

Raj

[Former Member]

Hi Rajasekhar,

I see your point, so let me tell you a bit more about the background.

We need to transfer a table via RFC to another SAP system. Let S00 be the sender system, R00 the receiver system. The table ztest_0001 is on S00 and needs to be transferred to R00. Because table ztest_0001 is very very big, you can only transfer a specific amount (say 1 million) via RFC to R00.

When transferring data, our algorithm starts on the sender system with the first million and transfers it. Afterwards the second million is processed and so on. With above code it happens that the last entries need significantly more time than the first ones, because the algorithm has to count until it reaches the last entries. Therefore it would be nice to have a solution to directly access entries (e.g.) 9.000.000 to 10.000.000.

Thanks in advance.

Greetings,

Daniel

[Former Member]

Hi Daniel,

One question that remains unanswered is whether this transfer is one time or ongoing. If it is one time, may be you can transfer it via transports.

If it is an ongoing one, I think you have to add another key to your table that is just a sequential number based on number range. Do one time update in your table to fill this field and from then onwards, the program(s) that fill this table have to fill this number. Create an index on this field.

Now for your RFC logic, you can utilize this field to say where the number is between 9000000 and 10000000.

If adding this field is not an option, all Z transparent tables are plain database tables at the underlying database level. May be instead of trying to do this through ABAP and SAP's application layer, try to do this at the database level with direct database connection.

Just another way around the problem,

Srinivas

[Former Member]

Hi Daniel, I think there is some confussion about what sy-dbcnt system variable contains.

sy-dbcnt - Contains the number of rows from the database operation, and does not contain the actual row number.

I think that your needs is a counter to check wether the row should be appended to the internal table according to the row number.

Maybe with little changes to your code to something like this:

n_start = 9000000.

n_end = 9100000.

counter = 1.

SELECT * FROM ztest_0001 INTO wa_ztest_0001.

IF counter >= n_start AND counter <= n_end.

APPEND wa_ztest_0001 TO it_ztest_0001.

ENDIF.

IF counter > n_end.

exit.

ENDIF.

ADD1 to counter.

ENDSELECT.

Hope it helps.

Regards,

Gilberto.

[Former Member]

Hi,

Please try following logic.

select max(empno) from emp into v_max..

count = 0

select * from emp from emp into it_emp

where empno = v_max.

count = count + 1 .

v_max = v_max - 1.

• exit when count 1,00,000

if count = 1000000.

exit.

endif.

append it_emp.

endselect.

Hope this helps.

Regards,

Amole

[Clemenss]

• Idea: read n records from start of table,

• transfer them

• read next n records starting after key

• of last record read

• ... until dobne:

data:

lv_key like <dbkey>.

do.

select * from <dbtab> into table itab up to 1000000 rows

where <key> > lv_key.

if sy-subrc = 0.

• transfer by itab by RFC

<transfer itab>

• get key of last extracted record

read table itab index sy-tfill.

lv_key = itab.

else.

• leave do loop when no more entries are read

exit.

endif.

[Former Member]

Hey Clemens,

I'm really impressed!  I have moaned about not having the OFFSET keyword in ABAP select statements for a long time now, and I never thought of doing this.  This is really a clever solution.  I realize that there may be some question about how it performs, especially if you are not using an indexed field, but we are doing some tests to see how it works in our situation.

Thanks so much for taking the time to post this!

[Former Member]

Hi everybody,

thank you very much for the very helpful answers, I will try everything out!

@Gilberto Li: It is correct that sy-dbcnt returns the number of rows from the last database operation, but in case of an select-endselect statement it actually is the actual row number. SAP uses it in the same way in the RFC_READ_TABLE function module.

@Amole renapurkar: Unfortunately this solution doesn't work because of our key structure. It is impossible to close down on the row number in the table from the primary key.

@Srinivas Adavi: It is an ongoing procedure and not processed only once. In case of own tables it is possible to add a special "row number" field, unfortunately you can't use this with standard SAP tables like KNA1 or something else.

@Christian Wohlfahrt: Thank you very much for this opportunity. I think this could solve the problem, I will try it today. It is possible to change our program flow logic so I will try it.

@Neil Woodruff: The package size isn't the problem, it's the access to specific row numbers. But I will try your way, too, this afternoon.

@Clemens Li: This also is a very interesting solution, I am not quite sure whether it will work because of the ">"-statement of the key, but I will try it.

Thank you guys for replying, I will try your solutions post the results.

Greetings,

Daniel

[former_member186741]

Hi Daniel,

sorry to keep going on about package size(boring) but I don't think I've outlined it's benefits properly. The use of it is perfect for you as you can loop through the first batch of packages 'ígnoring' all table entries and minimising the amount of memory used until you reach the package you really want to process. The psize could be 1000 or 10k or 100k whatever suits. Hope this clarifies.

Message was edited by: Neil Woodruff

[Former Member]

Hi Daniel,

Thank you for this thread it is really helpful for us at the moment, because we have nearly the same situation to handle with.

Would you please share the results with us.

Thank you in advance

Best regards

Bilyana