-
Improve the performance of the GET /:sources/:id/{access_requests,members} API endpoints · 5010be77Rémy Coutable authored
The performance was greatly improved by removing two N+1 queries issues for each endpoint. For comparison: 1. `GET /projects/:id/members` With two N+1 queries issues (one was already fxed in the following snippet): ``` ProjectMember Load (0.2ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = $1 AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $2 AND "members"."source_type" = $3 AND "members"."type" IN ('ProjectMember') AND "members"."requested_at" IS NULL ORDER BY "members"."id" DESC [["source_type", "Project"], ["source_id", 1], ["source_type", "Project"]] User Load (0.5ms) SELECT "users".* FROM "users" WHERE "users"."id" IN (5, 22, 16, 13) ORDER BY "users"."id" DESC ActiveRecord::SchemaMigration Load (0.2ms) SELECT "schema_migrations".* FROM "schema_migrations" ProjectMember Load (0.3ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = $1 AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $2 AND "members"."source_type" = $3 AND "members"."type" IN ('ProjectMember') AND "members"."requested_at" IS NULL AND "members"."user_id" = $4 ORDER BY "members"."id" DESC LIMIT 1 [["source_type", "Project"], ["source_id", 1], ["source_type", "Project"], ["user_id", 5]] ProjectMember Load (0.3ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = $1 AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $2 AND "members"."source_type" = $3 AND "members"."type" IN ('ProjectMember') AND "members"."requested_at" IS NULL AND "members"."user_id" = $4 ORDER BY "members"."id" DESC LIMIT 1 [["source_type", "Project"], ["source_id", 1], ["source_type", "Project"], ["user_id", 22]] ProjectMember Load (0.3ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = $1 AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $2 AND "members"."source_type" = $3 AND "members"."type" IN ('ProjectMember') AND "members"."requested_at" IS NULL AND "members"."user_id" = $4 ORDER BY "members"."id" DESC LIMIT 1 [["source_type", "Project"], ["source_id", 1], ["source_type", "Project"], ["user_id", 16]] ProjectMember Load (0.3ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = $1 AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $2 AND "members"."source_type" = $3 AND "members"."type" IN ('ProjectMember') AND "members"."requested_at" IS NULL AND "members"."user_id" = $4 ORDER BY "members"."id" DESC LIMIT 1 [["source_type", "Project"], ["source_id", 1], ["source_type", "Project"], ["user_id", 13]] ``` Without the N+1 queries issues: ``` ProjectMember Load (0.3ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = $1 AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $2 AND "members"."source_type" = $3 AND "members"."type" IN ('ProjectMember') AND "members"."requested_at" IS NULL ORDER BY "members"."id" DESC LIMIT 20 OFFSET 0 [["source_type", "Project"], ["source_id", 1], ["source_type", "Project"]] User Load (0.5ms) SELECT "users".* FROM "users" WHERE "users"."id" IN (5, 22, 16, 13) ORDER BY "users"."id" DESC ``` 2. `GET /projects/:id/access_requests` With two N+1 queries issues: ``` ProjectMember Load (0.3ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = $1 AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $2 AND "members"."source_type" = $3 AND "members"."type" IN ('ProjectMember') AND ("members"."requested_at" IS NOT NULL) ORDER BY "members"."id" DESC [["source_type", "Project"], ["source_id", 8], ["source_type", "Project"]] User Load (0.1ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" DESC LIMIT 1 [["id", 24]] User Load (0.1ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" DESC LIMIT 1 [["id", 23]] ActiveRecord::SchemaMigration Load (0.2ms) SELECT "schema_migrations".* FROM "schema_migrations" ProjectMember Load (0.1ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = $1 AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $2 AND "members"."source_type" = $3 AND "members"."type" IN ('ProjectMember') AND ("members"."requested_at" IS NOT NULL) AND "members"."user_id" = $4 ORDER BY "members"."id" DESC LIMIT 1 [["source_type", "Project"], ["source_id", 8], ["source_type", "Project"], ["user_id", 24]] ProjectMember Load (0.2ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = $1 AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $2 AND "members"."source_type" = $3 AND "members"."type" IN ('ProjectMember') AND ("members"."requested_at" IS NOT NULL) AND "members"."user_id" = $4 ORDER BY "members"."id" DESC LIMIT 1 [["source_type", "Project"], ["source_id", 8], ["source_type", "Project"], ["user_id", 23]] ``` Without the N+1 queries issues: ``` ProjectMember Load (0.3ms) SELECT "members".* FROM "members" WHERE "members"."source_type" = $1 AND "members"."type" IN ('ProjectMember') AND "members"."source_id" = $2 AND "members"."source_type" = $3 AND "members"."type" IN ('ProjectMember') AND ("members"."requested_at" IS NOT NULL) ORDER BY "members"."id" DESC LIMIT 20 OFFSET 0 [["source_type", "Project"], ["source_id", 8], ["source_type", "Project"]] User Load (0.5ms) SELECT "users".* FROM "users" WHERE "users"."id" IN (24, 23) ORDER BY "users"."id" DESC ``` Signed-off-by: Rémy Coutable <remy@rymai.me>
5010be77
×