Why does LINQ / SQL connection in C # take 10 times longer than running a stored procedure in sql studio?

I have a stored procedure that takes 4 seconds to execute. When I try to access it via C # LINQ or SQL connection, it takes over 40 seconds.

SQL

EXEC    @return_value = [dbo].[SummarizeRevenue_V10]
    @SalesLocation = 1,
    @District = 0,
    @ReportStartDate = N'1/1/2015',
    @ReportEndDate = N'1/31/2015',
    @SelectionReportType = 1,
    @SummarizeBy = N'Driver',
    @Threshold = 62.50

      

LINQ

DataClasses1DataContext dc = new DataClasses1DataContext();
dc.CommandTimeout = 600;
var foo = dc.SummarizeRevenue_V10(1, 0, DateTime.Parse("1/1/2015"), DateTime.Parse("1/31/2015"), 1, "Driver", 62.50m);

      

SQL connection

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["fluidsConnectionString"].ConnectionString))
        using (var command = new SqlCommand("SummarizeRevenue_V10", conn){CommandType = CommandType.StoredProcedure})
        {
            command.Parameters.Add("@SalesLocation", SqlDbType.Int).Value = 1;
            command.Parameters.Add("@District", SqlDbType.Int).Value = 0;
            command.Parameters.Add("@ReportStartDate", SqlDbType.DateTime).Value = System.DateTime.Parse("1/1/2015");
            command.Parameters.Add("@ReportEndDate", SqlDbType.DateTime).Value = System.DateTime.Parse("1/31/2015");
            command.Parameters.Add("@SelectionReportType", SqlDbType.Int).Value = 1;
            command.Parameters.Add("@SummarizeBy", SqlDbType.VarChar).Value = "Driver";
            command.Parameters.Add("@Threshold", SqlDbType.Money).Value = 62.50m;
            conn.Open();
            command.CommandTimeout = 600;
            command.ExecuteNonQuery();
            conn.Close();
        }

      

We ran this on multiple computers with the same results. Obviously, the LINQ expression came from a larger program. We pulled it out and sent it to where the code you see is the code we ran. We put a breakpoint on the LINQ and execution statements and one after the statement. When the program broke the expression, we continued the program and started counting. We tried to change the LINQ statement to SQL statement (as you can see).

+3
c # sql-server linq stored-procedures


source to share


No one has answered this question yet

See similar questions:

4
LINQ to SQL Runtime 50x Longer than SSMS SQL

or similar:

425
If .NET strings are immutable in .NET, then why does Substitution take O (n) time?
179
LINQ-to-SQL and stored procedures?
82
C # SQL Server - passing a list to a stored procedure
6
Linq over stored procedures
6
Stored Procedures in LINQ-to-SQL
1
C # (MVC3) LINQ SQL Stored Procedure
1
C # LINQ dynamic Query translate into stored procedure
1
Increase the field value in the table by pressing the button
0
Why isn't my stored procedure running in C #?
-2
SQL Server Stored Procedure takes a long time to complete



All Articles
Loading...
X
Show
Funny
Dev
Pics