Batch-loader or preloading with destroy and dependent: :destroy associations N+1 problems.

[ghost]

In Rails there is the model.destroy(id) command to delete (with callbacks) a model. Good.

But when you use dependent: :destroy with has_many or has_one it has fun never ending with N+1 queries!!! :(

Especially if I use polymorhpic associations.

See this:

Started POST "/api/v1" for 172.18.0.1 at 2017-12-28 00:05:38 +0000
Processing by GraphqlController#execute as */*
  Parameters: {"query"=>"mutation deleteProductMutation {\n  deleteProduct(id: 1) {\n    id\n  }\n}\n", "graphql"=>{"query"=>"mutation deleteProductMutation {\n  deleteProduct(id: 1) {\n    id\n  }\n}\n"}}
  User Load (1.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  Product Load (0.4ms)  SELECT  "products".* FROM "products" WHERE "products"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
   (0.3ms)  BEGIN
  Team Load (0.4ms)  SELECT  "teams".* FROM "teams" WHERE "teams"."product_id" = $1 LIMIT $2  [["product_id", 1], ["LIMIT", 1]]
  MotherItem Load (0.5ms)  SELECT "game_items".* FROM "game_items" WHERE "game_items"."team_id" = $1  [["team_id", 1]]
  PlayerProfile Load (1.3ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 1], ["profileable_type", "MotherItem"], ["LIMIT", 1]]
  SQL (0.5ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 1]]
  SQL (0.3ms)  DELETE FROM "game_items" WHERE "game_items"."id" = $1  [["id", 1]]
  PlayerProfile Load (0.5ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 2], ["profileable_type", "MotherItem"], ["LIMIT", 1]]
  SQL (0.6ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 2]]
  SQL (0.8ms)  DELETE FROM "game_items" WHERE "game_items"."id" = $1  [["id", 2]]
  PlayerProfile Load (0.6ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 3], ["profileable_type", "MotherItem"], ["LIMIT", 1]]
  SQL (0.5ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 3]]
  SQL (0.3ms)  DELETE FROM "game_items" WHERE "game_items"."id" = $1  [["id", 3]]
  PlayerItem Load (0.5ms)  SELECT "sister_items".* FROM "sister_items" WHERE "sister_items"."team_id" = $1  [["team_id", 1]]
  PlayerProfile Load (0.5ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 1], ["profileable_type", "PlayerItem"], ["LIMIT", 1]]
  SQL (0.4ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 4]]
  SQL (0.4ms)  DELETE FROM "sister_items" WHERE "sister_items"."id" = $1  [["id", 1]]
  PlayerProfile Load (0.6ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 2], ["profileable_type", "PlayerItem"], ["LIMIT", 1]]
  SQL (0.5ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 5]]
  SQL (0.7ms)  DELETE FROM "sister_items" WHERE "sister_items"."id" = $1  [["id", 2]]
  PlayerProfile Load (0.4ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 3], ["profileable_type", "PlayerItem"], ["LIMIT", 1]]
  SQL (0.3ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 6]]
  SQL (0.3ms)  DELETE FROM "sister_items" WHERE "sister_items"."id" = $1  [["id", 3]]
  PlayerProfile Load (0.4ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 4], ["profileable_type", "PlayerItem"], ["LIMIT", 1]]
  SQL (1.4ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 7]]
  SQL (0.5ms)  DELETE FROM "sister_items" WHERE "sister_items"."id" = $1  [["id", 4]]
  PlayerProfile Load (0.5ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 5], ["profileable_type", "PlayerItem"], ["LIMIT", 1]]
  SQL (0.5ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 8]]
  SQL (0.5ms)  DELETE FROM "sister_items" WHERE "sister_items"."id" = $1  [["id", 5]]
  PlayerProfile Load (0.4ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 6], ["profileable_type", "PlayerItem"], ["LIMIT", 1]]
  SQL (1.3ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 9]]
  SQL (1.9ms)  DELETE FROM "sister_items" WHERE "sister_items"."id" = $1  [["id", 6]]
  FootballItem Load (1.4ms)  SELECT "football_items".* FROM "football_items" WHERE "football_items"."team_id" = $1  [["team_id", 1]]
  PlayerProfile Load (1.0ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 1], ["profileable_type", "FootballItem"], ["LIMIT", 1]]
  SQL (0.6ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 10]]
  SQL (1.8ms)  DELETE FROM "football_items" WHERE "football_items"."id" = $1  [["id", 1]]
  PlayerProfile Load (0.3ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 2], ["profileable_type", "FootballItem"], ["LIMIT", 1]]
  SQL (1.0ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 11]]
  SQL (0.4ms)  DELETE FROM "football_items" WHERE "football_items"."id" = $1  [["id", 2]]
  PlayerProfile Load (0.4ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 3], ["profileable_type", "FootballItem"], ["LIMIT", 1]]
  SQL (0.6ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 12]]
  SQL (0.6ms)  DELETE FROM "football_items" WHERE "football_items"."id" = $1  [["id", 3]]
  PlayerProfile Load (0.9ms)  SELECT  "player_profiles".* FROM "player_profiles" WHERE "player_profiles"."profileable_id" = $1 AND "player_profiles"."profileable_type" = $2 LIMIT $3  [["profileable_id", 4], ["profileable_type", "FootballItem"], ["LIMIT", 1]]
  SQL (0.6ms)  DELETE FROM "player_profiles" WHERE "player_profiles"."id" = $1  [["id", 13]]
  SQL (0.3ms)  DELETE FROM "football_items" WHERE "football_items"."id" = $1  [["id", 4]]
  SQL (2.7ms)  DELETE FROM "teams" WHERE "teams"."id" = $1  [["id", 1]]
  SQL (2.6ms)  DELETE FROM "products" WHERE "products"."id" = $1  [["id", 1]]
   (3.3ms)  COMMIT
