Issue

When registering new cmHandle,  CPS will get module sets and yang resources from DMI model plugin. After module set response is received from the DMI Model Plugin, CPS will try to determine the known yang resources to avoid getting those from the Model Plugin again:

cpsModuleService.getYangResourceModuleReferences(NFP_OPERATIONAL_DATASTORE_DATASPACE_NAME);

With increasing schema-sets in the database, this query gets slower.

The query is executed for every cm handle and even if it takes 2 seconds, it will consume a considerable time for 10 or 20 thousand requests: it can take 2 * 10 000 seconds ~ 333 minutes.

Steps to reproduce

Register large number of cm handles and execute a single registration on top of them.

RCA

The executed query for the above operation is from DB logs:

2022-01-21 16:33:58.086 UTC [67] LOG:  execute S_17: SELECT DISTINCT
	yang_resource.module_name AS module_name,
	yang_resource.revision AS revision
	FROM
	dataspace
	JOIN schema_set ON schema_set.dataspace_id = dataspace.id
	JOIN schema_set_yang_resources ON schema_set_yang_resources.schema_set_id = schema_set.id
	JOIN yang_resource ON yang_resource.id = schema_set_yang_resources.yang_resource_id
	WHERE
	dataspace.name = $1
2022-01-21 16:33:58.086 UTC [67] DETAIL:  parameters: $1 = 'NFP-Operational'

This takes around 2 seconds, unfortunately this SELECT DISTINCT operation is expensive on the current database design.

Possible solution

Not known. I have experimented with indices, but not managed to reach a significant improvement.

There is only a possible workaround. Since NFP -Operational dataspace is hardcoded in the current implementation, so the above query can be simplified to selecting the distinct yang resources from the `yang_resource` table only. This executes in under 10 ms.

Possibly some new association table needs to be introduced which allows easier extraction of the needed information.

1) Suggestion ToineSiebelink

See https://wiki.onap.org/display/DW/CPS+Internal+Relation+DB+Schema

Update yang_resource table to include database_id field (assuming same yang resources in different DBs wil be stored multiple times, if this is not already so we can make it so!)

Watch out for unique constraint on checksum (included new db-id field)

Then the query for existing yang-resources can be done on 1 table instead of current join of 3 tables!

COSTLY because of Liquibase changeset changes

2) just get ALL module references in system (irrespective of dataspace name). No liquibase changesets
Need to test with a module that exists in a DIFFERENT dataspace
In memory transfer of data between NCMP and CPS Core (using Java interface!)

3a) Change algorithm to make CPS-Core check for new Modules (much less I/O but possible many small queries)
Might get slower when many client have many different modules. Any reason this was done in NCMP instead of CPS (check with tfinnerty ).  No Liquibase changesets. PoC with assumption of 100 modules in node (10% new), 2,000 in Database already. Thus should be know in about 10ms

3b) Alt to 3a using a temp table then get missing/new module references (using native query using left join) (like src/main/java/org/onap/cps/spi/repository/FragmentRepositoryCpsPathQueryImpl.java)

Team has chosen to implement alt 3b