Controller.Json function slow for IQueryable

Question

I have an mvc controller action which return json and it take around 2 secs and I expect this to be faster, under 1 secs. I was profilling the contoller action and found out that line with the return json is slow, until there It is executed less that 100ms. Is it because of I am reaching that line with the actual sql to linq query with IQueryable interface and only there DB query is executed and it does something like "toList()". I would like to know what is actually happening there? or is the Controller.Json function is in general slow and I can use something better?

 public ActionResult GetItems()
{
    IQueryable<Item> Items = default(IQueryable<Item>);
      Items = myComponent.getItems(); //returns IQueryable
     var result = Items.OrderByDescending(m => m.category).ThenBy(m => m.order);
      return Json(result, JsonRequestBehavior.AllowGet);
  }

If `getItems()` returns an `IQueryable`, it probably still hasn't hit the db. What is that method doing? — Mister Epic, Dec 24 '15 at 14:00
I updated my question. It is a MVC controller returns a json data. — Emil, Dec 24 '15 at 14:04
Show the code for `getItems()`, if it is slow the problem lies there. — Mister Epic, Dec 24 '15 at 15:25
You mean that it will be excuted later even though exact line is skipped fast while I am returning only Iquerable. — Emil, Dec 24 '15 at 15:43

score 4 · Accepted Answer · edited May 23 '17 at 10:28

There is possibility of two place where you can tune:

IQuerable it self
Controller.Json's Serialization method

For the IQuerable query, you could try sql profiler or try call ToList() before return it to see how long it take and possibly optimise it (without more knowledge of your query, I can not help you).

But for the second part, you can try serialize the result using library like Json.Net

As from the source code, you can see that Mvc use JavaScriptSerializer to do the json serialization, when you call the Json(). (you can also see what actually happen by looking at the source code)

From json.net website's comparison chart, the performace for JavaScriptSerializer is quite bad.

So you could try implement your own JsonActionResult using Json.Net, some sample code below:

Source from : Using JSON.NET to return ActionResult

In your Controller (or base Controller)

protected override JsonResult Json(object data, string contentType, System.Text.Encoding contentEncoding, JsonRequestBehavior behavior)
{
    return new JsonNetResult
    {
        Data = data,
        ContentType = contentType,
        ContentEncoding = contentEncoding,
        JsonRequestBehavior = behavior
    };
}

And the definition for JsonNetResult:

public class JsonNetResult : JsonResult
{
    public JsonNetResult()
    {
        Settings = new JsonSerializerSettings
        {
            ReferenceLoopHandling = ReferenceLoopHandling.Ignore,
        };
    }

    public JsonSerializerSettings Settings { get; private set; }

    public override void ExecuteResult(ControllerContext context)
    {
        if (context == null)
            throw new ArgumentNullException("context");
        if (this.JsonRequestBehavior == JsonRequestBehavior.DenyGet && string.Equals(context.HttpContext.Request.HttpMethod, "GET", StringComparison.OrdinalIgnoreCase))
            throw new InvalidOperationException("JSON GET is not allowed");

        HttpResponseBase response = context.HttpContext.Response;
        response.ContentType = string.IsNullOrEmpty(this.ContentType) ? "application/json" : this.ContentType;

        if (this.ContentEncoding != null)
            response.ContentEncoding = this.ContentEncoding;
        if (this.Data == null)
            return;

        var scriptSerializer = JsonSerializer.Create(this.Settings);

        using (var sw = new StringWriter())
        {
            scriptSerializer.Serialize(sw, this.Data);
            response.Write(sw.ToString());
        }
    }
}

Controller.Json function slow for IQueryable

1 Answers1