I have an app with a handful of heavy-duty queries, in amongst the normal CRUD operations.
I was just fixing an issue that a bunch of code calls were changing the timeout of the context and not resetting it. I want to leave the normal low timeout behind in general, after I've run my, long queries.
For the majority of the large queries, managing the timeout is pretty trivial, as I pull the whole dataset into memory. So I get:
Simples! Squeak.
But in one case the dataset is too large to hold in memory in one go, so I'm actively using the fact that EF offers streaming - iterating lazily through the initial IQueryable, passing it around as an IEnumerable, processing each element in turn, with Linq calls or yield return statements.
Previously this was fine, because I set the long timeout and then never reduced it (and the timeout was huge so it covered both the execution of the query and all of the processing). But now I want to reset it.
If I did that naively, the code execution would look like this:
i.e. the query is still executing after the timeout has been reset.
I can't find any documentation of how CommandTimeouts interact with streamed queries.
Does anyone know how this works?
Would the above meta-code work properly or not?
Is the command timeout fixed per query at the point at which the query is initiated, or done in some other way?
For that matter, I don't really know the implementation details of streamed queries. Does the SQL Server know that the query is to be streamed, or what?
From the time the client submits the query to the time it fetches the last row the query is running on the SQL Server. The results may have been spooled to memory, or TempDb, or the query plan may be actively running as the client fetches the rows.
SqlCommand.CommandTimeout is a client-side timeout that measures (in the case of a query that returns results) how long the client is waiting on a response to SqlCommand.ExecuteReader(), SqlDataReader.Read() and SqlDataReader.NextResult().
Is the command timeout fixed per query at the point at which the query is initiated, or done in some other way?
It's a set on the SqlCommand, and when you change the timeout in EF it should not affect running queries.