CODEWITHSUNDEEP
c#asp.netsql-serverlinqstored-procedures
Michael B
Michael B

Reputation: 129

Linq-to-SQL slow stored procedure when using date parameters

Any help on this one would be greatly appreciated!

I'm using Linq-to-SQL to call a stored procedure in SQL Server. The stored procedure has 3 parameters; int, datetime, datetime.

When I call this from Linq-to-SQL in my ASP.NET MVC application, it is agonisingly slow and can timeout. However when I call the stored procedure in SQL Server Management Studio, it returns the results in a second or so.

I removed the dates as parameters and hardcoded the sames dates into the procedure and called it from my web app and it runs as it should. Put those date params back in and it hangs. Any ideas? Issue with date formats etc?

Not really doing anything fancy here..

C#

public List<Sales> GetSales(int StoreId, DateTime dateFrom, DateTime dateTo)
{
    using (AppDataContext ctx = new AppDataContext())
    {
        var result = ctx.sp_GetSales(id, dateFrom, dateTo).ToList(); 

        foreach (var item in result)
        {
            // handle data returned
        }
    }
}

All dates are type DateTime. I don't think it is the stored procedure, as I said, it works perfectly when I hard code the dates. Perhaps something to do with mapping Linq-to-SQL with the stored procedure? Thanks for all your help!

Upvotes: 1

Views: 684

Answers (1)

Justin
Justin

Reputation: 86729

This is a long shot, but I think I've seen this before with dates. I think the issue I saw was something to do with parameter sniffing, but it never quite added up as SQL Server seemed to manage to generate a query which was orders of magnitude worse than any parameter that had ever been supplied to the query.

It was some time ago, however if I recall correctly doing the following in the stored procedure fixed the issue:

CREATE PROCEDURE Test
    @Param1 DATETIME
AS
BEGIN
    DECLARE @Param1_Copy DATETIME = @Param1

    -- Use @Param1_Copy instead of @Param1
END

For some reason "Copying" the parameter in this way seemed to prevent the issue. Potentially using WITH RECOMPILE or other parameter sniffing solutions might work, I can't remember.

If this doesn't solve your issue then I recommend using SQL Server Profiler to capture a SQL trace of the exact SQL statements executed by your application, and try running that exact statement in SQL Server Management Studio to see if that reproduces the poor performance - even quite subtle differences in the way in which a procedure is executed can cause differences. I also believe SQL Server Management Studio uses different Session Properties (i.e. QUOTED_IDENTIFIER etc...) to .Net. Its possible that one of those settings is the reason why you can't reproduce this in SQL Server Management Studio.

Upvotes: 2

Related Questions