Completed 200 OK in 522ms (Views: 0.4ms | ActiveRecord: 80.5ms)

user: root
POST /api/v1
USE eager loading detected
  MotherItem => [:player_profile]
  Add to your finder: :includes => [:player_profile]
Call stack
  /app/graphql/delete.rb:15:in `call'
  /app/controllers/graphql_controller.rb:11:in `execute'

user: root
POST /api/v1
USE eager loading detected
  PlayerItem => [:player_profile]
  Add to your finder: :includes => [:player_profile]
Call stack
  /app/graphql/delete.rb:15:in `call'
  /app/controllers/graphql_controller.rb:11:in `execute'

user: root
POST /api/v1
USE eager loading detected
  FootballItem => [:player_profile]
  Add to your finder: :includes => [:player_profile]
Call stack
  /app/graphql/delete.rb:15:in `call'
  /app/controllers/graphql_controller.rb:11:in `execute'

As you can see gem Bullet (https://github.com/flyerhzm/bullet) detects N+1 problems...

Is there a way to handle this? Maybe with the same gem for batching and preloader? (https://github.com/Shopify/graphql-batch) - (https://github.com/ConsultingMD/graphql-preload) ?

[tb]

I added Bullet, but I did not see this issue. Can you reproduce it on this project?

ATM I use services/includes.rb based on nettofarah/graphql-query-resolver#6 to avoid N+1 queries ... but it wont be not be called on destroy...

graphql-query-resolver supports well the nested object fields sorting - in particular sort by supplier address country, but that solution is not good for deep nested objects, see nettofarah/graphql-query-resolver#9

[ghost]

I don't know https://github.com/nettofarah/graphql-query-resolver, but what do you think about this https://github.com/ConsultingMD/graphql-preload?

Is this the same problem they try to fix?

graphql-preload is using graphql-batch in background...

What do you think?

[tb]

I tried graphql-preload and it did not work for sorting nested objects because preload makes separate SQL query.

[ghost]

I am very confused about this subject. What's the real difference between these?

• Shopify: graphql-batch, https://github.com/Shopify/graphql-batch
• https://github.com/nettofarah/graphql-query-resolver
• https://github.com/ConsultingMD/graphql-preload

[tb]

Batch loader concept is well documented here: https://github.com/exAspArk/batch-loader#graphql-example and https://github.com/exAspArk/batch-loader#implementation-details

Both of those gems use http://graphql-ruby.org/schema/lazy_execution.html

The graphql-preload is simple DSL to preloaded in fields, it originates from Shopify/graphql-batch#24 (comment)

It looks a little bit nicer than stacking a bunch of AssociationLoader's together in your resolve procs, and is pretty similar to the includes(comments: :author) syntax that Rails gives you.

graphql-query-resolver is similar to the graphql-preload but instead of DLS it uses graphql AST to generates the associations map.

Wherever loader uses preloader or includes does not matter as long as its possible to customize it. batch-loader and graphql-preload have preload_association method that could be modified to do records.includes(association) and then it should do the job for the nested object field sorting.

Another aspects is that according to mickhansen/graphql-sequelize#316 includes are slower... but I need them for the nested sorting. I used graphql-query-resolver and includes because it created a zero configuration option that works well in the nested sorting scenario.

Overall, batch loader is more flexible, faster and I should migrate to it.