C# 存储过程调用、参数探查/优化问题大幅减慢

我有以下代码重复运行存储过程。当我从字面上运行 SQL 语句时，它运行得很好，所以我创建了一个存储过程来封装我正在做的事情。

foreach (string worker in workers)
{
    _gzClasses.ExecuteCommand("EXEC dbo.Session_Aggregate @workerId = {0}, @timeThresh = {1}", worker, SecondThreshold);
    Console.WriteLine("Inserted sessions for {0}", worker);
}

想知道每个调用生成了多少行，因此我稍微更改了 SP，以返回 @@rowcount 作为输出参数。我无法使用 DataContext 执行带有输出参数的命令，因此我不得不将 for 循环中的上述代码更改为以下内容：

using (var cn = new SqlConnection(CnStr))
{
    cn.Open();
    using (var cmd = new SqlCommand("Session_Aggregate", 
        cn) {CommandTimeout = 300})
    {                        
        cmd.CommandType = CommandType.StoredProcedure;                        
        cmd.Parameters.AddWithValue("@workerId", worker);                        
        cmd.Parameters.AddWithValue("@timeThresh", SecondThreshold);                        
        SqlParameter sessions = cmd.Parameters.Add("@sessions", SqlDbType.Int);
        sessions.Direction = ParameterDirection.Output;
        cmd.ExecuteNonQuery();
        Console.WriteLine("Inserted {1} sessions for {0}", worker, sessions.Value);
    }
}

这有效，但它的运行速度比其他查询慢得多。我认为这可能是参数嗅探的情况，所以我将其更改为CommandType.Text并使用字符串EXEC Session_Aggregate ... WITH RECOMPILE。但在这种情况下，我不断收到未定义 out 参数@session的错误。无论如何，查询现在几乎不运行，即使 SQL 命令在 SSMS 中运行<1 秒也是如此。

这是存储过程，以防有人可以帮助弄清楚发生了什么，或者可以找到加快速度的方法。我还会就如何正确描述这里发生的事情提供指导。有了CommandType.StoredProcedure我什至看不到VS发送到SQL的实际命令。

PROCEDURE [dbo].[Session_Aggregate] 
    -- Add the parameters for the stored procedure here
    @workerId varchar(64) = 0, 
    @timeThresh dateTime = '13 July 2007 11:27:46'
    @sessions INT OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    INSERT INTO e_activeSessions
    SELECT *
    FROM (
        SELECT workerId, startTime, COUNT(*) as totalTasks, MAX(timeInSession) as totalTime, 
        MIN(dwellTime) as minDwell, MAX(dwellTime) as maxDwell, AVG(dwellTime) as avgDwell, STDEV(dwellTime) as stdevDwell, 
        SUM(CAST(wrong80 as INT)) + SUM(CAST(correct80 as INT)) as total80, SUM(CAST(correct80 as INT)) as correct80, 
        SUM(CAST(correct80 as FLOAT)) / NULLIF(SUM(CAST(wrong80 as INT)) + SUM(CAST(correct80 as INT)), 0 ) as percent80 
        FROM (
            SELECT *, (SELECT MAX(timeStamp)
                FROM workerLog w where dwellTime is null AND timeInSession = 0 AND workerId = @workerId AND w.timeStamp <= workerLog.timeStamp
                    AND w.timeStamp >= @timeThresh) as startTime
            FROM workerLog where workerId = @workerId) t 
    GROUP BY startTime, workerId) f 
    WHERE startTime is NOT NULL AND f.totalTasks > 1 AND totalTime > 0;
    SET @sessions = @@ROWCOUNT;
END

编辑：无论原始查询的执行计划如何，通过创建临时表都大大加快了速度。我以为SQL会通过分析查询来做到这一点，但我可能错了。此外，我还发现了OPTIMIZE FOR UNKNOWN提示，该提示在新版本的SQL Server中减轻了当执行计划针对大量不同大小的数据时参数嗅探的影响。

PROCEDURE [dbo].[Session_Aggregate] 
    -- Add the parameters for the stored procedure here
    @workerId varchar(64) = 0, 
    @timeThresh dateTime = '13 July 2007 11:27:46',
    @sessions INT OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    CREATE TABLE #startTimes
    (
        startTime DATETIME
    );
    CREATE INDEX Idx_startTime ON #startTimes(startTime);
    INSERT INTO #startTimes
    SELECT timeStamp FROM workerLog 
    WHERE dwellTime is null AND timeInSession = 0 
    AND workerId = @workerId AND timeStamp >= @timeThresh;
    INSERT INTO e_activeSessions
    SELECT *
    FROM (
        SELECT workerId, startTime, COUNT(*) as totalTasks, MAX(timeInSession) as totalTime, 
        MIN(dwellTime) as minDwell, MAX(dwellTime) as maxDwell, AVG(dwellTime) as avgDwell, STDEV(dwellTime) as stdevDwell, 
        SUM(CAST(wrong80 as INT)) + SUM(CAST(correct80 as INT)) as total80, SUM(CAST(correct80 as INT)) as correct80, 
        SUM(CAST(correct80 as FLOAT)) / NULLIF(SUM(CAST(wrong80 as INT)) + SUM(CAST(correct80 as INT)), 0 ) as percent80 
        FROM (
            SELECT *, (SELECT MAX(startTime) FROM #startTimes where startTime <= workerLog.timeStamp) as startTime
            FROM workerLog where workerId = @workerId) t 
    GROUP BY startTime, workerId) f 
    WHERE startTime is NOT NULL AND f.totalTasks > 1 AND totalTime > 0
    OPTION (OPTIMIZE FOR UNKNOWN);
    SET @sessions = @@ROWCOUNT;     
END;

其他简化：将 SP 拖到 DBML 文件中，您可以执行以下操作：

foreach (string worker in workers)
{
    int? rows = 0;
    _gzClasses.Session_Aggregate(worker, SecondThreshold, ref rows);
    Console.WriteLine("Inserted {1} sessions for {0}", worker, rows);
}