Improve caching of SQL queries
Problem to solve
It seems that we do not cache any of import
issued SELECTS, this results in a number of redundant
SQL queries executed to fetch the same data over and over.
Ideally, we should consider not only caching queries, but also the whole object:
- SQL caching is cheap, as we can cleanup memory when needed,
- Caching objects is more efficient, as we cache fully initialised objects that significantly increase the performance of object access: there's a big penalty on initialising AR object from SQL results.
Maybe we should consider introducing some LRU cache to group_project_object_builder.rb
,
and all other places we search for objects.
D, [2019-12-09T17:09:38.789175 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.789832 #64] DEBUG -- : Label Load (0.3ms) SELECT "labels".* FROM "labels" WHERE "labels"."project_id" = 43 AND "labels"."title" = 'XXX' ORDER BY "labels"."title" ASC LIMIT $1 [["LIMIT", 1]]
D, [2019-12-09T17:09:38.789896 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:37
D, [2019-12-09T17:09:38.790332 #64] DEBUG -- : (0.2ms) COMMIT
D, [2019-12-09T17:09:38.790387 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.791105 #64] DEBUG -- : (0.2ms) BEGIN
D, [2019-12-09T17:09:38.791164 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.791677 #64] DEBUG -- : Label Load (0.2ms) SELECT "labels".* FROM "labels" WHERE "labels"."project_id" = 43 AND "labels"."title" = 'XXX' ORDER BY "labels"."title" ASC LIMIT $1 [["LIMIT", 1]]
D, [2019-12-09T17:09:38.791758 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:37
D, [2019-12-09T17:09:38.792172 #64] DEBUG -- : (0.2ms) COMMIT
D, [2019-12-09T17:09:38.792228 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.792837 #64] DEBUG -- : (0.2ms) BEGIN
D, [2019-12-09T17:09:38.792886 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.793550 #64] DEBUG -- : Label Load (0.3ms) SELECT "labels".* FROM "labels" WHERE "labels"."project_id" = 43 AND "labels"."title" = 'XX' ORDER BY "labels"."title" ASC LIMIT $1 [["LIMIT", 1]]
D, [2019-12-09T17:09:38.793616 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:37
D, [2019-12-09T17:09:38.794032 #64] DEBUG -- : (0.2ms) COMMIT
D, [2019-12-09T17:09:38.794086 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.794782 #64] DEBUG -- : (0.2ms) BEGIN
D, [2019-12-09T17:09:38.794841 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.795552 #64] DEBUG -- : Label Load (0.3ms) SELECT "labels".* FROM "labels" WHERE "labels"."project_id" = 43 AND "labels"."title" = 'XXX' ORDER BY "labels"."title" ASC LIMIT $1 [["LIMIT", 1]]
D, [2019-12-09T17:09:38.795618 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:37
D, [2019-12-09T17:09:38.796081 #64] DEBUG -- : (0.2ms) COMMIT
D, [2019-12-09T17:09:38.796129 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.796858 #64] DEBUG -- : (0.2ms) BEGIN
D, [2019-12-09T17:09:38.796927 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.797539 #64] DEBUG -- : Label Load (0.2ms) SELECT "labels".* FROM "labels" WHERE "labels"."project_id" = 43 AND "labels"."title" = 'XXX' ORDER BY "labels"."title" ASC LIMIT $1 [["LIMIT", 1]]
D, [2019-12-09T17:09:38.797602 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:37
D, [2019-12-09T17:09:38.798003 #64] DEBUG -- : (0.2ms) COMMIT
D, [2019-12-09T17:09:38.798058 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.799213 #64] DEBUG -- : (0.7ms) BEGIN
D, [2019-12-09T17:09:38.799274 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.799857 #64] DEBUG -- : Label Load (0.2ms) SELECT "labels".* FROM "labels" WHERE "labels"."project_id" = 43 AND "labels"."title" = 'XX' ORDER BY "labels"."title" ASC LIMIT $1 [["LIMIT", 1]]
D, [2019-12-09T17:09:38.799920 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:37
D, [2019-12-09T17:09:38.800257 #64] DEBUG -- : (0.2ms) COMMIT
D, [2019-12-09T17:09:38.800310 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.801226 #64] DEBUG -- : (0.2ms) BEGIN
D, [2019-12-09T17:09:38.801284 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.801842 #64] DEBUG -- : Milestone Load (0.3ms) SELECT "milestones".* FROM "milestones" WHERE "milestones"."project_id" = 43 AND "milestones"."title" = 'XXX' ORDER BY "milestones"."id" ASC LIMIT $1 [["LIMIT", 1]]
D, [2019-12-09T17:09:38.801902 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:37
D, [2019-12-09T17:09:38.802249 #64] DEBUG -- : (0.2ms) COMMIT
D, [2019-12-09T17:09:38.802301 #64] DEBUG -- : ↳ lib/gitlab/import_export/group_project_object_builder.rb:16
D, [2019-12-09T17:09:38.802920 #64] DEBUG -- : Event Load (0.2ms) SELECT "events".* FROM "events" WHERE "events"."target_id" = $1 AND "events"."target_type" = $2 [["target_id", 354], ["target_type", "Milestone"]]
D, [2019-12-09T17:09:38.802978 #64] DEBUG -- : ↳ lib/gitlab/import_export/relation_factory.rb:294
D, [2019-12-09T17:09:38.803304 #64] DEBUG -- : (0.2ms) BEGIN
D, [2019-12-09T17:09:38.803366 #64] DEBUG -- : ↳ lib/gitlab/import_export/relation_factory.rb:294
D, [2019-12-09T17:09:38.804016 #64] DEBUG -- : Event Destroy (0.3ms) DELETE FROM "events" WHERE "events"."target_id" = $1 AND "events"."target_type" = $2 AND "events"."id" = $3 [["target_id", 354], ["target_type", "Milestone"], ["id", 3317]]
D, [2019-12-09T17:09:38.804079 #64] DEBUG -- : ↳ lib/gitlab/import_export/relation_factory.rb:294
D, [2019-12-09T17:09:38.804919 #64] DEBUG -- : Event Create (0.3ms) INSERT INTO "events" ("project_id", "author_id", "target_id", "created_at", "updated_at", "action", "target_type") VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING "id" [["project_id", 43], ["author_id", 20], ["target_id", 354], ["created_at", "2019-05-17 10:15:05.147000"], ["updated_at", "2019-05-17 10:15:05.147000"], ["action", 1], ["target_type", "Milestone"]]