PROTECTION Has a huge performance impact

RentProperty

Hi Guys, sooo since realizing how easy it is for a user to get ALL the data (even data that doesn't belong to him) in a multi-tenancy app we've been trying to implement the READ PROTECTION as demonstrated in the Video by Vlad.

However... We noticed that these rules have a HUGE impact on the speed of the queries. Queries that use to display in a second now takes 4 - 5 seconds. (And that's a small query). Larger queries sometimes take up to 10 seconds to display any data. We thought ok... Maybe this is because we are still running V5.9 so we setup a V7 trial on a brand new EC2 machine and imported a copy of the live data onto a new DB so that the volumes for testing purposes can be the same.... The results were bit disheartening. It is obvious that using READ PROTECT is causing this Lag.

We did benchmark tests and you can check the before and After Screenshots below. The Rule on Property is very simple:
IF Property.Company<>LoggedInMMSAdministrator.Company
THEN READ PROTECT Property FROM ALL EXCEPT Administrator

We tried Different things such as Comparing the Actual ID, adding another Condition to Check Access level etc... Made no difference. This is a huge security concern at the moment and I was wondering if anyone else experiences the same thing or if a 5 Second waiting time on a small query is just generally excepted? 🙂 Sincerely hoping I'm missing something stupid here.
Thanks for any Feedback.

Benchmark - Running Query Without Protection Rule

Benchmark - Running Query With Protection Rule. (About 4 times slower)

weblike

Your benchmarks are very concerning.
Maybe Support can make a light on this.

BenHayat

Hein, have you tried to create composite indexes on those tables that are involved as part of the READ PROTECT query statement? A composite index consists of several attributes in certain order that are chained together to form one index.
I plan to do this when I go to production. As of now, you have to do it outside of Aware and anytime you make a change to DB which aware drops tables, you have to re-create them.

Previously, in ASP.Net, where I had to create my own "READ PROTECT" system, by tenant, adding composite indexes made a huge difference when I was for example looking at invoices for a specific tenant.
I added a new composite index for the invoice that combined <Company_ID> + <Invoice_ID>. Then anytime I was looking for invoices for any company, I'd provide <Company_ID> as part of the first element of my query and I would instantly get invoices (in 10,000,000) test table. But If I would delete that composite index, it would take much longer.

The trick is that, the sequence of elements in the composite index must be organized with the hierarchy of table relations. So, for a "Line item", the composite might be <Company_ID> + <Invoice_ID> + <LineItem_ID>.
Now if you look for item "654" for Company=111 and Invoice 222, you would get instant return.

Hope this helps.

RentProperty

Thanks Ben,
Yes we can certainly explore this for Slow queries etc, problem is the ID of both the user and the company is indexed by default. So when implementing the protection as Vladimir explains in the video it's not a complicated Check. I'm not sure if Aware perhaps Fetches all the columns for the Property, Administrator, and company in order to determine if a specific user can see the property or not based on whether the Administrator Company & The Property Company is the same?
If this is the case it will definitely make that process quite slow because then aware will fetch and company Binary data as well. Most companies have Logos etc... Still running some tests to see what we find.
Will update here if we make any discoveries. Hoping support can also shed some light on this or if they are selecting * he columns from company to do a ID comparison... perhaps look at a more flexible READ PROTECT where we can specify the columns to be checked rather than selecting all... Not sure if I'm making any sense. 🙂

BenHayat

RentProperty wrote
Thanks Ben,
Yes we can certainly explore this for Slow queries etc, problem is the ID of both the user and the company is indexed by default. So when implementing the protection as Vladimir explains in the video it's not a complicated Check. I'm not sure if Aware perhaps Fetches all the columns for the Property, Administrator, and company in order to determine if a specific user can see the property or not based on whether the Administrator Company & The Property Company is the same?
If this is the case it will definitely make that process quite slow because then aware will fetch and company Binary data as well. Most companies have Logos etc... Still running some tests to see what we find.
Will update here if we make any discoveries. Hoping support can also shed some light on this or if they are selecting * he columns from company to do a ID comparison... perhaps look at a more flexible READ PROTECT where we can specify the columns to be checked rather than selecting all... Not sure if I'm making any sense. 🙂

One thing to keep in mind. Keep your binary data out of the main object to another 1-1 object. For example, LoggedInCustomer in my case gets used up a lot in the system, and I used to have several images in the object. Every time a customer got fetched, from DB it got slow. I created a 1-1 object to store images and I only need the images once to show on a report, the rest of the time I don't use.

Take a look at this thread I made, on performance. It helped me a lot.
https://forum.awareim.com/viewtopic.php?p=39394#p39394

Jaymer

Is this still a big performance issue with V8? thx