Situation

I'm currently working on a web API in ASP .NET Core. For this I make use of Entity Framework for database related operations. This API returns information about the game Azur Lane. One of my controllers is for retrieving information about "Ships". A ship is a pretty large object with a lot of stuff that needs to be included. Below you will see how I retrieve the set of ships with all the information linked to it. Recently I have added a new property to the so called "Ship" object called "ShipQuotes" which is a list of small objects (Id + 4 string properties). Of course I had to include this as well otherwise the API would return an empty list.

await _context.Ships
                    .Include(s => s.Stars)
                    .Include(s => s.Skins)
                    .Include(s => s.Skills)
                    .Include(s => s.LimitBreaks)
                    .Include(s => s.Gallery)
                    .Include(s => s.EquippableSlots)
                    .Include(s => s.Quotes)
                    .Include(s => s.BaseStats)
                    .Include(s => s.Level100Stats)
                    .Include(s => s.Level120Stats)
                    .Include(s => s.Level100RetrofitStats)
                    .Include(s => s.Level120RetrofitStats)
                    .Include(s => s.EnhanceValue)
                    .Include(s => s.ScrapValue)
                    .Include(s => s.Construction)
                    .Include(s => s.Construction.Availability)
                    .Include(s => s.Artist)
                    .Include(s => s.Pixiv)
                    .Include(s => s.Twitter)
                    .Include(s => s.Web)
                    .Include(s => s.VoiceActor)
                        .SingleAsync(ship => ship.Name == name);

The problem (?)

I'm not sure if this is a problem but I don't know why this might be happening but; after I added the ".Include(s => s.Quotes)" my API's response time dropped drastically. A response is on average 25kb so I don't think it has anything to do with my internet/network (the database is hosted on the same network as the web api)

an example response where the Quotes are also loadedtook about 30 seconds If we exclude the "Quotes" property from the object it would load in under 1 second

On average a ship has 30 quotes that look something like this:

{
      "id": "057404ac-ae41-494f-9825-f4fedbd61cd4",
      "skin": "Default Skin",
      "event": "Task",
      "audioUrl": "---------- EXTERNAL LINK REMOVED FOR STACKOVERFLOW POST ----------",
      "eN_Transcription": "We still have missions that haven't been completed. You should check on their progress.",
      "jP_Transcription": "ミッションがまだ終わっていないぞ。進捗を確認したほうがいい",
      "cN_Transcription": "还有未完成的任务哦，还是确认下进度比较好"
}

Here is the ship model (as seen on swagger)

Ship Model

