perf: replace TypeOrm `findOne` queries including relations with `find`

S3bb1 commented 5 days ago

What issue is this PR fixing

This PR addresses a performance issue with TypeORM (https://github.com/typeorm/typeorm/issues/5694)

When using findOne() with attached relations, TypeORM executes two queries:

query: SELECT DISTINCT "distinctAlias"."Identifier_did" AS "ids_Identifier_did" FROM (SELECT "Identifier"."did" AS "Identifier_did", "Identifier"."provider" AS "Identifier_provider", "Identifier"."alias" AS "Identifier_alias", "Identifier"."controllerKeyId" AS "Identifier_controllerKeyId", "Identifier__Identifier_keys"."kid" AS "Identifier__Identifier_keys_kid", "Identifier__Identifier_keys"."kms" AS "Identifier__Identifier_keys_kms", "Identifier__Identifier_keys"."type" AS "Identifier__Identifier_keys_type", "Identifier__Identifier_keys"."publicKeyHex" AS "Identifier__Identifier_keys_publicKeyHex", "Identifier__Identifier_keys"."meta" AS "Identifier__Identifier_keys_meta", "Identifier__Identifier_keys"."identifierDid" AS "Identifier__Identifier_keys_identifierDid", "Identifier__Identifier_services"."id" AS "Identifier__Identifier_services_id", "Identifier__Identifier_services"."type" AS "Identifier__Identifier_services_type", "Identifier__Identifier_services"."serviceEndpoint" AS "Identifier__Identifier_services_serviceEndpoint", "Identifier__Identifier_services"."description" AS "Identifier__Identifier_services_description", "Identifier__Identifier_services"."identifierDid" AS "Identifier__Identifier_services_identifierDid" FROM "identifier" "Identifier" LEFT JOIN "key" "Identifier__Identifier_keys" ON "Identifier__Identifier_keys"."identifierDid"="Identifier"."did"  LEFT JOIN "service" "Identifier__Identifier_services" ON "Identifier__Identifier_services"."identifierDid"="Identifier"."did" WHERE (("Identifier"."did" = $1))) "distinctAlias" ORDER BY "Identifier_did" ASC LIMIT 1 -- PARAMETERS: ["did:ethr:xyz"]
query: SELECT "Identifier"."did" AS "Identifier_did", "Identifier"."provider" AS "Identifier_provider", "Identifier"."alias" AS "Identifier_alias", "Identifier"."controllerKeyId" AS "Identifier_controllerKeyId", "Identifier__Identifier_keys"."kid" AS "Identifier__Identifier_keys_kid", "Identifier__Identifier_keys"."kms" AS "Identifier__Identifier_keys_kms", "Identifier__Identifier_keys"."type" AS "Identifier__Identifier_keys_type", "Identifier__Identifier_keys"."publicKeyHex" AS "Identifier__Identifier_keys_publicKeyHex", "Identifier__Identifier_keys"."meta" AS "Identifier__Identifier_keys_meta", "Identifier__Identifier_keys"."identifierDid" AS "Identifier__Identifier_keys_identifierDid", "Identifier__Identifier_services"."id" AS "Identifier__Identifier_services_id", "Identifier__Identifier_services"."type" AS "Identifier__Identifier_services_type", "Identifier__Identifier_services"."serviceEndpoint" AS "Identifier__Identifier_services_serviceEndpoint", "Identifier__Identifier_services"."description" AS "Identifier__Identifier_services_description", "Identifier__Identifier_services"."identifierDid" AS "Identifier__Identifier_services_identifierDid" FROM "identifier" "Identifier" LEFT JOIN "key" "Identifier__Identifier_keys" ON "Identifier__Identifier_keys"."identifierDid"="Identifier"."did"  LEFT JOIN "service" "Identifier__Identifier_services" ON "Identifier__Identifier_services"."identifierDid"="Identifier"."did" WHERE ( (("Identifier"."did" = $1)) ) AND ( "Identifier"."did" IN ($2) ) -- PARAMETERS: ["did:ethr:xyz","did:ethr:xyz"]

the two queries are specially gaining performance when doing pagination (where we have skip and take). In the datastore we explicitly don't want/need to page, just getting the first entry back.

When executing the query with just a "find" and taking the first element in the array, TypeORM executes only one query:

query: SELECT "Identifier"."did" AS "Identifier_did", "Identifier"."provider" AS "Identifier_provider", "Identifier"."alias" AS "Identifier_alias", "Identifier"."controllerKeyId" AS "Identifier_controllerKeyId", "Identifier__Identifier_keys"."kid" AS "Identifier__Identifier_keys_kid", "Identifier__Identifier_keys"."kms" AS "Identifier__Identifier_keys_kms", "Identifier__Identifier_keys"."type" AS "Identifier__Identifier_keys_type", "Identifier__Identifier_keys"."publicKeyHex" AS "Identifier__Identifier_keys_publicKeyHex", "Identifier__Identifier_keys"."meta" AS "Identifier__Identifier_keys_meta", "Identifier__Identifier_keys"."identifierDid" AS "Identifier__Identifier_keys_identifierDid", "Identifier__Identifier_services"."id" AS "Identifier__Identifier_services_id", "Identifier__Identifier_services"."type" AS "Identifier__Identifier_services_type", "Identifier__Identifier_services"."serviceEndpoint" AS "Identifier__Identifier_services_serviceEndpoint", "Identifier__Identifier_services"."description" AS "Identifier__Identifier_services_description", "Identifier__Identifier_services"."identifierDid" AS "Identifier__Identifier_services_identifierDid" FROM "identifier" "Identifier" LEFT JOIN "key" "Identifier__Identifier_keys" ON "Identifier__Identifier_keys"."identifierDid"="Identifier"."did"  LEFT JOIN "service" "Identifier__Identifier_services" ON "Identifier__Identifier_services"."identifierDid"="Identifier"."did" WHERE (("Identifier"."did" = $1)) -- PARAMETERS: ["did:ethr:xyz"]

This results in a performance boost when generating VPs (in our case the "find" executes in half of the time as with "findOne")

Quality

Check all that apply:

[X] I want these changes to be integrated
[X] I successfully ran pnpm i, pnpm build, pnpm test, pnpm test:browser locally.
[X] I allow my PR to be updated by the reviewers (to speed up the review process).
[ ] I added unit tests.
[ ] I added integration tests.
[X] I did not add automated tests because no logic was changed, and I am aware that a PR without tests will likely get rejected.

codecov-commenter commented 4 days ago

Codecov Report

All modified and coverable lines are covered by tests :white_check_mark:

Project coverage is 89.79%. Comparing base (925fd81) to head (c44954b).

Additional details and impacted files

```diff @@ Coverage Diff @@ ## next #1400 +/- ## ======================================= Coverage 89.79% 89.79% ======================================= Files 176 176 Lines 27791 27791 Branches 2208 2208 ======================================= Hits 24954 24954 Misses 2837 2837 ```

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

S3bb1 commented 4 days ago

Hi @mirceanis

I checked all findOne references in the code. The only "bad" findOnes are those which have included relations setting.

decentralized-identity / veramo