unclejosef (unclejosef) wrote,
unclejosef
unclejosef

Category:

IT заметки. Методы хранения данных в реляционной базе данных.


При разработке реляционных баз данных встречаются ситуации, когда неизвестно количество полей записи или их слишком много и заполнены они не полностью. Лет 20 назад типовым вариантом было создать таблицу заголовков и таблицу, хранящую название или ID названия переменной и ее значение (далее ключ/значение). В современных базах появилась возможность хранения строк в XML и JSON. Я решил попробовать все четыре варианта хранения и сравнить их.

Параметры теста:

Источником данных послужила таблица со сгенерированными записями с 11 значащими полями: 5 числовых, 3 битовых, 3 строчных. В одном дополнительном поле лежало целочисленное ID типа, которое просто копировалось в соответствующее поле во всех вариантах хранения.  Первичный ключ типа BIGINT, генерировался с помощью IDENTITY.

Операции:

INSERT из таблицы источника (для варианта ключ/значение использовался курсор),
SELECT из полученной в таблицу со структурой, совпадающей с таблицей источником,
INSERT в таблицу с помощью курсора.

Варианты хранения:

Обычное хранение в полях,
JSON в строке NVARCHAR(2000),
XML используя тип XML,
Таблица ключ/значение (были добавлены вторичные ключи и некластеризованный индекс по ID величины и ID записи).

Количество данных в операции:

От 20 до 80 тыс. записей. С шагом в 20 тыс.

Задержка после каждой операции:

2 секунды.

Количество попыток:

6.

Программное обеспечение:

MS SQL Server 2017 Express, Windows 10.

Аппаратное обеспечение:

ASUS GL533VD Intel i7-7700HQ 2.8 GHz, 12 GByte RAM, 500 GByte, SSD Samsung 970 EVO PCI.


Анализ результатов:

После нескольких запусков теста было отмечено, что первая операция INSERT, шедшая после операций над обычной таблицей, с таблицами JSON или XML выполнялась значительно дольше остальных тестов. На попытках 2-6 такого не отмечалось. Поэтому из расчета первая попытка была исключена. Предполагаю, что увеличение первой операции INSERT для JSON или XML связано с загрузкой MS SQL Server дополнительных библиотек работы с XML и JSON.
При просмотре данных до усреднения было замечено, что MS SQL Server от попытки к попытке, которые выполнялись в цикле) увеличивает время исполнения оператора. Рост небольшой – доли процента, но надо знать, что такое возможно.


INSERT

График зависимости времени выполнения (мс) от размера пакета записей для оператора INSERT


Вариант хранения ключ/значение – худший вариант, хранение в полях таблицы – лучший вариант. Время обработки увеличивается более, чем в 80 раз, при увеличении записей время выполнения растет быстрее остальных вариантов.
Если выбирать XML или JSON, то JSON. Относительно стандартного метода хранения JSON будет только в два раза медленнее.


SELECT

График зависимости времени выполнения (мс) от размера пакета записей для оператора SELECT


Самый медленный SELECT… для XML. По быстроте выполнения JSON опять второй после стандартного хранения. Странно, но и при росте количества записей в операции время исполнения растет быстрее, чем для всех остальных операций.


CURSOR

График зависимости времени выполнения (мс) от размера пакета записей для оператора CURSOR


Не зря во всех учебниках не рекомендуют использовать курсоры. По сравнению с INSERT для обычной таблицы JSON и XML время выполнения увеличится от 8 до 30 раз. Но практика показывает, что замена INSERT курсором может сократить количество ошибок исполнения и времени исполнения при обращении к удаленному серверу. MS SQL Server любит загрузить с начала все данные по SELECT в память, а затем выполнить INSERT. Если это в одной базе, то получим кеширование на диск, если данные переносятся с другого сервера, то можно напороться на потерю данных и ошибку.
Что касается технологии хранения, то лучшая скорость выполнения операций у стандартной технологии хранения. Вторая по скорости - JSON, она хуже только на 5%.


Другие выявленные особенности:

Для JSON важен размер строки, в котором хранятся данные. Если размер NVARCHAR будет 2000 символов, то JSON в операции INSERT будет на 1,81 раза медленнее стандартного хранения, если ограничения нет, то уже 3,4.  Для операции SELECT в 6 и 12 раз соответственно. Для курсоров размер поля не играет практически никакого значения.
Относительная разница в скорости между технологиями слабо зависит от размера набора данных.

В сухом остатке – если есть возможность храните данные в полях, если нет – формат JSON. XML и таблицы ключ/значение лучше избегать.

Tags: it
Subscribe

Posts from This Journal “it” Tag

  • IT заметки. Загружаем OpenStreetMap в MS SQL Server.

    Проект OpenStreetMap (OSM) существует с 2004 года, но длительное время качество данных по России в нем было низким, что не позволяло применять их в…

  • IT Заметки. Цифровизируемся.

    Хочешь цифровизироваться? Как поступить? На самом деле вариантов не много: 1. Мечта бизнесмена. Купить коробочку, или лучше скачать что-то…

  • IT заметки. Иерархия.

    Иерархия встречается часто в реляционных базах данных. Структуры административного деления стран, организационная структура или документооборот по…

  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 0 comments