{
  "shipId": "string",
  "name": "string",
  "rarity": "string",
  "stars": {
    "stars": "string",
    "count": 0
  },
  "nation": "string",
  "type": "string",
  "thumbnailImage": "string",
  "skins": [
    {
      "name": "string",
      "imageUrl": "string",
      "backgroundUrl": "string",
      "chibiUrl": "string",
      "live2dModel": true,
      "obtainedFrom": "string"
    }
  ],
  "skills": [
    {
      "iconUrl": "string",
      "name": "string",
      "description": "string",
      "color": "string"
    }
  ],
  "limitBreaks": [
    {
      "limitBreaks": [
        "string"
      ]
    }
  ],
  "gallery": [
    {
      "description": "string",
      "url": "string"
    }
  ],
  "equippableSlots": [
    {
      "maxEfficiency": 0,
      "minEfficiency": 0,
      "type": "string",
      "max": 0
    }
  ],
  "quotes": [
    {
      "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
      "skin": "string",
      "event": "string",
      "audioUrl": "string",
      "eN_Transcription": "string",
      "jP_Transcription": "string",
      "cN_Transcription": "string"
    }
  ],
  "baseStats": {
    "luck": 0,
    "armor": "string",
    "speed": 0,
    "health": 0,
    "firepower": 0,
    "antiAir": 0,
    "torpedo": 0,
    "evasion": 0,
    "aviation": 0,
    "oilConsumption": 0,
    "reload": 0,
    "antiSubmarine": 0,
    "oxygen": 0,
    "ammunition": 0,
    "accuracy": 0,
    "huntingRange": "string"
  },
  "level100Stats": {
    "luck": 0,
    "armor": "string",
    "speed": 0,
    "health": 0,
    "firepower": 0,
    "antiAir": 0,
    "torpedo": 0,
    "evasion": 0,
    "aviation": 0,
    "oilConsumption": 0,
    "reload": 0,
    "antiSubmarine": 0,
    "oxygen": 0,
    "ammunition": 0,
    "accuracy": 0,
    "huntingRange": "string"
  },
  "level100RetrofitStats": {
    "luck": 0,
    "armor": "string",
    "speed": 0,
    "health": 0,
    "firepower": 0,
    "antiAir": 0,
    "torpedo": 0,
    "evasion": 0,
    "aviation": 0,
    "oilConsumption": 0,
    "reload": 0,
    "antiSubmarine": 0,
    "oxygen": 0,
    "ammunition": 0,
    "accuracy": 0,
    "huntingRange": "string"
  },
  "level120Stats": {
    "luck": 0,
    "armor": "string",
    "speed": 0,
    "health": 0,
    "firepower": 0,
    "antiAir": 0,
    "torpedo": 0,
    "evasion": 0,
    "aviation": 0,
    "oilConsumption": 0,
    "reload": 0,
    "antiSubmarine": 0,
    "oxygen": 0,
    "ammunition": 0,
    "accuracy": 0,
    "huntingRange": "string"
  },
  "level120RetrofitStats": {
    "luck": 0,
    "armor": "string",
    "speed": 0,
    "health": 0,
    "firepower": 0,
    "antiAir": 0,
    "torpedo": 0,
    "evasion": 0,
    "aviation": 0,
    "oilConsumption": 0,
    "reload": 0,
    "antiSubmarine": 0,
    "oxygen": 0,
    "ammunition": 0,
    "accuracy": 0,
    "huntingRange": "string"
  },
  "enhanceValue": {
    "firepower": 0,
    "torpedo": 0,
    "aviation": 0,
    "reload": 0
  },
  "scrapValue": {
    "coins": 0,
    "oil": 0,
    "medals": 0
  },
  "construction": {
    "constructionTime": "string",
    "availability": {
      "light": "string",
      "heavy": "string",
      "special": "string",
      "limited": "string",
      "exchange": "string"
    }
  },
  "artist": {
    "name": "string",
    "url": "string"
  },
  "pixiv": {
    "name": "string",
    "url": "string"
  },
  "twitter": {
    "name": "string",
    "url": "string"
  },
  "web": {
    "name": "string",
    "url": "string"
  },
  "voiceActor": {
    "name": "string",
    "url": "string"
  }
}

My Question

What could be the cause of this drastic change? The contents of "Quotes" aren't that big but the database is showing an extreme amount of traffic

Below you can see a screenshot of my database traffic where I requested the object that I included in this post.

relations?

Thank you ❤

[edit -> Last bit of post was unfinished]

Include eagerly loads related entities by generating a LEFT JOIN. This leads to row duplication though. For 30 quotes, the rest of the ship data will be repeated 30 times. This is always a problem with large or complex entities but even experienced developers forget about it because such complex entities aren't that common in simple applications. For moderately complex entities, it's possible to map database entities to API DTOs in the Select clause.

Unless you have an ERP, CRM or BOM problem, where all the important entities and queries are at least that complex, where you do want to load 10s of related entities each with dozens of instances for a single master entity.

EF Core 5 added split queries to handle this case. In the documentation example, the following query:

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
        .ToList();
}

Generates

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [Blogs] AS [b]
LEFT JOIN [Post] AS [p] ON [b].[BlogId] = [p].[BlogId]
ORDER BY [b].[BlogId], [p].[PostId]

Adding AsSplitQuery() splits this to separate queries :

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
        .AsSplitQuery()
        .ToList();
}

Generates

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]
ORDER BY [b].[BlogId]

SELECT [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title], [b].[BlogId]
FROM [Blogs] AS [b]
INNER JOIN [Post] AS [p] ON [b].[BlogId] = [p].[BlogId]
ORDER BY [b].[BlogId]