Управление индексами в 1С 7.7 SQL   

Задача проектирования структуры БД является ключевой с точки зрения производительности и масштабируемости ИТ систем. Под структурой можно также понимать структуру индексов sql. В зависимости от того насколько правильно расставлены индексы sql один и тот же запрос может формироваться различное время. Соотношение во времени выполнения может составлять порядки. Кроме этого, совокупная нагрузка на серверные ресурсы будет существенно отличатся. Именно поэтому технология правильной расстановки индексов является одной из приоритетных в общей технологии оптимизации.

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

Рассмотрим среду 1С 7.7. В системе 1С невозможно стандартными средствами добавлять индексы. Это с одной стороны упрощает разработку. На начальном этапе не нужно задумываться, что такое индексы вообще. Ограничения по фильтрации и сортировке экранных форм(например в справочниках только сортировка по коду, наименованию или реквизитам с признаком отбора – т.е. по тем полям где есть по умолчанию индексы ) позволяют довольно длительный интервал времени после внедрения не беспокоится о проблеме оптимизации индексов. Но здесь есть и отрицательный момент. При увеличении объемов БД запросы с учетом стандартных sql индексов отрабатывают все хуже и хуже. Нагрузка на сервер и время отчетов увеличивается до тех пор, пока эти значения не принимают критической величины. Как правило, после этого принимается решение об коренной перестройке структуры объектов 1С либо об апгрейде серверного оборудования. И первый, и второй вариант требуют существенных затрат. Например, иногда структура объектов в текущей реализации выглядит вполне нормальной, и вполне устраивала разработчиков, если бы была возможность, например, добавить индекс по шапке документа. Классическим примером здесь можно привести ситуацию, когда структура документов завязана иерархически и реквизит с документом родителем хранится в шапке документа. В данном случае конечно можно вынести этот реквизит в общие реквизиты но это будет неэффективное решение как с точки зрения структуры запросов так и с точки зрения общей производительности. (зачем всем документам хранить реквизит который нужен только для одного типа документов?).

Не смотря на ограниченность управления индексами в 1С 7,7. решение существует. У этого метода есть все же определенные недостатки связанные с удобствами администрирования.

  • Необходимо определится с префиксом наименования индекса и в дальнейшем на этом сервере все индексы в рамках баз 1С нужно называть с предопределенным префиксом. Пускай этот префикс будет P1С – это довольно редкий префикс.
  • В процедуре master.dbo.sp_statistics (процедура, ответственная за проверку наличия индексов таблиц) необходимо изменить небольшую часть кода. Смысл изменений в том что если эту процедуру вызывает приложение 1С то в этом случае в результат выполнения этой процедуры не будут попадать индексы с префиксом P1С.
    if app_name()='1CV7'  /* это проверка какое приложение вызывает процедуру*/
    begin /*этот вариант срабатывает если запущена процедура из 1С */
    SELECT
    TABLE_QUALIFIER,
    TABLE_OWNER,
    TABLE_NAME,
    NON_UNIQUE,
    INDEX_QUALIFIER,
    INDEX_NAME,
    TYPE,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    COLLATION,
    CARDINALITY,
    PAGES,
    FILTER_CONDITION = convert(varchar(128),null)
    FROM #TmpIndex
    WHERE
    (INDEX_NAME like @index_name /* If matching name */
    or INDEX_NAME is null) /* If SQL_TABLE_STAT row */
    and (substring(INDEX_NAME,1,3)<>'P1C' or INDEX_NAME is NULL)
    /*вот это проверка на префикс, если он начинается на P1C*/
    /*то в результат выполнения процедуры не попадает*/
    ORDER BY 4, 7, 6, 8
    end
    else
    begin /* это старый вариант реализации, стандартный*/
    SELECT
    TABLE_QUALIFIER,
    TABLE_OWNER,
    TABLE_NAME,
    NON_UNIQUE,
    INDEX_QUALIFIER,
    INDEX_NAME,
    TYPE,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    COLLATION,
    CARDINALITY,
    PAGES,
    FILTER_CONDITION = convert(varchar(128),null)
    FROM #TmpIndex
    WHERE
    (INDEX_NAME like @index_name /* If matching name */
    or INDEX_NAME is null) /* If SQL_TABLE_STAT row */
    end
  • Каждый добавленный индекс фиксировать в T-SQL скрипт. Данный скрипт поставить на исполнение в SQL jobs. Смысл этих действий обуславливается тем что при структурном(как правило это редкая операция) изменении объектов 1С удаляет старый объект и создает новый. Таким образом если не будет job-а то в этом случае после структурного изменения объекта все дополнительные индексы по нему исчезнут. Реализация скрипта очень простая . Проверяется наличие дополнительных индексов и если какого то из них нет то он создается. Эту операцию можно выполнять даже в рабочем режиме. Хотя ее достаточно выполнять один раз после изменения конфигурации. Я например ставлю частоту выполнения 1 час – этого более чем достаточно.

 

