ACL creates heavy and slow database traffic

[mkrecek234]

Steps to reproduce:

• Have an application with more complex model structure (multiple hasOnes, hasManys)
• Set-up just one rule and activate ACL by setACL();

Issue:

• Database traffic is MASSIVE due to the login_access_rule queries
• Even if you index the related Atk4/login tables properly, it makes a difference between 0.2 sec to 4 sec. in loading time for comparably simple scripts.

[mvorisek]

please provide code example of steps to reproduce

the best way to verify it is to place it into a unit test and enable https://github.com/atk4/data/blob/3.1.0/src/Schema/TestCase.php#L23

then the queries should be dumped

[mvorisek]

when ACL rule can include/exclude the whole table, I belive the generated SQL should be much more performant

[mvorisek]

Database traffic is MASSIVE due to the login_access_rule queries

@mkrecek234 do you mean much more queries are generated or they are only slower?

[mkrecek234]

@mvorisek It results in a lot of additional queries being executed as ACK rules are checked for each field. I noticed if you have models which by default are adding a lot of hasOne fields and thus joining a lot of queries (in my case even over multiple join cascades), this reduces the database speed a lot. In my case ACL was resulting in 4-5sec queries, whereas without ACk it was far below 1sec.

[mvorisek]

in a lot of additional queries being executed

yes, larger queries are expected and as I have written, it would make sense to optimize the case when user has ACL for whole table only, thus ACL would not be needed to be checked per row

my question is, if the queries are only larger/slower, or if you meant more (much more) queries in total are executed

it would be helpful if you can attach minimalistic SQL log /w and /wo ACL enabled