Stored procedure is slow in Entity Framework

Question

I am calling a stored procedure (that returns data) from 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 is taking 10 to 15 seconds.

Any guidance to improve the situation? I have seen other post here on SO but have no success

Edit

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

Answer 1

I suspect parameter sniffing.

Try applying anti-parameter sniffing 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

bear in mind that your .Where(x => x.isActive == true) filter is not applied to the procedure itself. When you are dealing with tables, the filter is passed to the where clause and executed on the DB. On your case, the proc is going to be executed (without the active clause), the the full result set will be returned to the applications and after that the where will be applied.

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

I see you already have parameters on this proc, so maybe if you add status as another parameter and apply it at query level, it will improve your performance.