Предлагаем вашему вниманию следующую технологию с целью возможного расширения индексов. Сразу хочу отметить, что в 99-и процентов случаев индексы от 1С по умолчанию работают эффективно (конечно, кроме тех случаев, когда бездумно ставят сортировку и отбор на реквизиты). Поэтому варианты с удалением индексов 1С мы не рассматриваем.

После изменения процедуры sp_statistics мы можем беспрепятственно добавлять индексы. Эффект от правильной расстановки индексов может быть очень большим. Например, один из отчетов который формировался ранее порядка 7-и часов, стал формироваться порядка 20-и минут. Это и не удивительно так по миллионной таблице осуществлялось множество запросов с фильтром по реквизиту без индекса, в результате чего осуществлялся full scan. Кроме длительно времени выполнения запроса еще большим минусом является в момент выполнения запроса большая загрузка сервера.

Теперь кратко рассмотрим, как пользоваться средством SQL-сервера для оптимизации индексов – Index Tuning wizard. Запустим профайлер и выберем пункт «Index Tuning wizard».

 

 

Откроется диалоговое окно Index Tuning wizard.

 

 

Перейдем на следующую страницу помощника. Откроется диалог авторизации.

 

 

Необходимо выбрать сервер и указать логин и пароль для входа.

 

 

После будет нужно выбрать анализируемую базу данных и режим анализа. Важно оставить пометку «keep all existing indexes» включенной, чтобы помощник не пытался удалить существующие индексы, даже если они не оптимальны. Режим «Fast» не следует использовать из-за его неточности. Режим «Thorough» может занять слишком много времени для анализа. Поэтому используем золотую середину. Переходим к следующему окну.

 

 

Здесь мы должны указать трассу, которую мы будем анализировать в виде файла на диске или таблицы в базе данных. Вместо трассы можно указать файл, содержащий SQL-запрос, который мы хотим проанализировать. После указания трассы нужно обязательно посмотреть «Advanced Options».

 

 

Это диалоговое окно содержит 3 важных параметра. Первый из них, важнейший, количество запросов для анализа. По-умолчанию там стоит цифра 200. Это означает, что из трассы случайным образом будут выбраны 200 запросов, которые и будут проанализированы. Если снять пометку, то будет проанализирована вся трасса. Это очень важный момент. Важно, чтобы анализировалась трасса, которая содержит как можно более репрезентативную выборку запросов, но не содержит в себе множества легковесных запросов. Если трасса небольшая, то можно снять пометку, чтобы анализировались все запросы. Второй параметр – это максимальный объем места на диске для индексов, которые будут добавляться. Индексы, которые превышают этот объем, добавляться не будут. Третий параметр – ограничение на количество колонок, входящих в индекс. После выбора значений и возврата в основную форму, переходим к следующей странице помощника.

 

 

На этой странице нам предстоит выбрать таблицы, индексы к которым будут анализироваться. Рекомендуется выбрать важные таблицы, остальные оставить не помеченными. Также, с учетом будущего роста базы можно установить значения в колонке «Projected Rows», указать, какое количество строк в данной таблице планируется с ростом базы. После нажатия на кнопку «Далее», будет осуществляться анализ.

 

 

Когда процесс анализа будет завершен, мы увидим таблицу результатов анализа.

 

 

В этой таблице будут представлены и существующие индексы, и новые, если помощник посчитал, что они необходимы. Новые индексы обозначены специальным значком (см. индекс с именем «RA11583»). Ниже в тексте написано, сколько процентов прироста производительности оттестированных запросов повлечет за собой создание рекомендуемых индексов. Перейдем дальше.

 

 

Здесь нам предстоит выбрать, что делать с полученными данными. Предлагается два варианта: применить изменения (сразу или в назначенное время), или сохранить сгенерированный SQL-скрипт в файл. Мы выбираем второй вариант, так как автоматически сгенерированные имена индексов нам не подходят, а также, потому что потом можно будет посмотреть на рекомендации и выбрать, что из них делать, а что нет. Кроме новых индексов может быть предложено создать и соответствующую статистику. Вот как выглядит пример файла рекомендаций.

/* Created by: Index Tuning Wizard	*/
/* Date: 18.05.2005 */
/* Time: 10:50:10 */
/* Server Name: VLADA */
/* Database Name: TestMain */
/* Workload File Name: D:WorkSQLMSSQLTRACEТрассировка.trc */


USE [TestMain]
go

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
go

DECLARE @bErrors as bit

BEGIN TRANSACTION
SET @bErrors = 0

