Каталог решений - Метод быстрой реструктуризации больших таблиц (10 миллионов записей и более) через SQL Server Managment Studio

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

Даже при не самом большом складском документообороте, при наличии сети магазинов тяжелые таблицы неизбежно возникнут в базе. Конечно в идеале мониторить такие таблицы и по возможности их подрезать, а что если в них содержаться движения и просто обрезка по дате не поможет?  Предложите делать свертку? Возможно, но на базе в 1 ТБ, это будет проблема.

Наиболее часто такие "пухляши" встречаются в регистрах сведений. При этом количество записей может достигать сотен миллионов строк (пример из практики 500 миллионов строк). Подрезка регистра с сохранением данных за последние 2 года, сокращает таблицу до 200 миллионов строк. Соответственно при попытке реструктуризации данной таблицы, платформа 1с попросить нас подождать год другой. А что если я скажу, что эту задачу можно выполнить за час из которых полчаса уйдет на кофе?  Да даже при наличии всего 1 миллиона строк в объекте, при реструктуризации Вы столкнетесь с теми же проблемами. Поэтому данный лайфхак пригодится каждому админу и разработчику.

А теперь готовый рецепт, лично проверенный на регистрах сведений, документах и справочниках:

Предупреждение: 

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

Данный метод отлично подходит для справочников, документов, регистров сведений (с отключенными итогами). На регистров накопления и регистрах бухгалтерии автором данный метод не проверялся.

Наличие бэкапа и прямых рук обязательно!

Шаг 1. Узнать внутреннее имя таблицы

С помощью любой обработки, позволяющей увидеть структуру хранения таблиц БД нужно узнать внутренне имя таблицы.
Если такой обработки нет, то можно просто выполнить следующий код:

  МассивИменМетаданных = Новый Массив();
  МассивИменМетаданных.Добавить("РегистрСведений.НаименованиеРегистраСведений");
  ТаблицаЗначенийПоля = ПолучитьСтруктуруХраненияБазыДанных(МассивИменМетаданных);
  

Далее в полученной таблице значений в колонке "Имя таблицы хранения" будет содержаться внутренне имя таблицы.
Например — InfoReg12870

2 шаг. Получить доступ на SQL Server

Если Вы не знаете на каком SQL сервере лежит Ваша база, нужно зайти в консоль кластера 1С. Зайти в свойства базы и посмотреть имя сервера в поле "Сервер базы данных".

3 Шаг. Подключиться к SQL серверу с помощью консоли SQL Server Managment Studio и найти нашу базу и таблицу.

Нужно попросить админов выдать вам доступ к серверу и доступ к самой БД. Далее открыть SQL Server Managment Studio и подключиться к серверу:

После этого вам нужно раскрыть вкладку databases, выбрать вашу БД, раскрыть вкладку Tables:

4 шаг. Создать пустую копию таблицы, с теми же полями

Находим нашу таблицу, выделяем ее, щелкаем правой кнопкой мыши выбираем пункт "Script table as" -> "Create to" -> "New Query"

В получившимся скрипте поправить имя создаваемой таблицы. Например просто добавив префикс "Copy".

  USE [NameBD]
  GO
  
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [dbo].[_InfoReg12870_Copy](
  	[_Fld25822RRef] [binary](16) NOT NULL,
  	[_Fld12871_TYPE] [binary](1) NOT NULL,
  	[_Fld12871_RTRef] [binary](4) NOT NULL,
  	[_Fld12871_RRRef] [binary](16) NOT NULL,
  	[_Fld12872RRef] [binary](16) NOT NULL,
  	[_Fld12873RRef] [binary](16) NOT NULL,
  	[_Fld12874] [binary](1) NOT NULL,
  	[_Fld12875] [numeric](10, 0) NOT NULL,
  	[_Fld12876] [numeric](10, 0) NOT NULL,
  	[_Fld12877] [numeric](10, 0) NOT NULL,
  	[_Fld19525] [binary](1) NOT NULL,
  	[_Fld19526] [datetime] NOT NULL,
  	[_Fld26557] [numeric](10, 0) NOT NULL,
  	[_Fld17329RRef] [binary](16) NOT NULL,
  	[_Fld17487] [datetime] NOT NULL,
  	[_SimpleKey] [binary](16) NOT NULL
  ) ON [PRIMARY]
  GO
  
  

После выполнения скрипта, вы получите копию своей таблицы.

5 шаг. Подмена таблиц

Старую таблицу с данными переименовать _InfoReg12870 в _InfoReg12870_Original, новую пустую переименовать из  _InfoReg12870_Copy в _InfoReg12870. Переименовать можно через ПКM в меню свойства самой таблицы.

Теперь платформа не увидит нашу огромную таблицу, но зато увидит новую пустую. Теперь нужно зайти в конфигуратор, добавить/удалить нужные реквизиты и выполнить реструктуризацию. На пустой таблице платформа выполнит реструктуризацию мгновенно. Осталось загрузить данные из старой таблицы в новую пустую.

6 шаг. Выгрузка данных из старой таблицы с данными в новую пустую

Выгрузку можно осуществить простым запросом INSERT INTO [dbtest].[dbo].[_InfoReg12870]  (список полей) SELECT  (список полей) FROM старая таблица. Важный нюанс для новых пустых колонок надо задать значение по умолчанию. Например для поля ссылочного типа это значение: 0x00000000000000000000000000000000, а для даты: ‘1753-01-01’

Пример скрипта:

  INSERT INTO [dbtest].[dbo].[_InfoReg12870] 
  (
         [_RecorderRRef]
        ,[_LineNo]
        ,[_Active]
        ,[_Fld18707_RRRef]
        ,[_Fld18706RRef]
        ,[_Fld18708]
        ,[_Fld18812]
        ,[_Fld18813RRef]
        ,[_Fld18709]
        ,[_Fld18814]
        ,[_SimpleKey]
        ,[_RecorderTRef]
        ,[_Fld18707_TYPE]
        ,[_Fld18707_RTRef]
  	  ,[_Fld28290RRef]	
  	  ,[_Fld28291RRef]	
  	  ,[_Fld28292RRef]	
  	  ,[_Fld28293]	
  )
  SELECT 
         [_RecorderRRef]
        ,[_LineNo]
        ,[_Active]
        ,[_Fld18707_RRRef]
        ,[_Fld18706RRef]
        ,[_Fld18708]
        ,[_Fld18812]
        ,[_Fld18813RRef]
        ,[_Fld18709]
        ,[_Fld18814]
        ,[_SimpleKey]
        ,[_RecorderTRef]
        ,[_Fld18707_TYPE]
        ,[_Fld18707_RTRef]
  	  ,0x00000000000000000000000000000000
  	  ,0x00000000000000000000000000000000
  	  ,0x00000000000000000000000000000000
  	  ,0
  FROM [dbtest].[dbo].[_InfoReg12870_Original]
  

Данная конструкция отработает максимально быстро. Например 15 млн строк скопировались за 3 минуты.

После выполнения скрипта зайти в базу и проверить, что все ок!

По итогу таким способом можно выполнять реструктуризацию любых таблиц любого объема, при этом платформа с новой таблицей будет работать как с родной. Т.к. в SQL базе получится 2 таблицы, старую с данными после всех проверок можно будет удалить, а можно оставить в качестве бэкапа.  Вроде все. Спасибо за внимание!