SQL: Investigate number of queries being performed

[reinkrul]

While working on Prometheus metrics, I discovered that creating a subject (which creates 2 DIDs, did:web and did:nuts) caused 37 SQL queries to be executed. This sounds like too many for me.

Creating a subject

It seems that queries are retried multiple times, maybe due to the way we use GORM?

sql-query-log.txt

E.g.:

INSERT INTO `did_verification_method` (`id`,`key_types`,`weight`,`data`) VALUES ("did:web:localhost%3A8080:iam:45d8be72-0272-4120-83a8-bb097152d315#96ceae8f-074e-42a7-b834-3d7839b6fa74",15,0,"{""id"":""did:web:localhost%3A8080:iam:45d8be72-0272-4120-83a8-bb097152d315#96ceae8f-074e-42a7-b834-3d7839b6fa74"",""type"":""JsonWebKey2020"",""controller"":""did:web:localhost%3A8080:iam:45d8be72-0272-4120-83a8-bb097152d315"",""publicKeyJwk"":{""crv"":""P-256"",""kty"":""EC"",""x"":""e1dbsc52D0I8MXEfLXO-iqK8_g5poKOmcVH9qEOPA9U"",""y"":""TjQZaIPypyR43MDRDIY022oZRsQ6NAdmKyD9N_Q2GQ8""}}") ON CONFLICT DO NOTHING

[woutslakhorst]

When creating a DID, we SaveOrUpdate the DocumentVersion but later also store the DID_CHANGE_LOG which references the DIDDocument. This might be needed due to some foreign key dependency but the DID_CHANGE_LOG save should not save all relations again. DIDChangeLog.DIDDocumentVersion might be made ignorable on save?

[reinkrul]

I decided to look at API operations that are more frequently used. E.g., looking up a DID document:

nodeB-backend-1  | time="2025-05-05T05:48:29Z" level=trace msg="Query (took 42.959µs): SELECT * FROM `did` WHERE `did`.`id` = \"did:web:nodeB:iam:83a86457-5a7c-4d31-b4b7-d634ca74034e\"" module=Storage
nodeB-backend-1  | time="2025-05-05T05:48:29Z" level=trace msg="Query (took 15.625µs): SELECT * FROM `did_document_to_service` WHERE `did_document_to_service`.`did_document_id` = \"c216256b-d61a-4118-b8e8-91d8e56452a5\"" module=Storage
nodeB-backend-1  | time="2025-05-05T05:48:29Z" level=trace msg="Query (took 18.125µs): SELECT * FROM `did_document_to_verification_method` WHERE `did_document_to_verification_method`.`did_document_id` = \"c216256b-d61a-4118-b8e8-91d8e56452a5\"" module=Storage
nodeB-backend-1  | time="2025-05-05T05:48:29Z" level=trace msg="Query (took 19.583µs): SELECT * FROM `did_verification_method` WHERE `did_verification_method`.`id` = \"did:web:nodeB:iam:83a86457-5a7c-4d31-b4b7-d634ca74034e#0e9ac5c8-c21a-4fc8-8a47-77cd804a3ec0\"" module=Storage
nodeB-backend-1  | time="2025-05-05T05:48:29Z" level=trace msg="Query (took 217.5µs): SELECT * FROM `did_document_version` WHERE did = \"did:web:nodeB:iam:83a86457-5a7c-4d31-b4b7-d634ca74034e\" AND updated_at <= 1746427709 ORDER BY version desc,`did_document_version`.`id` LIMIT 1" module=Storage

[reinkrul]

It caused by our use of Preload instead of Join. The first populates the field with the related entity with a separate query, the latter uses a join. However, many2many relationships can't be preloaded using join: go-gorm/gorm#6715

Maybe with a workaround.