Row security causes poor performance, a common misnomer

Introduction

The use of row security is not necessarily a performance bottleneck, or something that effects performance negatively.  Sometimes (and quite often) performance can improve with row security enabled.  You are actually processing less rows in many open queries and you are helping the optimiser with more columns to put into the QEP.

Firstly, all row security is cached on login, there is one select on the F00950 for all rows for the environment in question. The web engine caches all records so that there is no additional impact if there is additional records. I appreciate that there will be more records, but this load does not generally affect performance.

Secondly, row security is generally applied to “keyed” columns. Generally row security is applied to Company (CO) or business unit (MCU) fields. These fields are often in indexes and therefore, do not cause table scans or reduce performance. If it is seen that this does occur for some queries, additional indexes should be created.

Inclusive or exclusive?

When defining Row Security in the Security Workbench application (P00950), you have the option to define the row security based on either Inclusive or Exclusive Row Security settings.

Inclusive Row Security allows for defining valid ranges of values in the Row Security From and Thru Values and the user/role on those security records would have access to only records lying within that range. Any records outside of the defined range would be secured from the user/role.

This is the opposite of using exclusive row security where the ranges that are defined in the From and Thru Values are those that the user/role should not have access to. When defining Exclusive Row Security, the user will only have access to records that are not within the defined range.

I generally choose inclusive security.

Row security exceptions:

You cannot apply row security to bootstrap tables (in general).

The following is an example list of bootstrap tables : F0092, F0093, F0094, F98OWSEC, F00165, F00921, F00922, F00924, F00925, F00926, F00941, F00942, F00945, F00948, F00960, F9200, F9202, F9203, F9207, F9210, F9211, F9312, F9650, F9860, F9861, F9862, F9863, F9865, F9885, F9886, F9887, F9888, F9889, F95921, F98101, F98611, F98613, F98710, F98712, F98713, F98720, F98740, F98741, F98743, F98745, F98750, F98751, F98752, F98753, F98760, F98761, F98762, F98891, F98950, F983051, F986101, F986110, F986111.

Conclusion:

The introduction of row security can have positive effects on performance. Quite often it will reduce data sets being processed for many batch and interactive application. It’s a way of enforcing a quasi “QBE entry” against company or MCU.

In general these columns are keyed well, if you find that there are hot-spots, some custom indexes will address this quickly.