CREATE NONCLUSTERED INDEX [RA11583] ON [dbo].[RA1158] ([SP1160] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

BEGIN TRANSACTION
SET @bErrors = 0

CREATE NONCLUSTERED INDEX [_1SJOURN10] ON [dbo].[_1SJOURN]
([IDDOCDEF] ASC, [DATE_TIME_IDDOC] ASC, [CLOSED] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION


/* Statistics to support recommendations */

CREATE STATISTICS [hind_874486194_6A_1A] ON [dbo].[RA1158]
([SP1160], [IDDOC])
CREATE STATISTICS [hind_429960608_9A_4A] ON [dbo].[_1SJOURN]
([CLOSED], [IDDOCDEF])
CREATE STATISTICS [hind_429960608_9A_3A] ON [dbo].[_1SJOURN]
([CLOSED], [IDDOC])
CREATE STATISTICS [hind_429960608_6A_4A] ON [dbo].[_1SJOURN]
([DATE_TIME_IDDOC], [IDDOCDEF])
CREATE STATISTICS [hind_429960608_3A_4A] ON [dbo].[_1SJOURN]
([IDDOC], [IDDOCDEF])
CREATE STATISTICS [hind_429960608_6A_3A] ON [dbo].[_1SJOURN]
([DATE_TIME_IDDOC], [IDDOC])
CREATE STATISTICS [hind_429960608_9A_6A] ON [dbo].[_1SJOURN]
([CLOSED], [DATE_TIME_IDDOC])
CREATE STATISTICS [hind_429960608_3A_6A] ON [dbo].[_1SJOURN]
([IDDOC], [DATE_TIME_IDDOC])
CREATE STATISTICS [hind_429960608_3A_9A] ON [dbo].[_1SJOURN]
([IDDOC], [CLOSED])
CREATE STATISTICS [hind_429960608_6A_9A_3A] ON [dbo].[_1SJOURN]
([DATE_TIME_IDDOC], [CLOSED], [IDDOC])
CREATE STATISTICS [hind_429960608_4A_6A_3A] ON [dbo].[_1SJOURN]
([IDDOCDEF], [DATE_TIME_IDDOC], [IDDOC])
CREATE STATISTICS [hind_429960608_6A_9A_4A] ON [dbo].[_1SJOURN]
([DATE_TIME_IDDOC], [CLOSED], [IDDOCDEF])

 

Полный текст измененной процедуры sp_statistics для SQL-2000 sp3:

/*	Procedure for 8.0 server */
ALTER PROCEDURE sp_statistics (
@table_name sysname,
@table_owner sysname = null,
@table_qualifier sysname = null,
@index_name sysname = '%',
@is_unique char(1) = 'N',
@accuracy char(1) = 'Q')
AS
set nocount on
DECLARE @indid int
DECLARE @lastindid int
DECLARE @table_id int
DECLARE @full_table_name nvarchar(257)

create table #TmpIndex(
TABLE_QUALIFIER sysname collate database_default NULL,
TABLE_OWNER sysname collate database_default NULL,
TABLE_NAME sysname collate database_default NOT NULL,
INDEX_QUALIFIER sysname collate database_default null,
INDEX_NAME sysname collate database_default null,
NON_UNIQUE smallint null,
TYPE smallint NOT NULL,
SEQ_IN_INDEX smallint null,
COLUMN_NAME sysname collate database_default null,
COLLATION char(1) collate database_default null,
index_id int null,
CARDINALITY int null,
PAGES int null,
status int NOT NULL)

if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end

if @accuracy not in ('Q','E')
begin
raiserror (15251,-1,-1,'accuracy','''Q'' or ''E''')
return
end

if @table_owner is null
begin /* If unqualified table name */
SELECT @full_table_name = quotename(@table_name)
end
else
begin /* Qualified table name */
if @table_owner = ''
begin /* If empty owner name */
SELECT @full_table_name = quotename(@table_owner)
end
else
begin
SELECT @full_table_name = quotename(@table_owner) +
'.' + quotename(@table_name)
end
end
/* Get Object ID */
SELECT @table_id = object_id(@full_table_name)

/* Start at lowest index id */
SELECT @indid = min(indid) FROM sysindexes
WHERE not (@table_id is null)
AND id = @table_id
AND indid > 0
AND indid < 255

/* Create a temp table to correct the ordinal position of the columns */
create table #TmpColumns
(ordinal int identity(1,1),
colid smallint not null)

/* Load columns into the temp table */
insert into #TmpColumns (colid)
select c.colid
from syscolumns c
where c.id = @table_id
order by c.colid

WHILE @indid is not NULL
BEGIN
INSERT #TmpIndex /* Add all columns that are in index */
SELECT
DB_NAME(), /* TABLE_QUALIFIER*/
USER_NAME(o.uid), /* TABLE_OWNER*/
o.name, /* TABLE_NAME*/
o.name, /* INDEX_QUALIFIER*/
x.name, /* INDEX_NAME*/
case /* NON_UNIQUE*/
WHEN x.status&2 <> 2 then 1 /* Nonunique index*/
else 0 /* Unique index*/
end,
case /* TYPE*/
when @indid > 1 then 3 /* Non-Clustered*/
else 1 /* Clustered index*/
end,
tc.ordinal, /* SEQ_IN_INDEX*/
INDEX_COL(@full_table_name, indid, tc.ordinal),/* COLUMN_NAME*/
'A', /* COLLATION*/
@indid, /* index_id*/
case /* CARDINALITY*/
when @indid > 1 then NULL /* Non-Clustered*/
else x.rows /* Clustered index*/
end,
case /* PAGES*/
when @indid > 1 then NULL /* Non-Clustered*/
else x.dpages /* Clustered index*/
end,
x.status /* status*/
FROM sysindexes x, syscolumns c, sysobjects o, #TmpColumns tc
WHERE
not (@table_id is null)
AND x.id = @table_id
AND x.id = o.id
AND x.id = c.id
AND tc.colid = c.colid
AND tc.ordinal < keycnt+(x.status&18)/18
/* all but Unique Clust indices have an extra key */
AND INDEX_COL(@full_table_name, indid, tc.ordinal) IS NOT NULL
AND indid = @indid
AND (x.status&2 = 2
OR @is_unique <> 'Y')
AND (x.status&32) = 0
/*
** Now move @indid to the next index.
*/
SELECT @lastindid = @indid
SELECT @indid = NULL

SELECT @indid = min(indid)
FROM sysindexes
WHERE not (@table_id is null)
AND id = @table_id
AND indid > @lastindid
AND indid < 255
END

/* now add row for table statistics */
INSERT #TmpIndex
SELECT
DB_NAME(), /* TABLE_QUALIFIER*/
USER_NAME(o.uid), /* TABLE_OWNER */
o.name, /* TABLE_NAME */
null, /* INDEX_QUALIFIER*/
null, /* INDEX_NAME */
null, /* NON_UNIQUE */
0, /* SQL_TABLE_STAT */
null, /* SEQ_IN_INDEX */
null, /* COLUMN_NAME */
null, /* COLLATION */
0, /* index_id */
x.rows, /* CARDINALITY */
x.dpages, /* PAGES */
0 /* status */
FROM sysindexes x, sysobjects o
WHERE not (@table_id is null)
AND o.id = @table_id
AND x.id = o.id
AND (x.indid = 0 or x.indid = 1)/*If there are no indexes*/
/*then table stats are in*/
/*a row with indid =0*/
if app_name()='1CV7'
begin
SELECT
TABLE_QUALIFIER,
TABLE_OWNER,
TABLE_NAME,
NON_UNIQUE,
INDEX_QUALIFIER,
INDEX_NAME,
TYPE,
SEQ_IN_INDEX,
COLUMN_NAME,
COLLATION,
CARDINALITY,
PAGES,
FILTER_CONDITION = convert(varchar(128),null)
FROM #TmpIndex
WHERE
(INDEX_NAME like @index_name /* If matching name*/
or INDEX_NAME is null) /* If SQL_TABLE_STAT row*/
and (substring(INDEX_NAME,1,3)<>'P1C' or INDEX_NAME is NULL)
ORDER BY 4, 7, 6, 8
end
else
begin
SELECT
TABLE_QUALIFIER,
TABLE_OWNER,
TABLE_NAME,
NON_UNIQUE,
INDEX_QUALIFIER,
INDEX_NAME,
TYPE,
SEQ_IN_INDEX,
COLUMN_NAME,
COLLATION,
CARDINALITY,
PAGES,
FILTER_CONDITION = convert(varchar(128),null)
FROM #TmpIndex
WHERE
(INDEX_NAME like @index_name /* If matching name */
or INDEX_NAME is null)/* If SQL_TABLE_STAT row */
end

DROP TABLE #TmpIndex, #TmpColumns

GO

 

Компания "СофтПоинт"  предлагает вашему вниманию продукт, который позволит облегчить выполнение задачи оптимизации индексов. Кроме того, компания "СофтПоинт" имеет штат высококлассных специалистов, которые проводят работы, в том числе и по оптимизации индексов, которые могут качественно выполнить эту работу.

 


 

Перепечатка, воспроизведение в любой форме, распространение, в том числе в переводе, любых материалов с сайта www.softpoint.ru возможны только с письменного разрешения компании "СофтПоинт". Это правило действует для всех без исключения случаев, кроме тех, когда в материале прямо указано разрешение на копирование (основание: Закон Российской Федерации "Об авторском праве и смежных правах").