Публикация

 
Заказчик выдвинул требования значительно ускорить быстродействие выполнения определенного функционала.

Для определения "узких" мест в функционале разработал набор методов (функций), которые позволяют измерять суммарное время на выполнение каждой функции и процентное отношение суммарного времени вызова этой функции к суммарному времени выполнения общего функционала. В моей ситуации каждая функция вызывалась по много раз, поэтому нужно было реализовать накопление времени выполнения по каждой функции или участку кода.

Функционал в моем случае: импорт прайс-листа из Excel по производителю с большим количеством строк.

В результате оптимизации, удалось ускорить импорт прайс-листа приблизительно раза в четыре (был 1 час, стало 15 минут).

Вот набор этих методов

(прикрепил ниже в архиве scr_ScriptExecutionAnalysis.zip).

//-----------------------------------------------------------------------------
// scr_ScriptExecutionAnalysis
//-----------------------------------------------------------------------------

var ScriptExecutionAnalysis = {};

function _StartTiming(Caller, IsMain) {
     if (!Caller) {
          return;
     }
     var Name;
     if (typeof(Caller) == 'function') {
          Name = "[fn]" + Caller.toString().split('(')[0].substring(9);
     } else {
          Name = Caller;
     }
     if (!ScriptExecutionAnalysis[Name]) {
          ScriptExecutionAnalysis[Name] = {};
          ScriptExecutionAnalysis[Name].TotalDuration = 0;
          ScriptExecutionAnalysis[Name].NumberOfCalls = 0;
          ScriptExecutionAnalysis[Name].MinDuration = 0;
          ScriptExecutionAnalysis[Name].MaxDuration = 0;
          ScriptExecutionAnalysis[Name].WasZeroDuration = false;
     }
     if (IsMain) {
          ScriptExecutionAnalysis.IsMainName = Name;
     }
     ScriptExecutionAnalysis[Name].Start = (new Date()).getTime();
}

function _EndTiming(Caller) {
     if (!Caller) {
          return;
     }
     var Name;
     if (typeof(Caller) == 'function') {
          Name = "[fn]" + Caller.toString().split('(')[0].substring(9);
     } else {
          Name = Caller;
     }
     if (!ScriptExecutionAnalysis[Name]) {
          return;
     }
     ScriptExecutionAnalysis[Name].End = (new Date()).getTime();
   
     var Duration = ScriptExecutionAnalysis[Name].End - ScriptExecutionAnalysis[Name].Start;
     if (Duration == 0) { ScriptExecutionAnalysis[Name].WasZeroDuration = true }    
         
     if ((ScriptExecutionAnalysis[Name].MinDuration == 0 &&
          !ScriptExecutionAnalysis[Name].WasZeroDuration) ||
          Duration ScriptExecutionAnalysis[Name].MinDuration) {
          ScriptExecutionAnalysis[Name].MinDuration = Duration;
     }
     if (Duration > ScriptExecutionAnalysis[Name].MaxDuration) {
          ScriptExecutionAnalysis[Name].MaxDuration = Duration;
     }
     ScriptExecutionAnalysis[Name].TotalDuration += Duration;
     ScriptExecutionAnalysis[Name].NumberOfCalls++;    
}

function _CreateScriptExecutionAnalysisReport() {
     var Item;
     var Name;
     var Main;
     if (ScriptExecutionAnalysis.IsMainName) {
          Main = ScriptExecutionAnalysis[ScriptExecutionAnalysis.IsMainName];
     }
     var FString = "[%1] TotalDuration - %2 ms, NumberOfCalls - %3, MinDuration - %4 ms, MaxDuration - %5 ms, AverageDuration - %6 ms";
     for (Name in ScriptExecutionAnalysis)   {
          if (Name == 'IsMainName') {
               continue;
          }
          Item = ScriptExecutionAnalysis[Name];
          Log.Write(1, FormatStr(FString,
               Name, Item.TotalDuration, Item.NumberOfCalls, Item.MinDuration, Item.MaxDuration,
               Math.round(Item.TotalDuration / Item.NumberOfCalls * 100) / 100) +
               (Main ? ", Portion - " + Math.round(Item.TotalDuration / Main.TotalDuration * 1000) / 10 + " %": ""));
     }
     ScriptExecutionAnalysis = {};
}

function FormatStr(Str) {
     var Ln = arguments.length;
     for (var i = 1; i Ln; i++ ) {
          Str = Str.replace(new RegExp('%' + i, 'g'), arguments[i]);
     }
     return Str;
}

Описание методов:

_StartTiming(Caller, IsMain) - используется для указания начала отсчета времени.
Аргумент Caller может быть либо объект функции, в которой вызывается этот метод, а может быть и просто текстовая строка (тэг) для унификации этого замера времени. Унификация используется при построении отчета.
Аргумент IsMain - признак того, что метод вызывается из основной функции и нужен для подсчета суммарного времени работы функционала, а суммарное время расчеты функционала в свою очередь используется для определения процентного отношения вызова функции к общему времени выполнении функционала (в отчете).
_EndTiming(Caller) - используется для указания окончания замера времени.
Аргумент Caller указывается по аналогии как в методе _StartTiming.
Каждому вызову метода _StartTiming(Caller) должен соответствовать вызов метода _EndTiming(Caller) с таким же аргументом Caller. Это нужно для построения отчета и вообще для корректного замера времени.
_CreateScriptExecutionAnalysisReport() - метод выводит в лог Террасофт отчет по выполнению функций.

Пример использования в коде:

function MainFunction() {
     _StartTiming(arguments.callee, true);
     ...
     InnerFunction();
     ...
     _EndTiming(arguments.callee);
     _CreateScriptExecutionAnalysisReport();
}

