I currently have a requirement that involves getting the latest Document Number in table BKPF
. As per my analysis, the only filters I can use are the Company Code and Document Type. I tried sorting BKPF
via the Doc Num in Ascending to use the dates present in the BKPF
but the dates aren't sequential, so I can't use a date range as one of the filters. I'm currently stuck if I should use a SELECT
with the filters and then SORT
the itab by Doc Num in Descending order:
SELECT *
INTO TABLE gt_bkpf
FROM bkpf
WHERE bukrs EQ ls_upload-bukrs
AND blart EQ ls_upload-blart.
SORT gt_bkpf ASCENDING BY belnr.
DESCRIBE TABLE gt_bkpf LINES lv_lastdoc.
READ TABLE gt_bkpf INTO gs_bkpf INDEX lv_lastdoc.
or just use SELECT UP TO 1 ROWS
with the filters and ORDER by BELNR
:
SELECT belnr
UP TO 1 ROWS
INTO lv_belnr
FROM bkpf
WHERE bukrs EQ ls_upload-bukrs
AND blart EQ ls_upload-blart
ORDER BY belnr DESCENDING.
ENDSELECT.
I worry about the speed of the statements, will the UP TO 1 ROWS
be faster? (If so, by how much) Is there a better way of getting the latest Document Number (maybe like a Function Module or etc.)? Thank you in advance! :)
First, avoid selecting unnecessary data like SELECT *
Aways reduce the dataset to the absolut mininum required to solve the task. It became much more handy with ABAP 7.4, where one can write SELECT ... INTO TABLE @DATA(it_result)
and the table type would be automatically created in accordance with the selection criteria. Moreover, using table column store makes the selection of the whole row data (like SELECT *) even much more inefficient.
Second, try to avoid transferring big raw datasets (large amount of data, especially combined with SELECT * ... ) to the application server for post-processing when it is possible to do efficiently on the dabase.
You just need one document number, so SELECT belnr UP TO 1 ROWS is the right choice, delegating the search, sorting and reducing the dataset to the database. In this case the index including columns belnr and blart already exists for the table (you can always check the table indexes via transaction SE11
, Indexes...
)