CODEWITHSUNDEEP
c#sqlasp.net-mvclinqlinq-to-sql
CR41G14
CR41G14

Reputation: 5594

Linq to SQL executing stored procedure very slow

I have a stored procedure which I call by

Int32 siteID = 1;
DateTime startDate date;
DateTime endDate date;

var result = this.datacontext.GetSummary(siteID, startDate, endDate);

This executes very slow with linq but in SSMS this is very quick.

When I change the SP to only accept a start date and in the SP Create the endDate by:

DECLARE @EndDate date
SET @EndDate = DATEADD(yy,1,@StartDate)

and then execute as below:

var result = this.datacontext.GetSummary(siteID, startDate);

The query is really fast again. I am passing in the exact values as what would be the EndDate.

Has anyone seen anything like this. The query without the end date passed in executes in 0 seconds. With the end date it takes 6 minutes.

Any suggestions would be greatly appreciated.

Upvotes: 0

Views: 989

Answers (1)

konkked
konkked

Reputation: 3231

I would look at what sql is being generated, you can see here how to do that. Once you have gotten the sql see if Linq is doing anything weird when it is translating your query to SQL.

Upvotes: 2

Related Questions