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

Improve performance of deletion of asset manager properties.

Description

We ran into problems with a valid bugfix (https://github.com/opencast/opencast/pull/580) because the SQL query (unknown to me) Opencast generates causes massive performance problems.

On our test environment with more than 1 million entries in the table oc_assets_properties, I've tested some manuel naive SQL queries that would perform the job Opencast should.

Mediapackage "795bf83d-9d3b-40a5-aae8-50df6f2b73ec" has 1071 properties (artificially generated).

Here some benchmark results:

select * from oc_assets_properties where mediapackage_id = "795bf83d-9d3b-40a5-aae8-50df6f2b73ec";
approx. 3ms, results 1071 properties

select * from oc_assets_properties where mediapackage_id = "795bf83d-9d3b-40a5-aae8-50df6f2b73ec" and namespace = "org.opencastproject.scheduler.ca.configuration";
approx. 4ms, results 23 properties

delete from oc_assets_properties where mediapackage_id = "795bf83d-9d3b-40a5-aae8-50df6f2b73ec" and namespace = "org.opencastproject.scheduler.ca.configuration";
approx. 5ms

This makes me think that the DSL Opencast uses (e.g. in PR #580 results in some very inefficient database queries). Not verified, though.

Steps to reproduce

None

Status

Assignee

Unassigned

Reporter

Sven Stauber

Criticality

None

Tags (folksonomy)

None

Components

Fix versions

Affects versions

6.1

Priority

Minor