Stored procedure is slow in Entity Framework

Question

I am calling a stored procedure (which returns data) from the Entity Framework using the query below

from sp in db.GetSale(bID, SID,m,y).Where(x => x.isActive == true) select sp 

If I run this procedure directly, it takes only 2 seconds, but when I call this stored procedure through EF, it takes 10 to 15 seconds.

Any recommendations for improving the situation? I saw another post here on SO, but did not succeed

Edit

Here is sqlplan for my procedure https://skydrive.live.com/redir?resid=87DCBD5D3E9AAA57!374

Answer 1

I suspect the sniffing parameter.

Try using the parameter verification code in your procedure. (Assign parameters to local variables before using them).

http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

Answer 2

remember that your .Where(x => x.isActive == true) filter .Where(x => x.isActive == true) does not apply to the procedure itself. When you are dealing with tables, the filter is passed to the where clause and executed in the database. In your case, proc will be executed (without an active sentence), the full set of results will be returned to the applications, and after that where will be applied.

I know that you said that proc is faster, but are you sure that you are somehow not passing isActive = true as a parameter?

I see that you already have parameters for this proc, so it’s possible if you add the status as another parameter and apply it at the request level, this will improve your performance.