Authority check - better performance time (after select query or in select where condition)

[xiswanto]

Hello,

I recently got curious which is faster in term of performance time,
( example: I use an Z auth. object with 2 fields:
BUKRS, allowed value A001, A002, B001, B002, until Z001,
WERKS, allowed 1001, 1002 until 1099 )

Method 1. doing an authority check after a select query
whereas I do
- select data from ekko into itab
- loop at itab
- do authority-check on each row and remove non authorized row,

or

Method 2. put the authorized value into a variable, then use it in select .. where condition
ex: using FM get_auth_VALUES then with some logic, managed to put the allowed value inside a variable, such as
z_var = ( ( BUKRS = A001 OR BUKRS = A002 ... OR BUKRS = Z001 ) AND ( WERKS = 1001 OR WERKS = 1002 ... OR WERKS = 1099 ) ).
, then
select from ekko into itab WHERE ( z_var )

I realize that in method 1, the query would select more data and additional time for loop process compared to method 2, however I am unsure about my conclusion since I've encountered a case where it took longer query time to select data with where condition (not sure why this happen thus, I am not sure if having more where condition would make the query faster).
Thus, please share your thought or your experience, would method 1 be faster, or method 2 be faster in case of large data select (maybe 10.000+ record) 

Regards,

Siswanto

[Sandra_Rossi]

Q&A: one question, one answer, but here you want to initiate a discussion, so Application Development - SAP Community discussions would be a better place.

[Sandra_Rossi · Accepted solution]

Impossible to answer. It depends on many factors.

• Database system (in-memory versus disk)
• Total read versus total volume
• Existing database index (if applicable)
• etc.

The important fact is that database access is slower than access to buffered tables (authorizations), especially much slower in case of disk-storage database.

Note an answer to your question but you'd better use CL_AUTH_OBJECTS_TO_SQL. 1885409 - Use CL_AUTH_OBJECTS_TO_SQL for authority check - SAP for Me

[Tomas_Buryanek]

Method 2 is not using the Z auth. object. So in case of auth.object change you would have to update also the code? Not good.

I would probably select all company codes (BUKRS) and plants (WERKS) from master data tables (T001, T001W, or maybe T001K?...). Filter authorized ones with Z auth.object into the range tables. And finally do the main data select using the range tables. That is cleanest way in my opinion.