TimeoutException not being thrown Linq to SQL DataContext.CommandTimeout C#

Question

I'm trying to throw a TimeoutException whenever it takes longer than 4 minutes to execute a stored procedure call in Linq to SQL.

Here is the code in my controller:

public IEnumerable<Quality> Get(DateTime param1, DateTime param2, string param3)
        {     

            var dc = new VideoDataContext(WebApplication.MonitorServer);

            dc.CommandTimeout = 240;

            List<Quality> cqs = dc.Vid_GetQualityForVideo(param1, param2, 
            param3).ToList();


            return cqs;
        }

For some reason the code executes for longer (way longer) than 4 minutes but doesn't throw a timeout exception. In the end I get a Memory exception for really big queries.

I can't optimize the query or even see it because I don't have access to the stored procedures. I can only detect a timeout in the application. Does anyone know why I'm not getting a TimeoutException.

In other places in my code I have very similar code and the timeout is thrown after 4 minutes on another server holding a different database. It's just not happening here.

I can write to the Data team to change something in their but I'd like to be specific as to what the problem is.

Thanks!

Edit: To clarify I saw before when I retrieved data it would take forever and noticed that it was the massive amount of data being passed to the client. I have an MVC Action Filter that detects the number of rows and if it's more than a certain threshold (10000 right now) it only passes an error message to the client and not the entire dataset. The queries I'm running return way more than the threshold but it's still taking longer than 4 minutes to complete.

check connection string for context connection, is it set to true or false or not set at all? — sll
– sll, Commented Nov 4, 2013 at 20:32

Adam Kewley · Accepted Answer · 2013-11-04 20:52:42Z

1

If your query is large the .ToList() may cause performance issues. The IEnumerable<T> you get from the query is lazily evaluated. However, calling a .ToList() will iterate through the entire contents of the result, putting it into the resulting List (cqs).

If you truly wish the return of your function to be IEnumerable<Quality> then you may be able to just return the query IEnumerable:

public IEnumerable<Quality> Get(DateTime param1, DateTime param2, string param3)
    {     

        var dc = new VideoDataContext(WebApplication.MonitorServer);

        dc.CommandTimeout = 240;

        return dc.Vid_GetQualityForVideo(param1, param2, param3);
    }

Edit:

To clarify, if you are getting a memory flow, not a timeout flow, I would suspect that evaluating the IEnumerable into cqs is filling memory as quickly as the IEnumerable iterates. I would use LINQ queries (Filter, etc.) to truncate the resulting IEnumerable.

Also (reminded in comments), I'll put my money on the reason you are not getting a timeout is because dc.Vid_GetQualityForVideo(param1, param2, param3) evaluates successfully. The following ToList() called upon it returning is causing the overflow. Doing some basic breakpoints checks on it would confirm this.

edited Nov 4, 2013 at 20:52

answered Nov 4, 2013 at 20:38

Adam Kewley

1,2347 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

LINQ2Vodka Over a year ago

Also, if resultset is too big, then i believe it won't be a TimeOut case while large data retrieving is still in progress...

Adam Kewley Over a year ago

Forgot that part, adding clarification to answer.

Eitan Over a year ago

It's around 500,000 rows and each row has about 20-30 columns so it is pretty massive.

Adam Kewley Over a year ago

You could do some napkin maths on how much work ToList must do and calculate the size of each element multiplied by the expected return value. Might turn out that the number is very large. However, for your current issue I'd just recommend the breakpoint testing.

Eitan Over a year ago

Let's say I pass two dates only a couple of days apart, it will take 6.5 minutes from SSMS and 6.5 minutes to return the list to the client. I'm also iterating through the IEnumerable to get the count of the records. Would this eagerly retrieve the data?

|

LINQ2Vodka · Accepted Answer · 2013-11-04 21:25:48Z

0

Probably request worked well and your large data being received via network. In this case no TimeOut exception should occur.

answered Nov 4, 2013 at 21:25

LINQ2Vodka

3,0362 gold badges31 silver badges49 bronze badges

6 Comments

Eitan Over a year ago

Good point! I noticed that and I don't return all the data to the client if it's more than a certain number of rows (10000). Right now the queries return way more than 10000.

LINQ2Vodka Over a year ago

if exactly this was an issue then i'd be glad to see the answer accepted :) otherwise please don't

Eitan Over a year ago

That was the issue before I asked this question but I fixed it and I'm still not getting the timeout from server side

LINQ2Vodka Over a year ago

can you run the query in SSMS and check time it runs before 1st record gets retrieved?

Eitan Over a year ago

It's like 5 seconds before it starts returning rows and like 6.5 minutes for the query to finish entirely

|

Collectives™ on Stack Overflow

TimeoutException not being thrown Linq to SQL DataContext.CommandTimeout C#

2 Answers 2

7 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related