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 и таблицы ключ/значение лучше избегать.
Ссылка на проект.