Нужна помощь советом в какую сторону нужно смотреть.
Реализовал импорт прайс-листа по продуктам указанного производителя. В начале импорта указывается производитель и выбирается 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
[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
функции CheckHasDuplicate
[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.rar.
Смотрю в сторону индексов. В таблице tbl_Offering 152773 записи. С индексами раньше особо не работал. Возможно, стоит добавить некластерный индекс по полю Article, по которому происходит фильтрация продуктов? Может, еще что-то посоветуете? Нужно ли запускать какую-то регулярную обработку индексов, чтобы их оптимизировать?
Вот SQL-код создания таблицы tbl_Offering:
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]
Нравится
Здравствуйте.
Да, Вам точно нужен индекс, т.к. и в 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 минут.