CODEWITHSUNDEEP
.netjsonasp.net-web-apiserializationjson.net
crackedcornjimmy
crackedcornjimmy

Reputation: 1992

JSON Serialization takes a very long time

I have two ViewModels (Product and Part):

public class ProductViewModel
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }       

    public bool IsActive { get; set; }

    public IEnumerable<PartViewModel> Parts { get; set; }
}

public class PartViewModel
{
    public int Id { get; set; }

    public string Name { get; set; }

    public bool IsActive { get; set; }
}

I run an EF Core query that returns 1727 Products with at least 1 Part per product. An example of a return of 1 of these would serialize to JSON as such:

[  
   {  
      "Id":8761,
      "Name":"Product Name 1",
      "Description":"This is a product",
      "IsActive":true,
      "Parts":[  
         {  
            "Id":103767,
            "Name":"Name 1"
            "IsActive":true
         },
         {  
            "Id":156436,
            "Name":"Name 2",
            "IsActive":true
         },
         {  
            "Id":109436,
            "Name":"Name 3",
            "IsActive":true
         }
      ]
   }
]

Now, this works well with a query where I .Take(10), albeit seemingly slow, but when I try and serialize 1727 records, it bogs down and a five minute wait doesn't even complete the serialization process.

I tried using Json.Net as such:

var ret = JsonConvert.SerializeObject(products, new JsonSerializerSettings { Formatting = Formatting.Indented });

I only decided to try and use JsonConvert from Json.Net because, in my Controller Action, when attempting to return a JsonResult, the following code to convert my object to JSON had the same efficiency issue:

return Json(products);

I get the products via EF Core as such:

var products = _context.Products.OrderBy(o => o.Name).Where(w => w.IsActive  //all products are active
            && (w.Parts.Count(c => c.IsActive) > 0)) //remove parts that are
            .Select(pr => new ProductViewModel
            {
                Id = pr.Id,
                Name = pr.Name,
                Description = pr.Description,
                IsActive = pr.IsActive,
                Parts = pr.Parts.OrderBy(o => o.Name).Where(w => w.IsActive)  //all parts are active
                .Select(prt => new PartViewModel
                {
                    Id = prt.Id,
                    Name = prt.Name,
                    IsActive = prt.IsActive,
                })
            }).ToList();

What can I do?

Upvotes: 2

Views: 4320

Answers (1)

jorgonor
jorgonor

Reputation: 1719

Serialization is not the big deal, it's easy to notice now that you have added the LINQ query that the issue is the poor SQL Entity Framework will generate from it.

First of all, you should be using eager loading to join your products table with the parts table. You can do that simply by adding an Include method call.

_context.Products.Include(p => p.Parts)

If this is not done the query is actually doing N + 1 queries. You should use a simple trick to watch the actual SQL queries your query is doing adding this code to your DbContext. (Do this only if you are using EF6, EF Core does query logging for you.)

public YourDBContext()
{
   #if DEBUG   
   this.Database.Log = msg =>
   {
       Debugger.Log(1, "ALL", "EF DB SQL: " + msg + Environment.NewLine);
   };
   #endif
}

Another condition that is doing the query take long is .Where(w => w.IsActive && (w.Parts.Count(c => c.IsActive) > 0)). I guess Entity Framework is generating a HAVING Clause, but it would help if you posted the generated SQL to optimize the query.

Finally, a microoptimization in your Select method, would be to change the Parts property fetch expression by.

// other properties ...
Parts = pr.Parts.Where(w => w.IsActive).OrderBy(o => o.Name),
// other properties ...

This would prevent your database to fetch and sort the inactive parts.

Upvotes: 3

Related Questions