MVC: Timeout issue

Question

I'm trying to extract the results of a SQL server view in MVC. The view itself is relatively straightforward and UNIONs a couple of tables together - when run it takes around 2 seconds to return its rows. I've added the view to my model in MVC.

In my controller I have the following code, which is designed to return in JSON format the values from the SQL view:-

public JsonResult GetActivity(string LocalIdentifier) 
{
    return Json(db.Activities.Where(r => r.LocalIdentifier== LocalIdentifier).ToList(), JsonRequestBehavior.AllowGet);
}

When I try to run this, supplying a valid LocalIdentifier nothing happens for a while and then I get an exception (unhandled in user code) in Visual Studio. For reference, this would generally only return between 30 and 50 rows of data from SQL.

Looking at the Inner Exception I get this error:-

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I've done something similar elsewhere in my application and it works fine - I can't see anything wrong with my code in MVC. Is there anything else I could look at to help diagnose and fix this issue?

Update

Interestingly I've just changed the code to extract just one row (just to see what happens - see below) and it runs instantly...could this be a problem with ToList()?? Is there another way of achieving what I'm trying to do that I could try?

public JsonResult GetActivity(string LocalIdentifier) 
{
    return Json(db.Activities.First(r => r.LocalIdentifier== LocalIdentifier), JsonRequestBehavior.AllowGet);
}

Yes...db is up and running and working with the rest of my application. — Mat Richardson, Mar 07 '16 at 08:52
since it returns a json format, do you set an server authorization? It might be the caller of the function is not authorize to call that function — Anonymous Duck, Mar 07 '16 at 08:56
not related maybe....but are you using azure-sql server paas?? we had a scenario where there were issues with paas and union operation... — SamGhatak, Mar 07 '16 at 09:04
Fire up SSMS and open the Activity Monitor, check the activity and whether there are locks while the query hangs. — Lucero, Mar 07 '16 at 09:15
And one thing, where did the exception occured? try to set breakpoints if necessary — Anonymous Duck, Mar 07 '16 at 09:16
The exception occurs in the one line in my code above. I've been playing with this (see update above)...confused now. — Mat Richardson, Mar 07 '16 at 09:29
FYI - its best practice to use ViewModels here when returning data to the View or JSON calls and to not send back the EF Entities directly. you will have issues down the line as well as sending back more data than what is required and it tightly couples your solution. — Ahmed ilyas, Mar 07 '16 at 09:30
I think you can convert your output first to IEnumerable type before calling ToList api take a look what i done here : https://jsfiddle.net/0m828xps/1/ this is c# code i just paste it in fiddler — Anonymous Duck, Mar 07 '16 at 09:41
What are you using for the `db` layer? Check the actual SQL that it's generating, not "run the query on the DB server" as that's unlilely to be exactly the same SQL If you're using nHibernate, use log4net and configure SQL logging. If it's Entity Framework, read this: http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework — freedomn-m, Mar 08 '16 at 11:02

score 0 · Answer 1 · answered Mar 07 '16 at 08:56

0

you should try running the resulting query directly on the sql server. I expect that the query runs for more then 30s, resulting in a timeout. The default timeout value is 30s, so the behaviour you're seeing is expected.

2 ways to deal with it:

speed up the query (maybe you're missing an index?)
change the default timeout (but this is not the preferred option in my opinion)

answered Mar 07 '16 at 08:56

Wim Reymen

190
2
4

possible if the app and DB resides on different server i think :) cause if he query in sql side it only takes 2s to finish – Anonymous Duck Mar 07 '16 at 08:58
The query literally takes 2 seconds to run as mentioned in my original question...I have previously increased the timeout just to see what would happen but with no change (apart from a longer wait until I get the error) – Mat Richardson Mar 07 '16 at 08:58
@MatRichardson is your `LocalIdentifier` nullable in DB side? I have read that comparing not nullable scalar with a nullable takes time to finish – Anonymous Duck Mar 07 '16 at 09:01
No, the value isn't nullable. – Mat Richardson Mar 07 '16 at 09:07

score 0 · Answer 2 · edited May 23 '17 at 12:31

It depends on how db.Activities is defined. If it has properties for other entities (meaning other tables in the database) then these could also be loaded by EF, even though you did not specify so explicitly, because JSON is serializing each object and its properties. Half your database or more could end up being loaded, even from a simple statement like this.

This SO question has several answers that may help you understand better what is going on behind the scenes, and then fix it.

score 0 · Answer 3 · answered Mar 07 '16 at 09:42

Enable profiling and see the query inside
Launch this query in SSMS and build the query execution plan
Analyse the plan and make the updates to the database - change structure, build index, change query

If the query runs fine in DB then it seems something wrong with your ORM side part and you should search for the problem there

MVC: Timeout issue

3 Answers3