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


source to share





All Articles