Огромное замедление для вызова хранимой процедуры 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 в качестве выходного параметра. Я не могу использовать 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, Но в этом случае я получаю ошибку, что выходной параметр @session не определено. В любом случае запрос теперь выполняется только в том случае, если команда SQL выполняется в SSMS менее чем за 1 секунду.

Вот хранимая процедура, в случае, если кто-то может помочь выяснить, что происходит, или может найти способ ускорить процесс. Я также взял бы указатели на то, как правильно профилировать то, что здесь происходит. С CommandType.StoredProcedure Я даже не вижу фактическую команду, которая отправляется в SQL VS.

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

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, но это маршрут, который вы, вероятно, хотели бы выбрать при отладке вашего запроса.

Я не уверен, что это ваш запрос после рассмотрения, хотя он выглядит довольно простым. Возможно, это связано с тем, сколько раз вы звоните. Возможно, вы захотите найти способ передать все ваши рабочие данные в запрос, чтобы вы просто запустили сам запрос один раз вместо того, чтобы выполнить его работники. Количество раз......HTH