function InnerFunction() {
     _StartTiming(arguments.callee);
     ...
     _StartTiming('ScriptBlock1');
     ...
     _StartTiming('ScriptBlock2');
     ...
     _EndTiming('ScriptBlock2');
     ...
     _StartTiming('ScriptBlock3');
     ...
     _EndTiming('ScriptBlock3');
     ...
     _EndTiming('ScriptBlock1');
     ...
     _EndTiming(arguments.callee);
}

Пример отчета из "реальной жизни"

прикрепил ниже (Report.txt).
Пару строк из отчета:

[[fn]PriceListImport] TotalDuration - 9907 ms, NumberOfCalls - 1, MinDuration - 9907 ms, MaxDuration - 9907 ms, AverageDuration - 9907 ms, Portion - 100 %
[[fn]InitUpdateQuery] TotalDuration - 652 ms, NumberOfCalls - 46, MinDuration - 0 ms, MaxDuration - 31 ms, AverageDuration - 14.17 ms, Portion - 6.6 %
[ProcessOneRecord] TotalDuration - 4219 ms, NumberOfCalls - 49, MinDuration - 31 ms, MaxDuration - 329 ms, AverageDuration - 86.1 ms, Portion - 42.6 %

Расшифровка отчета:

TotalDuration - суммарное время на вызов этой функции/участка кода;
NumberOfCalls - количество вызовов этой функции / прохождения участка кода;
MinDuration - минимальное время выполнения этой функции/участка кода;
MaxDuration - максимальное время выполнения этой функции/участка кода;
AverageDuration - среднее время выполнения этой функции/участка кода;
Portion - процентное отношение суммарного вызова этой функции/участка кода к суммарному времени вызова основной функции.

В отчете [fn] обозначает, что это функция. Отсутствие [fn] обозначает, что это участок кода, который характеризуется указанным тэгом.

PriceListImport - это в моем примере основная функция (видно даже по значению Portion = 100%)
[fn]InitUpdateQuery - это вызов функции InitUpdateQuery, Portion = 6.6 %. Возможно, стоит на эту функцию обратить внимание с таким процентным соотношением.
ProcessOneRecord - участок кода с тэгом 'ProcessOneRecord'. Portion = 42.6 % - большой вес в общем функционале, стоит хорошенько пересмотреть этот участок кода.

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

Поделиться

1 комментарий

Спасибо)) :twisted:

Войдите или зарегистрируйтесь, чтобы комментировать
Вопрос

Нужна помощь советом в какую сторону нужно смотреть.

Реализовал импорт прайс-листа по продуктам указанного производителя. В начале импорта указывается производитель и выбирается Excel-файл прайс-листа этого производителя. В процессе импорта добавляются/обновляются продукты. Считывается строка из Excel-файла, проверяется наличие этого продукта в БД по артикулу и производителю, дальше в зависимости от наличия продукта выполняется либо добавление (если нет), либо обновление (если есть) продукта. Также, во время импорта происходит проверка на дубли по артикулу (коду) продукта, в конце выводиться отчет.

Попробовал дефрагментировать индексы по этому совету. С большим файлом Excel (22411 строк) импорт занял: до - 45 минут, после - 33 минуты.

Задача: максимально ускорить процесс импорта.

Я доработал функционал, чтобы получить статистику сколько сумарно времени занимает выполнение того или иного участка кода/функции, а также какой процент приходиться на этот участок кода/функции от общего времени импорта.

В результате, узкими местами оказались функции GetOffering (10 минут, 29.4 %), CheckHasDuplicate (11.5 минут, 33.6 %), которые выполняют запрос на выборку (Select query) по продукту:
[[fn]GetOffering] TotalDuration - 600573 ms, NumberOfCalls - 22409, MinDuration - 15 ms, MaxDuration - 328 ms, AverageDuration - 26.8 ms, Portion - 29.4 %
[[fn]CheckHasDuplicate] TotalDuration - 684564 ms, NumberOfCalls - 22409, MinDuration - 15 ms, MaxDuration - 328 ms, AverageDuration - 30.55 ms, Portion - 33.6 %

Portion - это процент от общего времени выполнения импорта прайса.

Вот эти запросы:

функции GetOffering

SELECT
        [tbl_Offering].[ID] AS [ID],
        [tbl_ProductCode].[Code] AS [ProductCode]
FROM
        [dbo].[tbl_Offering] AS [tbl_Offering]
LEFT OUTER JOIN
        [dbo].[tbl_ProductCode] AS [tbl_ProductCode] ON [tbl_ProductCode].[ID] = [tbl_Offering].[ProductCodeID]
WHERE([tbl_Offering].[Article] = N'20237-179' AND
        [tbl_Offering].[VendorID] = '{AFF31DDE-20C5-4207-AD54-275962A4D7F8}')

Execution plan
execution plan

функции CheckHasDuplicate

SELECT
        [tbl_Offering].[ID] AS [ID],
        [tbl_Offering].[OriginalName] AS [OriginalName],
        [tbl_Offering].[VendorID] AS [VendorID],
        [Vendor].[Name] AS [VendorName],
        [tbl_ProductCode].[Code] AS [ProductCode],
        [tbl_Offering].[Article] AS [Article]
FROM
        [dbo].[tbl_Offering] AS [tbl_Offering]
LEFT OUTER JOIN
        [dbo].[tbl_Account] AS [Vendor] ON [Vendor].[ID] = [tbl_Offering].[VendorID]
LEFT OUTER JOIN
        [dbo].[tbl_ProductCode] AS [tbl_ProductCode] ON [tbl_ProductCode].[ID] = [tbl_Offering].[ProductCodeID]
WHERE([tbl_Offering].[Article] = N'PCM-3730I-AE')

Execution plan
execution plan

Большие картинки в прикрепленном архиве execution_plan.rar.

