We're updating the issue view to help you get more done. 

Asset Manager Performance

Steps to reproduce

The assetmanager security layer ensures user are authorized to access
assets. It does that by checking all of the users roles against all of
the access control rules for a given event.

The problem is that a non-admin user with access to all of the admin
interface has about 100 extra roles due to the concept of role based
visibility in the admin interface. This leads to huge queries with
obviously bad performance.

For example, a default query would look like this:

select distinct snapshotDto
from Snapshot snapshotDto
where snapshotDto.mediaPackageId = ?1 and ((not snapshotDto = snapshotDto or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?3 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?5 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?6 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?7 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?8 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?9 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?10 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?11 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?12 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?13 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?14 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?15 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?16 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?17 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?18 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?19 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?20 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?21 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?22 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?23 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?24 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?25 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?26 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?27 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?28 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?29 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?30 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?31 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?32 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?33 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?34 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?35 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?36 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?37 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?38 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?39 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?40 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?41 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?42 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?43 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?44 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?45 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?46 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?47 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?48 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?49 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?50 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?51 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?52 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?53 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?54 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?55 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?56 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?57 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?58 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?59 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?60 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?61 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?62 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?63 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?64 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?65 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?66 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?67 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?68 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?69 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?70 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?71 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?72 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?73 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?74 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?75 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?76 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?77 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?78 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?79 and p.boolValue = ?4)) and snapshotDto.organizationId = ?80)

UI roles are usually not used for any access control though and could
therefore be excluded in most cases. This patch adds an option to allow
for this pre-filtering of roles. The UI roles are excluded by default.

With the filtering, the same query looks like this:

select distinct snapshotDto
from Snapshot snapshotDto
where snapshotDto.mediaPackageId = ?1 and ((exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?3 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?5 and p.boolValue = ?4) or exists (select p
from Property p
where snapshotDto.mediaPackageId = p.mediaPackageId and p.namespace = ?2 and p.propertyName = ?6 and p.boolValue = ?4)) and snapshotDto.organizationId = ?7)

Status

Assignee

Lars Kiesow

Reporter

Lars Kiesow

Severity

Performance

Tags (folksonomy)

None

Components

Affects versions

7.0

Priority

Major