Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 скорость выполнения процедуры  [new]
ZverYuga
Member

Откуда:
Сообщений: 5
господа, буду благодарен за любую подсказку. не могу разобраться почему скорость выполнения процедуры разная в 2-х вариантах.

сервер 2008 R2
база работает в режиме изоляции read_commited_snapshot


есть процедура. в ней используется пользовательская хранимая функция CSM_WORKSPACE$GetOverlapCount(код ее ниже). при таком раскладе выполнение процедуры подвисает на этапе вычисления функции на пару часов. но если вынуть код функции и прописать ее вычисление непосредственно в коде процедуры, то на всё тратиться максимум 3 минуты.

код процедуры с вызовом функции:

 IF EXISTS (SELECT 1 FROM @pProjectMRIDList)
    BEGIN
        IF Upper(dbo.CSM_PARAMETER$GetParameter('report_new_overlap_unapplied')) = 'YES'
        BEGIN -- Get count of overlapping and unapplied change items before and after
            
            SET @lOverlapBefore = dbo.CSM_WORKSPACE$GetOverlapCount (@pWorkspaceId);

            SELECT @lUnappliedBefore = COUNT(*)
                FROM csmUnappliedChangeItem
                WHERE WorkspaceId = @pWorkspaceId;
            -- print 'unapplied before = ' + STR(@lUnappliedBefore) +
            -- ' overlap before = ' + STR(@lOverlapBefore);
        END;


код скалярной функции CSM_WORKSPACE$GetOverlapCount

ALTER FUNCTION [dbo].[CSM_WORKSPACE$GetOverlapCount] (@pWorkspaceId Varchar(100))
    RETURNS Integer AS
BEGIN
    DECLARE @Overlap AS TABLE (TableId Varchar(100),
                               ColumnId Varchar(30),
                               RowMRID Varchar(64));

    INSERT INTO @Overlap (TableId, ColumnId, RowMRID)
        SELECT TableId, ColumnId, RowMRID
            FROM dbo.CSM_PROJECT$GetLoadedColumnsDuplicate (@pWorkspaceId);

    DECLARE @count Integer; 
    SELECT @count = COUNT (*)
        FROM csmWorkspace w
        JOIN csmSchemaVerTableVer svtv ON w.SchemaId = svtv.SchemaId
                                      AND w.SchemaVersion = svtv.SchemaVersion
        JOIN csmChangeItem ci ON ci.TableId = svtv.TableId
        JOIN csmLoadedProject lp ON lp.ProjectMRID = ci.ProjectMRID
        JOIN @Overlap overlap ON ci.TableId = overlap.TableId
                             AND IsNull (ci.ColumnId, 'x') =
                                 IsNull (overlap.ColumnId, 'x')
                             AND ci.RowMRID = overlap.RowMRID
        LEFT JOIN csmColumn c ON c.TableId = ci.TableId
                             AND c.TableVersion = svtv.TableVersion
                             AND c.ColumnId = ci.ColumnId
        WHERE lp.WorkspaceId = @pWorkspaceId AND w.id = @pWorkspaceId;

    RETURN @count;
END 


код табличной функции CSM_PROJECT$GetLoadedColumnsDuplicate, которая используется в функции CSM_WORKSPACE$GetOverlapCount

ALTER FUNCTION [dbo].[CSM_PROJECT$GetLoadedColumns] (@pWorkspaceId Varchar(100))
    RETURNS TABLE AS
    RETURN (SELECT ci2.TableId, ci2.ColumnId as OriginalColumnId, ci2.[Action],
                   IsNull(ci2.ColumnId, ci2.[Action]) as ColumnId, ci2.RowMRID,
                   p.MRID, p.ID as ProjectId, ci2.ChangeItemSeq
                FROM csmChangeItem ci2
                JOIN csmLoadedProject lp ON lp.WorkspaceId = @pWorkspaceId
                                        AND lp.ProjectMRID = ci2.ProjectMRID
                JOIN csmProject p ON p.MRID = lp.ProjectMRID);
24 янв 12, 13:57    [11960074]     Ответить | Цитировать Сообщить модератору
 Re: скорость выполнения процедуры  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
последняя функция не та. [dbo].[CSM_PROJECT$GetLoadedColumns] вместо CSM_PROJECT$GetLoadedColumnsDuplicate
24 янв 12, 14:08    [11960200]     Ответить | Цитировать Сообщить модератору
 Re: скорость выполнения процедуры  [new]
ZverYuga
Member

Откуда:
Сообщений: 5
сорри.
не тот код скопировал. вот последняя функция

