www.mamboteam.com
ДС бибилиотека  
Главная arrow Статьи по 1С arrow Управление индексами в 1С SQL
18.01.2018 г.
 
 
Главное меню
Главная
Каталог книг
Ссылки
Поиск
Правообладателям
Статьи по 1С
1С для начинающих
Rambler's Top100
Управление индексами в 1С SQL Печать E-mail
Рейтинг: / 0
ХудшаяЛучшая 
Автор Administrator   
07.10.2008 г.

1С SQL индексы

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

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

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

 
« Пред.   След. »
Новые поступления

1С:Предприятие 8. Руководство разработчика 8.2
 

1С:Предприятие 8. Руководство администратора 8.2
 

Интернет-Курс Расчет зарплаты и управление персоналом в 1С Предприятии 8.0
 

1С:Предприятие 7.7 Регламентированная отчетность 1С:Предприятие 7.7 Регламентированная отчетность за I квартал 2009 года
 

Документация по 1С 7.7
 

Самые скачиваемые
Документация по 1С 7.7 (66509)
1С:Предприятие 8. Руководство администратора 8.2 (10076)
Армагеддон (5507)
1С:Предприятие 7.7 Регламентированная отчетность 1С:Предприятие 7.7 Регламентированная отчетность за I квартал 2009 года (3708)
Интернет-Курс Расчет зарплаты и управление персоналом в 1С Предприятии 8.0 (3387)
Единая Справочно - Информационная Система 1С:Предприятие 8.0 (3041)
1С:Предприятие. Бухгалтерский учет. Секреты работы (2758)
1С Бухгалтерия. Пошаговые инструкции для начинающих (1928)
Профессиональная разработка в системе 1С:Предприятие 8 (1654)
1С:Предприятие 8.0. Полное описание функциональных возможностей и приемов работы (1640)
Rambler's Top100
 
Top! Top!