Entity Framework Timeouts on SqlQuery

Question

I am using EF 6.1.3, and populating a model with one of the stored procs times out.

var spResult = context.Stuff.SqlQuery("exec GetStuff @p0, @p2", pId, typeId )
.OrderBy(a => a.Barcode)
.ToDataSourceResult(request);

I have a stored proc called GetStuff with two parameters: @id int and @typeId int = null //nullable

In SQL Profiler I can see the stored proc is being executed by EF on the server, with correct parameters.
When I run the stored proc in SSMS with those same parameters I get results in less than 1 second (copy pasted from Profiler to be sure).

If I change the one parameter when calling from EF, I get immediate results!

In Short: stored procedure GetStuff (1, null) works in EF and SSMS while GetStuff(1, 1) times out in EF, but works in SSMS.

The Stored proc is a Select only, with no transactions in SQL or my C# code defined.

Did you check it is throwing `Timeout Expception` or some thing else is there in `Inner Exception` — Rajeev Kumar, Mar 30 '15 at 11:17
Looks like a parameter sniffing issue. Have you tried creating the stored procedure WITH RECOMPILE? — Steve Ford, Mar 30 '15 at 11:22
@callisto results are different between EF and ssms, unfortunately. My solution has been to increase the CommandTimeout, or try to optimize the used queries. You can look into `SET STATISTICS xxx` for more information regarding debugging queries (`TIME` and `IO` provide a lot of information) — default, Mar 30 '15 at 11:50
@SteveFord Please put yours in an answer so I can mark it as correct. Many thanks for this! — callisto, Mar 30 '15 at 12:06

score 3 · Accepted Answer · answered Mar 31 '15 at 10:54

3

This looks like a parameter sniffing issue see simple talk article

Try creating the stored procedure as

CREATE PROCEDURE GetStuff
  @pid INT,
  @typId INT
WITH RECOMPILE
AS
 ....

answered Mar 31 '15 at 10:54

Steve Ford

7,433
19
40

score 0 · Answer 2 · edited May 23 '17 at 10:30

0

add this code to your context class

{
        Database.SetInitializer<yourDatabaseContextName>(null);
        var adapter = (System.Data.Entity.Infrastructure.IObjectContextAdapter)this;
        var objectContext = adapter.ObjectContext;
        objectContext.CommandTimeout = 120; //2 minutes
    }

click this for more information

edited May 23 '17 at 10:30

Community

1
1

answered Mar 30 '15 at 11:23

for Entity Framework 6 there are [easier solutions](http://stackoverflow.com/a/6234593/238902) – default Mar 30 '15 at 11:36
yes this is the refrence [Click for Refrence](http://stackoverflow.com/questions/6232633/entity-framework-timeouts) – Mar 30 '15 at 11:39
yes, that is the question/answer that I linked to. It is however not the code example that you are displaying. The code you have in your answer is used for EF5, not EF6 (the OP is using EF6). – default Mar 30 '15 at 11:43
@Dexter My timeout is set to 180 seconds, but the same query returns instantly from SSMS. – callisto Mar 30 '15 at 11:47
can you post the Complete error that occurred during query execution ! – Mar 30 '15 at 12:03

Entity Framework Timeouts on SqlQuery

2 Answers2