Смотрю в сторону индексов. В таблице tbl_Offering 152773 записи. С индексами раньше особо не работал. Возможно, стоит добавить некластерный индекс по полю Article, по которому происходит фильтрация продуктов? Может, еще что-то посоветуете? Нужно ли запускать какую-то регулярную обработку индексов, чтобы их оптимизировать?

Вот SQL-код создания таблицы tbl_Offering:

/****** Object:  Table [dbo].[tbl_Offering]    Script Date: 05/14/2013 15:05:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Offering](
        [ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [PDFOfferingID]  DEFAULT (newid()),
        [CreatedOn] [datetime] NULL,
        [CreatedByID] [uniqueidentifier] NULL,
        [ModifiedOn] [datetime] NULL,
        [ModifiedByID] [uniqueidentifier] NULL,
        [Name] [nvarchar](250) NOT NULL,
        [Code] [nvarchar](250) NULL,
        [CurrencyID] [uniqueidentifier] NULL,
        [BasicPrice] [decimal](15, 2) NULL,
        [DefaultUnitID] [uniqueidentifier] NULL,
        [VendorID] [uniqueidentifier] NULL,
        [SupplierID] [uniqueidentifier] NULL,
        [OwnerID] [uniqueidentifier] NULL,
        [OfferingTypeID] [uniqueidentifier] NULL,
        [URL] [nvarchar](250) NULL,
        [Description] [image] NULL,
        [Weight] [decimal](15, 4) NULL,
        [Volume] [decimal](15, 4) NULL,
        [SerialNumber] [nvarchar](50) NULL,
        [OriginalCountryID] [uniqueidentifier] NULL,
        [UnitDivision] [decimal](15, 4) NULL,
        [IsUsed] [int] NULL,
        [ProductCodeID] [uniqueidentifier] NULL,
        [GuaranteePeriod] [decimal](15, 4) NULL,
        [QuantityInPackage] [decimal](15, 4) NULL,
        [MinRest] [decimal](15, 4) NULL,
        [StatusID] [uniqueidentifier] NULL,
        [OriginalName] [nvarchar](250) NULL,
        [DeliveryTerm] [decimal](15, 4) NULL,
        [Price1FOB] [decimal](15, 4) NULL,
        [Price1DDP] [decimal](15, 4) NULL,
        [VendorCurrencyID] [uniqueidentifier] NULL,
        [SupplierCurrencyID] [uniqueidentifier] NULL,
        [VendorSupplierConvRateID] [nvarchar](250) NULL,
        [DeliveryBasisID] [nvarchar](250) NULL,
        [SupplierMarkup] [decimal](15, 4) NULL,
        [SupplierExtraCostsForUkraine] [decimal](15, 4) NULL,
        [EndUserPriceFOB] [decimal](15, 4) NULL,
        [EndUserPriceDDP] [decimal](15, 4) NULL,
        [IsSpecialSupplierPrice] [int] NULL,
        [Article] [nvarchar](250) NULL,
        [SupplierConversionRateID] [nvarchar](250) NULL,
        [IsFixedCosts] [int] NULL,
        [FixedCosts] [decimal](15, 4) NULL,
        [SpecialSupplierPrice] [decimal](15, 4) NULL,
        [IsRequiredIncomeControl] [int] NULL,
        [OrderCode] [nvarchar](250) NULL,
        [Note] [nvarchar](1000) NULL,
        [IsUsedInPriceListImport] [int] NULL,
        [CategoryID] [uniqueidentifier] NULL,
        [GnomeID] [nvarchar](250) NULL,
        [IsSeparateLineMarkupInSupplie] [int] NULL,
 CONSTRAINT [POfferingID] PRIMARY KEY NONCLUSTERED
(
        [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FK13238CategoryIDtbl_Offeri1] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[tbl_OfferingCategory] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FK13238CategoryIDtbl_Offeri1]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingCurrencyID] FOREIGN KEY([CurrencyID])
REFERENCES [dbo].[tbl_Currency] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingCurrencyID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingDefaultUnitID] FOREIGN KEY([DefaultUnitID])
REFERENCES [dbo].[tbl_Unit] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingDefaultUnitID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingOfferingTypeID] FOREIGN KEY([OfferingTypeID])
REFERENCES [dbo].[tbl_OfferingType] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingOfferingTypeID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingOriginalCountryID] FOREIGN KEY([OriginalCountryID])
REFERENCES [dbo].[tbl_Country] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingOriginalCountryID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingOwnerID] FOREIGN KEY([OwnerID])
REFERENCES [dbo].[tbl_Contact] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingOwnerID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingProductCodeID] FOREIGN KEY([ProductCodeID])
REFERENCES [dbo].[tbl_ProductCode] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingProductCodeID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingStatusID2] FOREIGN KEY([StatusID])
REFERENCES [dbo].[tbl_OfferingStatus] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingStatusID2]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingSupplierCurrencyID2] FOREIGN KEY([SupplierCurrencyID])
REFERENCES [dbo].[tbl_Currency] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingSupplierCurrencyID2]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingSupplierID] FOREIGN KEY([SupplierID])
REFERENCES [dbo].[tbl_Account] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingSupplierID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingVendorCurrencyID] FOREIGN KEY([VendorCurrencyID])
REFERENCES [dbo].[tbl_Currency] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingVendorCurrencyID]
GO
ALTER TABLE [dbo].[tbl_Offering]  WITH CHECK ADD  CONSTRAINT [FOfferingVendorID] FOREIGN KEY([VendorID])
REFERENCES [dbo].[tbl_Account] ([ID])
GO
ALTER TABLE [dbo].[tbl_Offering] CHECK CONSTRAINT [FOfferingVendorID]

У меня такой же вопрос

4 комментария

Здравствуйте.

Да, Вам точно нужен индекс, т.к. и в GetOffering и в CheckHasDuplicate есть строки вида:
WHERE([tbl_Offering].[Article] = N'20237-179'

Именно здесь мы и получаем "затык"... Осталось определиться нужно ли создавать кластерный индекс?

На самом деле все просто: если у таблицы есть кластерный индекс, то записи, находящиеся в этой таблице, физически (в дисковом файле) упорядочиваются в соответствии с условием индексирования). Именно поэтому кластерный индекс может быть на таблицу только один. Некластерный индекс хранит ссылки на местоположение данных в дисковом файле.

Со всеми вытекающими.

Вытекающие:
1. Скорость доступа к строкам, при указании условия where, совпадающиего с условием индексирования для кластерного индекса, будет выше, чем при поиске записей по простому индексу (не надо высислять абсолютное положение данных из относительного).

2. Если кластерный индекс содержит большое количество часто обновляемых полей, то это отрицательно скажется на производительности, так как приведет к постоянному физическому перупорядочиванию данных.

3. Если кластерный индекс создается по IDENTITY столбцу, и из таблицы часто удаляются записи, то это приводит к сильной фрагментации таблицы.

Также, вот статья о "Идексах". Описано довольно подробно.

Спасибо, Дмитрий.
Подумав, решил попробовать создать некластерный индекс по полю Article, поскольку предполагается частая вставка новых записей (пункт 2 в Ваших вытекующих), и удаление (пункт 3). Сейчас тестирую импорт с некластерным индексом по полю Article, о результатах отпишусь.

Протестировал:
1. Где основное действие - это вставка (Insert) - 15 минут.
2. Где изменение (Update) - 14 минут.

Результаты на лицо :smile:
Спасибо большое за помощь :twisted:

А начиналось все с 1 часа.

Шаги по оптимизации:
1. Вместо использования Dataset.Append(), Dataset.Edit(), Dataset.Post() переделал на использование Insert и Update Query - стало 45 минут.
2. Дефрагментировал индексы - стало 33 минуты.
3. Добавил индекс по полю Article, по которому происходит фильтрация. Индекс по полю VendorID уже был ранее добавлен автоматически, при добавлении поля в сервис таблицы. Стало - 15 минут.

Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Ничто так не "убивает" базу, как "плохое" индексирование (С)

Создать правильные индексы - это только половина дела. Нужно еще и правильно ими управлять.

В процессе работы с базой, особенно, если данные в ней довольно часто модифицируются/добавляются/удаляются, со временем индексы приходят в некоторую "негодность". Увеличивается их "фрагментарность", ухудшается их влияние на скорость исполнения запросов к БД.
Оптимальным считается, когда уровень фрагментации индекса не превышает 10%, но для поддержания такого показателя необходимо проводить периодическую их дефрагментацию и реорганизацию.
(подробнее про реорганизацию и дефрагментацию)

Основным инструментом в процессе управления фрагментацией индексов выступает функция sys.dm_db_index_physical_stats (подробнее)

Для определения списка индексов с уровнем фрагментарности выше оптимальных 10% в своей работе я воспользовалась вот таким запросом:

DECLARE @db_name varchar(50) = N'db_name',
                @table_name varchar(250) = N'db_name.dbo.tbl_name'

SELECT  IndStat.database_id,
                IndStat.object_id,
                QUOTENAME(s.name) + '.' + QUOTENAME(o.name) AS [object_name],
                IndStat.index_id,
                QUOTENAME(i.name) AS index_name,
                IndStat.avg_fragmentation_in_percent,
                IndStat.partition_number,
                (SELECT count (*) FROM sys.partitions p
                        WHERE p.object_id = IndStat.object_id AND p.index_id = IndStat.index_id) AS partition_count
FROM sys.dm_db_index_physical_stats
    (DB_ID(@db_name), OBJECT_ID(@table_name), NULL, NULL , 'LIMITED') AS IndStat
        INNER JOIN sys.objects AS o ON (IndStat.object_id = o.object_id)
        INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
        INNER JOIN sys.indexes i ON (i.object_id = IndStat.object_id AND i.index_id = IndStat.index_id)
WHERE IndStat.avg_fragmentation_in_percent > 10 AND IndStat.index_id > 0

Если указать @table_name = NULL, тогда мы получим данные по всем таблицам указанной базы.
Если указать и @db_name = NULL - получим информацию по всем таблицам всех баз.

Естественно, для выполнения этого запроса нужно обладать некоторыми правами:

  • CONTROL на специфический объект БД.
  • VIEW DATABASE STATE для получения информации обо всех объектах определенной БД (@object_id = NULL).
  • VIEW SERVER STATE - для получения информации обо всех базах сервера (@database_id = NULL).

Так же перед использованием желательно обновить статистику БД.

Вышеприведенный запрос дает только справочную информацию. Но на основании функции sys.dm_db_index_physical_stats можно построить скрипт или хранимую процедуру, которая будет не только определять список индексов, нуждающихся в перестройке, но и будет сама проводить эту операцию.
Один из вариантов такого скрипта приведен ниже (проводит реорганизацию или перестройку(оффлайн) индексов таблиц текущей базы в зависимости от степени фрагментации):

USE [DATABASE];
GO

SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @dbid smallint;

-- Выбираем индексы с уровнем фрагментации выше 10%
-- Определяем текущую БД

SET @dbid = DB_ID();
SELECT
    [object_id] AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag, page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0  
AND index_id > 0 -- игнорируем heap
AND page_count > 25; -- игнорируем маленькие таблицы

-- объявляем курсор для списка обрабатываемых partition
DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;

OPEN partitions;

-- цикл по partition
WHILE (1=1)
BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

-- 30% считаем пределом для определения типа обновления индекса.
IF @frag 30.0
    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);
PRINT N'Выполнено: ' + @command;
END;

CLOSE partitions;
DEALLOCATE partitions;

-- удаляем временную таблицу
DROP TABLE #work_to_do;
GO

Еще посмотреть примеры скриптов/хранимок можно тут или вот тут.

Операцию по устранению дефрагментации индексов рекомендуется проводить регулярно (например, раз в неделю/месяц - в зависимости от величины операций модификации над хранимыми данными). Индексы, за состоянием которых не следят, могут очень существенно "просадить" производительность БД при исполнении запросов.

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Ничто не оптимизирует скорость работы базы так, как правильно подобранные индексы (С)

Многим, кто сталкивался с промышленной эксплуатацией баз данных определенных (скажем так, очень больших) размеров, приходилось проводить кропотливый анализ запросов на предмет возможной оптимизации скорости их выполнения.

Как оказалось, сама СУБД MSSQL версии 2005 и выше содержит механизм, грамотное использование которого может очень сильно облегчить работу по поиску некоторых узких мест.

Это sys.dm_db_missing_index_group_stats и связанные с ней функции (подробнее >>)
Данные для них формируются на основании статистики запросов к базе данных, и потому являются довольно хорошей информацией, от которой можно оттолкнуться при оптимизации.

Я не буду приводить тут подробный анализ ее использования, а только приведу запрос, который очень помог мне лично:

SELECT
        [Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),
        [TABLE] = [statement],
        [CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_'
                        + sys.objects.name COLLATE DATABASE_DEFAULT
                        + '_'
                        + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')+ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_')
                        + ' ON '
                        + [statement]
                        + ' ( ' + IsNull(mid.equality_columns, '')
                        + CASE
                                WHEN mid.inequality_columns IS NULL THEN ''
                                ELSE
                                        (CASE
                                                WHEN mid.equality_columns IS NULL THEN ''
                                                ELSE ','
                                         END)
                                        + mid.inequality_columns
                                END
                        + ' ) '
                        + CASE
                                WHEN mid.included_columns IS NULL THEN ''
                                        ELSE 'INCLUDE (' + mid.included_columns + ')'
                                END
                        + ';',
        mid.equality_columns,
        mid.inequality_columns,
        mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
        INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
        INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE   (migs.group_handle IN
                (SELECT TOP (500) group_handle
                FROM sys.dm_db_missing_index_group_stats WITH (nolock)
                ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
                AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

Естественно, для выполнения этого запроса нужно обладать правом VIEW SERVER STATE или любым другим правом, включающим в себя VIEW SERVER STATE.
Так же перед использованием желательно обновить статистику БД.

P.S. Еще можно почитать (и даже посмотреть видео) вот тут.

P.S.2 Определяем, как часто "пользуются" индексами:

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
 I.[NAME] AS [INDEX NAME],
 USER_SEEKS,
 USER_SCANS,
 USER_LOOKUPS,
 USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
 INNER JOIN SYS.INDEXES AS I
 ON I.[OBJECT_ID] = S.[OBJECT_ID]
 AND I.INDEX_ID = S.INDEX_ID

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Почти в каждом COM-объекте Terrasoft есть методы по умолчанию. В описании интерфейса эти методы имеют нулевой id. Например, ComponentsByName в IWindow, Values в IDataset или ItemsByName в IDataFields и т.д.
А доступ к этим методам выполняется быстрее, если обращаться к ним именно как методам по умолчанию.

Т.е вместо Window.ComponentsByName('EditName') использовать Window('EditName'), вместо Dataset.Values('FieldName') использовать Dataset('FieldName') и т.д.

Для доказательства во вложении два сервиса. В примере тестирование 1 000 000 обращений к компоненту окна двумя способами.
Результаты теста:

  • через Self.ComponentsByName('Edit'): 12,1 с
  • через Self('Edit'): 8,1 с

Таким образом использование свойств по умолчанию дает ускорение в 1,5 раза.

P.S. Навеяно блогом Артема: Dataset()

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Пишу кратко. Может кому-то следующая информация будет полезна и позволит сэкономить время.
Ниже я опишу как с помощью SQL Profiler я смог увидеть какие сервисы и в какой последовательности загружаются при запуске CRM с активным разделом - "Контрагенты". Также я смог получить информацию о времени потраченном при выполнении действий клиентской частью Terrasoft CRM и времени на выполнение запросов к БД.
Действия выполнялись на Terrasoft XRM 3.3.1.146 MS SQL.

Примечание: SQL Profiler-а нет в версии Express MS SQL Server

Шаги по подготовке:
1. CRM должна быть закрыта
2. Чистим папку с кэшем, чтобы увидеть все подтягиваемые сервисы из БД в профайлере.
Путь к папке для версии 3.3.1:
%appdata%\Terrasoft\3.3.1\Cache
3. Запускаем SQL Profiler

Получение трассировки запросов, выполняемых при загрузке CRM:
1. В SQL-Profiler создаем новую трассировку

2. Подключаемся к нашему серверу MS SQL

3. Запускаем трассировку со стандартными настройками:


4. Запускаем CRM и дожидаемся ее полной загрузки
5. Останавливаем трассировку
6. Сохраняем полученную трасировку в таблицу БД:

Подключаемся к базе и указываем базу и таблицу, в которую будут сохранены данные по трасировке.
Я использовал БД "master", владельца "dbo" и таблицу "tbl_trace1":

Во время сохранения эта таблица будет создана с нужными колонками и в нее будут выгружены данные.

Очистка таблицы от мусора.
Далее, для очистки таблицы от ненужных для меня данных я выполнил следующие запросы на БД "master" в MS SQL Server Management Studio:
1. Удалил записи у которых TextData пустое, ИЛИ поле ApplicationName не равно "TSClient.exe", или Duration пустое, ИЛИ LoginName не равно вашему логину (в моем случае это был 'Supervisor'):

DELETE FROM tbl_trace1
WHERE TextData IS NULL OR Duration IS NULL OR ApplicationName <> 'TSClient.exe' OR LoginName <> 'Supervisor'


2. Удалил не нужные для анализа колонки - EventClass, ApplicationName, NTUserName, LoginName, ClientProcessID, SPID, BinaryData:

 

ALTER TABLE tbl_trace1
DROP COLUMN EventClass, ApplicationName, NTUserName, LoginName, ClientProcessID, SPID, BinaryData

 

 


Извлечение из таблицы трассировки полезной информации для дальнейшего анализа:
1. Добавляем колонки в таблицу:
DiffBetweenSteps - поле будет содержать значение времени в милисекундах, которое прошло между временем окончания выполнения предыдущего запроса и временем начала выполнения текущего запроса. Значение этого поля вмещает в себя время потраченное клиентской частью программы (выполнение скриптов, прорисовка окон и т.д. - все, что выполняется без обращения к серверу БД);
ServiceID и ServiceCode - поля содержащие ID и код сервиса, данные по которому подтягиваются из БД. По этим полям видно в какой последовательности выполняется загрузка сервисов во время запуска CRM;
PrevRowNumber - значение предыдущего по последовательности выполнения запроса, нужно для связки, если будет выполняться сортировка по какому-то полю.

ALTER TABLE tbl_trace1 ADD DiffBetweenSteps int, ServiceID uniqueidentifier, ServiceCode nvarchar(250), PrevRowNumber int


2. Создаем функцию для проверки того, что передаваемое как аргумент значение является GUID-значением и выполняем ее на базе данных "master" (в которой находиться наша таблица трассировки):

 

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[IsGuid] ( @testString varchar(38))
returns int
AS
begin
    declare @ret int
    SELECT  @ret = 0,
            @testString = REPLACE(REPLACE(@testString, '{', ''), '}', '')
    IF len(isnull(@testString, '')) = 36 AND
       @testString NOT LIKE '%[^0-9A-Fa-f-]%' AND
       -- check for proper positions of hyphens (-)  
       charindex('-', @testString) = 9 AND
       charindex('-', @testString, 10) = 14 AND
       charindex('-', @testString, 15) = 19 AND
       charindex('-', @testString, 20) = 24 AND
       charindex('-', @testString, 25) = 0
          SET @ret = 1
   
    RETURN @ret
end


Эта функция понадобиться в дальнейшем
3. Заполняем значение поля DiffBetweenSteps с помощью следующего sql кода:

 

 

-- Проставляем разницу во времени между окончанием одного запроса и началом выполнения следующего
declare @EndTimePrev datetime
declare @StartTimeNext datetime
declare @EndTimeNext datetime
declare @RowNumber int
declare @DiffBetweenSteps int

SET @EndTimePrev = NULL

declare c_Rec cursor FOR
  SELECT RowNumber, StartTime, EndTime FROM tbl_trace1

OPEN c_Rec
FETCH NEXT FROM c_Rec INTO @RowNumber, @StartTimeNext, @EndTimeNext
WHILE @@FETCH_STATUS=0
BEGIN
        SET @DiffBetweenSteps = DATEDIFF(millisecond, @EndTimePrev, @StartTimeNext)  

        IF (@DiffBetweenSteps IS NULL) SET @DiffBetweenSteps = 0

    UPDATE tbl_trace1
    SET DiffBetweenSteps = @DiffBetweenSteps  
    WHERE RowNumber = @RowNumber
   
    SET @EndTimePrev = @EndTimeNext

    FETCH NEXT FROM c_Rec INTO @RowNumber, @StartTimeNext, @EndTimeNext
END
CLOSE c_Rec
DEALLOCATE c_Rec


4. Заполняем полей ServiceID и ServiceCode с помощью следующего sql кода:

 

 

declare @RowNumber int
declare @ServiceGUID nchar(38)

-- Начиная с 205 позиции вырезаем 38 символов и сохраняем их в переменную @ServiceID
declare c_Rec cursor FOR
  SELECT RowNumber, substring(TextData, 205, 38) FROM tbl_trace1

OPEN c_Rec
FETCH NEXT FROM c_Rec INTO @RowNumber, @ServiceGUID
WHILE @@FETCH_STATUS=0
BEGIN
    -- Проверяем является ли вырезанный кусок текста GUID-значением
    IF (SELECT dbo.IsGuid(@ServiceGUID)) = 1
                BEGIN
                        -- Есди - да, то вытягиваем по полученому ID сервиса его код из рабочей базы CRM
                        UPDATE tbl_trace1
                        SET ServiceID = @ServiceGUID, ServiceCode = (SELECT Code FROM XRM.dbo.tbl_Service WHERE ID = @ServiceGUID)
                        WHERE RowNumber = @RowNumber           
                END
        ELSE
                BEGIN
                        UPDATE tbl_trace1
                        SET ServiceID = NULL, ServiceCode = ''
                        WHERE RowNumber = @RowNumber           
                END

    FETCH NEXT FROM c_Rec INTO @RowNumber, @ServiceGUID
END
CLOSE c_Rec
DEALLOCATE c_Rec

 

 

В коде

SELECT Code FROM XRM.dbo.tbl_Service WHERE ID = @ServiceGUID


вместо названия БД 'XRM' вам нужно указать название вашей рабочей CRM базы.

 

Поскольку запрос на полученние данных сервиса выглядит следующим образом:

exec sp_executesql N'SELECT TOP 1
        [tbl_Service].[ID] AS [ID],
        [tbl_Service].[XMLData] AS [XMLData]
FROM
        [dbo].[tbl_Service] AS [tbl_Service]
WHERE([tbl_Service].[ID] = @P1)'
,N'@P1 nvarchar(38)',N'{64A6CED0-5C42-481F-97CA-C9632144B101}'


, где в конце указывается значение ID сервиса, то по этому ID мы можем получать необходимые данные о загружаемом сервисе, такие как код сервиса, его местонахождение в дереве сервисов и т.д. Для себя мы возьмем только код сервиса, чтобы наглядно было видно какой сервис запрашивается из БД.
5. Проставляем значение предыдущего шага в поле PrevRowNumber

 

-- Простановка предыдущего шага
declare @PrevRowNumber int
declare @RowNumber int

SET @PrevRowNumber = 0

declare c_Rec cursor FOR
  SELECT RowNumber FROM tbl_trace1

OPEN c_Rec
FETCH NEXT FROM c_Rec INTO @RowNumber
WHILE @@FETCH_STATUS=0
BEGIN
    UPDATE tbl_trace1
    SET PrevRowNUmber = @PrevRowNumber  
    WHERE RowNumber = @RowNumber
   
    SET @PrevRowNumber = @RowNumber

    FETCH NEXT FROM c_Rec INTO @RowNumber
END
CLOSE c_Rec
DEALLOCATE c_Rec


6. В итоге, с помощью SQL-запроса

 

 

SELECT RowNumber, TextData, CPU, Reads, Writes, Duration/(1000) AS Duration_t, StartTime, EndTime, ServiceCode, DiffBetweenSteps, PrevRowNumber
FROM tbl_trace1


выводим следующую таблицу:

7. Также можно выполнить сортировки по разным колонкам и увидеть:

 

 

 

SELECT RowNumber, TextData, CPU, Reads, Writes, Duration/(1000) AS Duration_t, StartTime, EndTime, ServiceCode, DiffBetweenSteps, PrevRowNumber
FROM tbl_trace1
ORDER BY CPU DESC

 

SELECT RowNumber, TextData, CPU, Reads, Writes, Duration/(1000) AS Duration_t, StartTime, EndTime, ServiceCode, DiffBetweenSteps, PrevRowNumber
FROM tbl_trace1
ORDER BY Duration_t DESC

 

SELECT RowNumber, TextData, CPU, Reads, Writes, Duration/(1000) AS Duration_t, StartTime, EndTime, ServiceCode, DiffBetweenSteps, PrevRowNumber
FROM tbl_trace1
ORDER BY DiffBetweenSteps DESC
  • каке запросы сколько используют процессорного времени в милисекундах на их выполнение

  • Выводимая таблица:

     

  • какие запросы дольше всего выполнялись, отсортировав по полю Duration. Значение в поле Duration храниться в микросекундах, делю на 1000, чтобы выводилось в милисекундах:

  • Выводимая таблица:

     

  • увидеть между какими запросами дольше всего происходили операции на стороне клиента, что поможет локализировать место в работе клиентской части Террасофт, которое требует больше всего времени на выполнение:

  • Выводимая таблица:

     

  • поля Reads и Writes показывают количество логических чтений и записей с жесткого диска на сервере во время выполнения события (запроса)

8. Также можно подсчитать общее время на выполнение запросов (ServerDuration), общее время на выполнение клиентской частью (ClientDuration), общее время на запуск CRM (OverallDuration):

SELECT Sum(Duration)/1000 AS ServerDuration, Sum(DiffBetweenSteps) AS ClientDuration, Sum(Duration/1000+DiffBetweenSteps) AS OverallDuration
FROM tbl_trace1


Выводимое значение:

 

Аналогичным образом можно анализировать не только процесс запуска CRM, а любое выполняемое действие с CRM, например, открытие окна редактирования.

Прикрепил SQL код, используемый мною - SLQ_code.txt
В архиве прикрепил картинки в их исходном размере - Images.zip.

Будет желание, делитесь полезными замечаниями :smile:

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Вопрос

В бизнес процессах если используется задержка процесса по времени, то следующая задача после процесса не создается автоматически.
т.е. чтобы запустить задачу надо выполнить следующий шаг запущенного процесса.
Это не всегда удобно, т.к. таких процессов может быть запущенно огромное количество и пользователь не помнит, а может и не знает, что надо продолжить процесс.
Предлагается при окончании задержки выполнения процесса, чтобы автоматически запускалось окно создания задачи

У меня такой же вопрос

3 комментария

В базовой конфигурации такой функциональности нет, взгляните на наше расширение (http://community.terrasoft.ua/catalog/4881), возможно заинтересует.

"Валерий Андрусик" написал:

Валерий, ваше решение достаточно интересное хотелось бы узнать о нем подробнее - стоимость, условие внедрения.
Что касается затрагиваемой мною темы, то здесь я считаю что была недоработка системы, т.е. если пользователь отменил действие или что то допустил не то, это другое дело, но запланированная задержка, должна быть задержкой на указанный промежуток времени, а не на все время :smile:

Да, проблему с задержкой обсуждали вот тут:
http://community.terrasoft.ua/ideas/3363

Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Здравствуй, человек-читатель блогов на Community.

Хочу рассказать о своем недавнем опыте оптимизации курсора в SQL Server.
Первое, что нужно знать, курсор – это не хорошо, а плохо. Где есть возможность заменить курсор на INSERT SELECT или на использование временной таблицы, там нужно это сделать (за редким исключением). Курсор – это почти всегда дополнительные ресурсы сервера и резкое падение производительности по сравнению с другими решениями.
Второе, иногда без курсора не обойтись – там где не обойтись без построчного прохода по результату выборки. Вот в таких случаях очень важно правильно создать нужный тип курсора – тот, который соответствует решаемой задаче. Общий синтаксис объявления курсора имеет вид:

DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]

Остановлюсь на первых трех строчках ключевых параметров.
    LOCAL или GLOBAL:    если хотим, чтобы курсор был доступен другим процедурам, функциям, пакетам в рамках нашей сессии, то GLOBAL – в этом случае за удалением курсора следим сами (команда DEALLOCATE). Во всех остальных случаях (т.е. в подавляющем своем большинстве) – LOCAL. Внимание, по умолчанию создается именно GLOBAL курсор!
    FORWARD_ONLY или SCROLL: если хотим ходить по курсору, как ненормальные, туда-сюда, то SCROLL, иначе – FORWARD_ONLY. Внимание, по умолчанию создается SCROLL курсор!
    STATIC или KEYSET, DYNAMIC, FAST_FORWARD: если хотим, чтобы при проходе по курсору отображалась актуальная информация из таблицы (т.е., если после открытия курсора, мы поменяли информацию в одном из полей таблицы и хотим, чтобы при проходе по курсору в нужной строчке курсора была уже обновленная информация), то используем или KEYSET (если КАЖДАЯ таблица, участвующая в выборке, имеет уникальный индекс) или DYNAMIC (самый медленный тип). Если же нам нужен снимок результата выборки после открытия курсора – STATIC (самый быстрый тип – копия результата выборки копируется в базу tempdb и работаем уже с ней). FAST_FORWARD = KEYSET+FORWARD_ONLY+READ_ONLY – пацаны из инета пишут, что STATIC дольше открывается (т.к. создается копия в tempdb), но быстрее работает, а FAST_FORWARD – наоборот. Так что если количество записей велико (насколько большое показывает практика), то применяем STATIC, иначе – FAST_FORWARD. Внимание, по умолчанию создается DYNAMIC курсор.

Таким образом, для большого кол-ва записей в большинстве случаев мой выбор:
DECLARE cursor_name CURSOR LOCAL FORWARD_ONLY STATIC FOR
select_statemen
t

для небольшого кол-ва записей:
DECLARE cursor_name CURSOR LOCAL FAST_FORWARD FOR
select_statement

Теперь перейдем к практике (что собственно и подтолкнуло меня к писанине сего).
Испокон веков при объявлении курсора я применял конструкцию DECLARE ... CURSOR LOCAL FOR...
При разработке интеграции с одной очень нехорошей базой, в которой нет ни одного индекса и не одного ключа, я применил тот же подход при объявлении курсоров, что и всегда. Выборка одного курсора содержала 225 000 записей. В результате процесс импорта данных из такой базы занял 15 часов 14 минут !!! И хотя импорт и был первичный (т.е. одноразовый), но даже для нормального тестирования такого импорта потребовалось бы несколько суток! После замены вышеназванной конструкции при объявлении курсора на DECLARE .. CURSOR LOCAL FORWARD_ONLY STATIC FOR.. весь процесс импорта занял ... внимание ... 10 минут 5 секунд !!! Так что игра точно стоит свеч.
Хочу повториться, что идеальный вариант - это все же не использовать курсоры вообще - для СУБД MS SQL намного роднее реляционный, а не навигационный подход.

Поделиться

10 комментариев

Артем, классно, сколько пишу на T-SQL почти никогда не применял данные типы курсоров - всегда считал что это медленно и все старался заменить на insert select и декартово произведение, но оказывается эти параметры сильно ускоряют! Интересно! Надо будет запомнить. Есть правда еще один способ который ускоряет. Как известно если явно не стартовать транзакцию, то все запросы выполняются в рамках неявных транзакций, что сильно "бьет" по производительности, так вот если завести счетчик и стартовать транзакцию и коммитить после каждой 1000-й вставки, то скорость возрастает на порядки. Интересно попробовать это в случае с LOCAL FORWARD_ONLY STATIC :)
Только для надежности надо установить
SET CURSOR_CLOSE_ON_COMMIT OFF
А то курсор закроется после коммита...

Полезно, спасибо!

 

Артем, спасибо! Очень полезная информация.

Олег Лабьяк,
разработчик,
3-я линия Службы поддержки Terrasoft.

"Репко Артём" написал:если хотим ходить по курсору, как ненормальные, туда-сюда, то SCROLL

Артем, объясни, пожалуйста, как это понимать?

"Раловец Ольга" написал:как это понимать?

Ольга, простите за вольный стиль. Я имел ввиду, что если курсор SCROLL, то по нему можно двигаться как вперед, так и назад, а если FORWARD_ONLY - только вперед.

Думаю, Артём имел в виду использование не только с начала и до конца FETCH NEXT, а и таких возможностей, как FETCH FIRST, FETCH LAST, FETCH PRIOR, FETCH ABSOLUTE n, FETCH RELATIVE n.

Если коротко, цитата из хелпа по SQL:

Cursor Classifications
A cursor can be classified by the fetch options it supports:

Forward-only
Rows must be fetched serially from the first row to the last row. FETCH NEXT is the only fetch operation allowed.

Scrollable
Rows can be randomly fetched from anywhere in the cursor. All the fetch operations are allowed (except that dynamic cursors do not support fetch absolute).

Scrollable cursors are especially useful for supporting online applications. A cursor can be mapped to a grid or list box in the application. As the user scrolls up and down and all around the grid, the application uses scroll fetches to retrieve the rows from the cursor the user wants to see.

Олег Лабьяк,
разработчик,
3-я линия Службы поддержки Terrasoft.

Спасибо, Артем, спасибо, Олег. :)

"Репко Артём" написал:Ольга, простите за вольный стиль

Ваш стиль кажется мне приемлемым.

Коллеги, если всем понравилось, голосуем! +1
Артёму большое спасибо!

:twisted:

Огромнейшее спасибо! На "раскидывание" импортированных данных по таблицам (на 30 000 записей) вместо почти 5 часов ушло 50 минут.

Войдите или зарегистрируйтесь, чтобы комментировать