select | Community Creatio

Вопрос

14 мая 2013 15:04

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

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

функции 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.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 комментария

Олейник Дмитрий

14 мая 2013 15:58

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

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

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

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

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

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

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

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

Ответить

Олейник Дмитрий

14 мая 2013 16:00

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

Ответить

Кошкаров Андрей

14 мая 2013 16:42

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

Ответить

Кошкаров Андрей

14 мая 2013 17:14

Протестировал:
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 минут.

Ответить

Показать все комментарии

Вопрос

Добавление не повторяющихся данных в БД

Котельников Александр Васильевич

25 февраля 2013 13:06

Подскажите пожалуйста. Вопрос у меня по работе с БД.
Необходимо осуществить добавления в БД не повторяющихся данных из файла. Как получить данные из файла и добавить я разобрался. Добавление делаю с помощью insert:

if (????????){

var insert = new Insert(UserConnection)

  .Into("ConfItemsCode")

  .Set("Name", Column.Const(str));

  insert.Execute();

}

Но передо добавлением в БД мне надо проверить есть ли в этой таблице уже такая запись. Делаю это так:

var select = new Select(UserConnection)

  .Column("Name")

  .From("ConfItemsCode")

  .Where("Name").IsEqual(str); // пробовал и IsLike

str - это переменная типа string - данный из файла.
Вопрос: Как мне узнать есть ли в select какие -либо записи. Пробовал RowCount, HasCondition. Подскажите как это сделать правильно. Желательно получить int или bool для того чтобы проверить а только потом добавлять.

Нравится

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

Олейник Дмитрий

25 февраля 2013 16:07

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

Проще всего воспользоваться EnitySchemaQuery классом:

string Str = "test";
 
 
var manager = UserConnection.EntitySchemaManager;
var query = new EntitySchemaQuery(manager, "Account");
var currentAccountId = query.AddColumn(query.RootSchema.PrimaryColumn.Name);
var accountPhone = query.AddColumn("Name").Name;
query.Filters.Add(query.CreateFilterWithParameters(FilterComparisonType.Equal, "Name", Str));
var queryResult = query.GetEntityCollection(UserConnection);
if (queryResult.Count == 0) 
{
       var insert = new Insert(UserConnection)
  .Into("ConfItemsCode")
  .Set("Name", Column.Const(str));
  insert.Execute();
}

Ответить

Котельников Александр Васильевич

25 февраля 2013 18:16

Дмитрий, спасибо. Все получилось

Ответить

Показать все комментарии

Подписаться на select