ALTER FUNCTION [dbo].[CSM_PROJECT$GetLoadedColumnsDuplicate] (@pWorkspaceId Varchar(100))
    RETURNS TABLE AS
    RETURN (SELECT ci2.TableId, IsNull(ci2.ColumnId, ci2.[Action]) as ColumnId, ci2.RowMRID
                FROM csmChangeItem ci2
                JOIN csmLoadedProject lp ON lp.WorkspaceId = @pWorkspaceId
                                        AND lp.ProjectMRID = ci2.ProjectMRID
                GROUP BY ci2.TableId, ColumnId, ci2.RowMRID, ci2.[Action]
                    HAVING COUNT(*) > 1);
24 янв 12, 14:25    [11960365]     Ответить | Цитировать Сообщить модератору
 Re: скорость выполнения процедуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
ZverYuga
есть процедура. в ней используется пользовательская хранимая функция CSM_WORKSPACE$GetOverlapCount(код ее ниже). при таком раскладе выполнение процедуры подвисает на этапе вычисления функции на пару часов. но если вынуть код функции и прописать ее вычисление непосредственно в коде процедуры, то на всё тратиться максимум 3 минуты.
Странно.

Смотрите планы в профайлере.
24 янв 12, 14:26    [11960370]     Ответить | Цитировать Сообщить модератору
 Re: скорость выполнения процедуры  [new]
aleks2
Guest
Ну... функции то дурацкие - с группировкой. И писать надо правильно...

ALTER FUNCTION [dbo].[CSM_WORKSPACE$GetOverlapCount] (@pWorkspaceId Varchar(100))
    RETURNS Integer AS
BEGIN
    DECLARE @Overlap AS TABLE (TableId Varchar(100) not null,
                               ColumnId Varchar(30)  not null,
                               RowMRID Varchar(64)  not null,
                               primary key clustered(TableId), ColumnId, RowMRID)
                               );

    INSERT INTO @Overlap (TableId, ColumnId, RowMRID)
        SELECT TableId, IsNull (ColumnId, 'x'), RowMRID
            FROM dbo.CSM_PROJECT$GetLoadedColumnsDuplicate (@pWorkspaceId);

    RETURN (SELECT COUNT (*)
        FROM (select * FROM csmWorkspace id = @pWorkspaceId) w
        JOIN csmSchemaVerTableVer svtv ON w.SchemaId = svtv.SchemaId
                                      AND w.SchemaVersion = svtv.SchemaVersion
        JOIN csmChangeItem ci ON ci.TableId = svtv.TableId
        JOIN (select * FROM csmLoadedProject WHERE WorkspaceId = @pWorkspaceId) lp ON lp.ProjectMRID = ci.ProjectMRID
        JOIN @Overlap overlap ON ci.TableId = overlap.TableId
                             AND IsNull (ci.ColumnId, 'x')=overlap.ColumnId
                             AND ci.RowMRID = overlap.RowMRID
        LEFT JOIN csmColumn c ON c.TableId = ci.TableId
                             AND c.TableVersion = svtv.TableVersion
                             AND c.ColumnId = ci.ColumnId
     )

END 
24 янв 12, 14:32    [11960448]     Ответить | Цитировать Сообщить модератору
 Re: скорость выполнения процедуры  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
Тут скорей вопросы к оптимизатору SQL Server'а
Пробовал вторую табличную функции выкинуть,в место нее использовать ее запрос(ну там на CTE махнуть)?
24 янв 12, 14:33    [11960462]     Ответить | Цитировать Сообщить модератору
 Re: скорость выполнения процедуры  [new]
ZverYuga
Member

Откуда:
Сообщений: 5
пробовал вместо скалярной функции прописать её код в теле процедуры. всё работает отлично с табличной функцией.
в профайлере видно, что именно на вычислении скалярной функции затык. но вот почему понять не могу
буду смотреть план
24 янв 12, 14:46    [11960640]     Ответить | Цитировать Сообщить модератору
 Re: скорость выполнения процедуры  [new]
ZverYuga
Member

Откуда:
Сообщений: 5
Решилось =)
помогло явное указание в джоинах подсказки оптимизатору hash join.
нашёл на заграничных форумах описание похожей проблемы. оптимизатор иногда тупит.
27 янв 12, 14:35    [11983551]     Ответить | Цитировать Сообщить модератору
 Re: скорость выполнения процедуры  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
ZverYuga
Решилось =)
помогло явное указание в джоинах подсказки оптимизатору hash join.
нашёл на заграничных форумах описание похожей проблемы. оптимизатор иногда тупит.


Если есть возможность перепиши процедуру, это будет правильней хинта.
В большинстве случаев, когда оптимизатор "тупит" со статистикой на сервере не все гладко.
27 янв 12, 14:44    [11983664]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить