c# - C# 存储过程调用、参数嗅探/优化问题的巨大减速？

Question

我有以下重复运行存储过程的代码。当我按字面意思运行 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 作为输出参数。我 can't use the DataContext to execute commands with output parameters ，所以我不得不将 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。但在那种情况下，我不断收到未定义输出参数 @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 会通过分析查询来完成此操作，但我可能是错误的。 此外，我还发现了新版本 SQL Server 中的OPTIMIZE FOR UNKNOWN 提示，当执行计划针对大量不同大小的数据时，它可以减轻参数嗅探的影响。

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);
}

Answer 1

启动 SQLServerProfiler，这可以让您区分单个查询和您现在运行它的方式。

http://www.techrepublic.com/article/step-by-step-an-introduction-to-sql-server-profiler/5054787

但更重要的是，您应该查看查询执行计划，您可以通过查询磁贴在 SSMS 中打开它并选择显示执行计划。

http://www.mssqltips.com/sqlservertip/1856/sql-server-query-execution-plans-in-sql-server-management-studio/

如果您真的是 SSMS 的新手，我可能会在我提供的内容之上阅读几篇文章，但查询执行计划会真正向您显示查询滞后的地方。 (基本的经验法则是您不希望进行全表扫描，您希望它进行搜索，这意味着您希望它在索引和/或主键上进行搜索)我不是 dba，但这就是调试查询时可能希望采用的路由。

虽然看起来非常简单，但在查看后我不太确定这是您的查询。不过，这可能与您调用它的次数有关。您可能想找出一种方法将所有工作人员数据传递到查询中，这样您只需运行一次查询本身而不是运行它 workers.count 次......HTH