c# - Stored Procedure Run from SMSS is Lightning Fast, ADO.NET takes 20x slower

Question

Hopefully someone could shine a little bit of light on this for me. I've been trying to use Slow in the Application, Fast in the SMSS to try and troubleshoot my problem for the last day or so with no avail.

I have a stored procedure that's attempting to retrieve values out of a custom built view :

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ARITHABORT ON
GO

CREATE PROCEDURE [dbo].[GetProcesses]
    @SessionId INT,
    @LastUpdate DATETIME2(7)
AS
    DECLARE @MySessionId INT
    SET @MySessionId = @SessionId

    DECLARE @MyLastUpdate DATETIME2(7)
    SET @MyLastUpdate = @LastUpdate
BEGIN

    SELECT * 
    FROM [GetProcessesView]
    WHERE SessionId = @MySessionId 
      AND (StartTime >= @MyLastUpdate OR 
           LastModified >= @MyLastUpdate OR 
           ProcessStepViewLastModified >= @MyLastUpdate)
END
GO

I can run this from SQL Server Management Studio using the same parameters my application provides and this completes in 1-2 seconds. However, when I attempt to run it from my Data Access layer, it takes over 40. The dataset is relatively small (maybe 5 mbs or so).

using (var connection = new SqlConnection(GetConnectionString()))
{
    var dataSet = new DataSet("ProcessUpdates");

    SqlCommand command = new SqlCommand("GetProcesses", connection);
    command.Parameters.AddWithValue("@SessionId", sessionId);
    command.Parameters.AddWithValue("@LastUpdate", lastUpdate);
    command.CommandType = CommandType.StoredProcedure;

    SqlDataAdapter dataAdapter = new SqlDataAdapter()
    {
        SelectCommand = command
    };

    dataAdapter.Fill(dataSet);
}

Here is a list of my view: Queries in this view in SQL Management Studio are incredibly fast.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[GetProcessesView]
as
select P.Id as ProcessViewId, P.[Name], P.StartTime, P.CompletedTime, P.IsFailed, P.UnitId, P.ProcedureId, P.SessionId, P.LastModified, P.AutomationId,
       PS.Id as ProcessStepViewId, PS.[Status], PS.ProcessId, PS.StepNum, PS.Description, PS.LastModified as ProcessStepViewLastModified,
       [U].Id as UnitViewId, [U].Number, [U].[Grouping], [U].EnumValue,
       PR.Id as ProcedureViewId, PR.Title as ProcedureViewTitle, PR.Steps, PR.SystemId as ProcedureViewSystemId, PR.VisionId, PR.[Grouping] as ProcedureViewGrouping,
       A.Id as AutomationViewId, A.Title as AutomationViewTitle, A.UserControl, A.InProgressUserControl, A.SystemId as AutomationViewSystemId,
       SY.Id as SystemViewId, SY.FullName as SystemViewFullName, SY.Acronym as SystemViewAcronym,
       S.Id as SystemTwoViewId, S.FullName as SystemTwoViewFullName, S.Acronym as SystemTwoViewAcronym,
       P.[UID]

from [Process] P
left join [ProcessSteps] PS on P.Id = PS.ProcessId 
left join [Unit] U on P.UnitId = U.Id
left join [Procedure] PR on P.ProcedureId = PR.Id
left join [Automation] A on P.AutomationId = A.Id
left join [PlantSystem] S on  PR.SystemId = S.Id
left join [PlantSystem] SY on  A.SystemId = SY.Id

GO

标签： c#sqlsql-serverado.net