Microsoft sql server

... с объектной ориентированностью, сериализацией, reflection, полиморфизмом, визуальным программированием, no-code, блэкджеком и шлюхами - и это на MS SQL 6.5 1995 году!

Знакомые с историей IT при слове "однозвенка" вспомнят dBase и Clipper. Однако, я расскажу об ERP однозвенке. Интерфейсная программа для этой ERP общалась с базой через несколько интерфейсных таблиц и несколько процедур. То есть фактически она является браузером, который за слой не считается. Да, это #ненормальное программирование, которое дало ряд уникальных свойств.

Иногда ведь хочется ручку настройки повернуть до максимума и довести идею до абсурда логического завершения. Как говорили родители одному из героев фильма "О чем говорят мужчины, продолжение" - "Так ты дойдешь до черте-чего!", а он думал "Так уже хочется взять и дойти до этого черте-чего!".

Система называлась Ultimа-S, она же Nexus. Судьба ее была незавидна - эта ERP делалась на продажу, а продаж не было. Что, впрочем, было естественно - мы, в том числе автор этих строк, не имели ни малейшего понятия о маркетинге и продажах. Зато я имел удовольствие развлечься за счет работодателя. Итак,

Поехали

Берем установочные скрипты и устанавливаем систему на MS SQL 2019. Правда, базу пришлось загнать в compatibility mode. Запускаем exe. Он, кстати, крошечный (мне пришлось найти ntwdblib.dll):

и в памяти занимает примерно столько же, сколько Notepad:

Запускаем nexus.exe, и открываем иерархию классов:

При использовании возникает очень интересное чувство, которое я не испытывал со времен MS DOS. Многие операции отрабатывают мгновенно. Нет, реально мгновенно. В современных программах, даже WinForms (я уж не говорю про Web), код настолько тяжел, столько динамически отводит памяти и столько раз вызывает GC, что глаз замечает видимые задержки. Мы к ним привыкли и не замечаем. Но когда программа реально рисует окно после нажатия на клавишу за время одного фрейма экрана, то это удивляет. А когда-то, во времена MS DOS, это было нормально.

Как это работает?

Вся коммуникация с сервером происходит через несколько процедур и три (с небольшим) таблицы. Хочется раскрыть папку - вызываем процедуру, передавая id папки, а в результирующую таблицу сваливается список документов, которые там находятся. Причем клиента не интересует, как этот список получился.

Делаем right click и хотим получить список свойств?

Тоже через таблицу. Пользователь видит user friendly имена свойств, а еще у них есть системные id, среди которых - как правило 'view' (read only просмотр по умолчанию) и 'edit' (основное редактирование).

Наконец, мы выбрали свойство и хотим посмотреть документ. Результат выдается через таблицу Detailed где есть id полей, значения (есть колонки для разных типов) и колонку форматирования. Это поле в первой букве содержит тип значения int, float, money, string, document (который на самом деле тоже int), а дальше название для человека и некие теги указывающие поведение, например: fширина^min=0^max=10.0

Вас конечно заинтересовало, если таблицы для коммуникации общие, то как могут одновременно работать много клиентов? Все очень просто - у всех этих таблиц есть поле spid (=@@spid). А все клиенты открывают ровно одну коннекцию и используют только ее (да, такой дизайн был нормой). Никаких connection pools. Представляете как легко сделать пессимистичные блокировки - по spid вы точно знаете что клиент не отсох и даже с какого hostnamе он работает!

Обычно клиент располагает поля сверху вниз, но для важных документов могут быть задизайнены красивые формы. И да, теперь языком дизайна был бы наверняка HTML, а языком сценариев (min^ max^ и полее сложные пересчетом totals) был бы JS.

Объектная ориентированность

Все документы в системе произведены от общего класса Doc. При создании производных классов создаются документы типа Класс, осуществляя своеобразный Reflection. Часть иерархии классов вы могли видеть на первом скриншоте. Классы могут быть и абстрактными.

При выполнении операции (раскрытия папки, получения свойств и отображения) вызываются процедуры с именами вида Class_operation_ID_, причем вначале идет волна 'pre' свойств, начиная от Doc к листьевому классу, а потом 'post', в обратном направлении, от листа к корню. Как правило, все свойства pre, а post полезен для удаления данных (удаления лучше делать обратном порядке, от detail таблиц к master).

Для pre свойство очередного класса 'видит' что натворили классы под ним, поэтому может не просто добавить поле или свойство, но и 'надругаться' над тем, что сделано до него - переименовать свойство или вообще убрать его, сделать поля read-only, дать им умолчания или вообще скрыть поля с помощью специального флага (но не удалять - иначе будут проблемы при записи)

Ниже вы видите пример процедур на SQL, к которому был прикручен самописный макроязык. Обратите внимание как производный класс изменяет friendly name свойства пользователя, а также иногда добавляет новое свойство.

Работа через Detailed давала еще одну интересную возможность: сериализация. Ведь можно просто вызывать свойство view (например), получить ответ, и запомнить его. Получалась мертвая копия, ксерокс, но выглядела она как настоящий документ - только была read-only.

Аналогично, Аудит (история изменений) работала на уровне корневого класса Doc, сравнивая Detailed до и после редактирования. Все производные классы были освобождены от необходимости следить за аудитом редактирования!

No-code

Система позволяла мастерить новые документы прямо через интерфейс. Сейчас покажу пример. Создадим класс, производный от пользователя:

В диалоге создания класса заполним необходимые поля. Как видите, я лишь прохожусь по самым верхам - в системе много что было еще.

Теперь через right click на образовавшемся классе создадим расширение, то есть новое поле - число пальцев от 5 до 10.

Теперь мы создаем сотрудника, указав число пальцев 5, и записываем его.

Посмотрим на результат. Новое поле добавилось внизу:

Сравним обычного пользователя и VIP пользователя. Благодаря полиморфизму (в одной папке могут быть документы любого типа) мы можем положить в папку и обычного пользователя и VIP:

С помощью No-code можно было создать сложные документы, с таблицами в духе товар-количество-цена и полем общая сумма, которая пересчитывалась автоматически - и это все без единой строчки кода. Данные ('расширения'), правда, хранились в универсальной таблице, что плохо с точки зрения производительности. Поэтому такие вещи предназначались для маленьких дополнений, а основной код все-таки писался на TSQL

Вершиной no-code был user assistant - метод переопределения поведения любых полей.

Администратор мог для любого документа, для любого поля, для определенной группы пользователей добавить умолчание, сделать поле read-only (что вместе с умолчанием заставляет использовать только определенное значение), или спрятать поле вовсе. Для полей выбора документов можно было изменить Root (папку начиная с которой выбираются документы для ввода) - например, пусть Вася Пупкин при создании платежки сможет указывать только этих трех клиентов (накидаем ссылки на этих клиентов в отдельную папочку)

Фильтрация

В системе были как обычный папки, похожие на папки для файлов, куда можно было перетаскивать документы мышкой, так и пользовательские фильтры:

Через систему событий опрашивались все классы - а по каким полям ты умеешь фильтроваться?

Далее из этих кирпичиков пользователь мог собрать любой фильтр. Конечно, в такую папку документы перетаскивать было запрещено.

Заключение

С тех пор я не видел потенциально более гибких систем. А монстры типа SAP R/3 живут и процветают...

В преддверии старта курса "Инфраструктурная платформа на основе Kubernetes" приглашаем всех желающих на бесплатный демо-урок, в рамках которого одним глазком посмотрим на устройство kubernetes, немного поговорим о том, как взаимодействуют компоненты, разберем основные подходы к обеспечению безопасности кластера, поговорим об ограничениях ресурсов, сетевых политиках, привилегиях запуска и т. д.

Реализуем доставку кастомных миграций баз данных с помощью файлов манифестов сценариев

Управление миграциями баз данных для нескольких сред и команд может быть достаточно сложной задачей. В этой статье описывается, как сочетание Git, контейнеров и клонов баз данных используется для реализации доставки в среды разработки, тестирования и стейджинга за считанные секунды.

Хотя Git и так часто используется в сочетании с контейнерами баз данных, описанный здесь подход все же вводит два новых элемента. Вместо того, чтобы воспроизводить базы данных из бекапов или создавать из источника данных, мы клонируем идентичные безопасные среды производственных баз данных и доставляем их в течении секунд. Клоны баз данных доступны для записи и позволяют легко внедрять маскирование данных и синтетические тестовые данные. Второй элемент - это файл манифеста сценариев, используемый при создании и применении персонализированных сценариев миграции.

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

Эта статья берет за основу SQL Server, но эти методы также поддерживаются Postgres и MySQL.

Компоненты

Можно использовать любой публичный или приватный репозиторий Git, включая GitHub, GitLab или Git на частной виртуальной машине.

Образ базы данных - это набор производственных баз данных, воссозданных из бекапов или файлов баз данных с применением сценариев маскирования данных. Для поддержки конкретных сред разработки, тестирования и стейджинга пользователи могут указывать сценарии из веток и репозиториев Git. Когда у вас есть резервные копии лога транзакций, образ можно обновлять инкрементно.

Файл манифеста сценариев - это текстовый файл со списком запускаемых сценариев.

Контейнер базы данных - это инстанс базы данных, предоставляющий сервисы для работы с базой данных.

Клон баз данных - это база данных с возможностью записи, создаваемая из образа, которая доставляется за секунды и требует для этого всего 40 МБ дискового пространства.

Создание образа базы данных

Dockerfile включает еще один путь к бэкапам производственных баз данных и сценариям маскирования данных. Во время выполнения репозиторий Git клонируется в файловую систему контейнера со специальным чекаутом ветки. Также во время выполнения пользователи или конвейер предоставляют две переменные среды, выделенные красным, указывая ветку Git и файл манифеста сценариев (подробнее об этом ниже). Последний шаг включает команду PowerShell, которая создает объединенный сценарий all.sql, отражающий порядок сценариев в файле манифеста, который затем и запускается.

Образ создается с помощью стандартной docker-команды:

>docker build -t microservice1 c:\path\to\dockerfile

Манифестные файлы

Манифест - это текстовый файл, в котором перечислены пути к сценариям относительно корня репозитория Git в том порядке, в котором они должны выполняться. Можно использовать несколько манифестных файлов. Один манифест может перечислять сценарии обновления, а второй включать сценарии обновления, за которыми следуют сценарии отката. Файл manifest.txt может включать:

Среды разработки и тестирования

Разработка начинается с доставки контейнера SQL Server с клоном производственной базы данных, но без применения гитовых сценариев. По мере выполнения работы сценарии фиксируются в репозитории, создается манифестный файл и фиксируется список сценариев в том порядке, в котором они должны выполняться.

Пользователи и конвейеры предоставляют среды с помощью docker-команд или restful API. Вывод лога сценариев доступен с помощью Rest API. Среды используются для модульного тестирования SQL скриптов и приложений с определенными файлами манифеста и ветками Git. Доставка включает вводимые пользователем данные, выделенные красным.

Тестирование релизных веток

По мере выполнения задач рабочие ветки мержатся в релизную ветку с обновленными манифестными файлами. Теперь тестировщики и DevOps конвейеры могут доставлять среды тестирования релизов с использованием одного и того же образа базы данных, ссылаясь на релизную ветку и обновленный файл манифеста. Миграция базы данных с помощью Git поддерживает полный жизненный цикл разработки/тестирования с согласованной средой баз данных.

Управление данными и безопасность

Этот подход не только упрощает и автоматизирует разработку и тестирование баз данных, он также создает безопасное централизованное хранилище данных. Там, где организации встряют в борьбу с разрастанием виртуальных машин и инстансов, контейнеры позволяют консолидироваться на контейнерном хосте. Один образ базы данных легко поддерживает одновременно от 20 до 50 сред, что потенциально снижает объем хранилища до 95%.

Записаться на бесплатный демо-урок.

В процессе подготовки инструмента для автоматического определения субъекта РФ по точке (тип данных Point) потребовалась таблица вида "Субъект РФ" - "geography::Object".

Предыстория: есть большой автопарк (>1000 ТС), который отправляет свои координаты на сервер в составе данных "Машина" - "Момент времени UTC" - "geography::Point". На сервере есть вторая таблица определенных событий по транспортному средству в составе данных "Машина" - "Момент времени (местное время)" - "Событие". Две задачи - перевести время во второй таблице из местного в UTC и далее использовать обе таблицы для дальнейшей автоматизации аналитики по событиям ТС в привязке к субъектам РФ.

Поиск в гугле по фразе "geojson субъекты РФ" привел на страницу https://gist.github.com/max107/6571147 - на ней в формате JSON перечислены субъекты и списки точек координат - границ.

Если вы пробежитесь по тексту, то структура этого JSON такая: на верхнем уровне один блок - один субъект. На следующем уровне вниз - блоки с нумерацией от 0 до, кажется, 19. Это означает, что субъект состоит из нескольких областей и каждая из них - это отдельный полигон (многоугольник). В файле нет Крыма, Севастополя. В файле не выделены Москва и СПб. Крым я дорисую сам, а Мск и СПб для моих задач не принципиальны.

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

Скорее всего есть более простые способы решения этой задачи, но, решая её поэтапно, удалось более детально разобраться в структуре объектов, попробовать методы работы с пространственными данными.

Скопировал текст опубликованного JSON в текстовый файл в блокнот, выбрал кодировку ANSI, сохранил в файл координаты_субъектов_РФ_json.txt

Далее использовал Python и SQL Server Express в двух средах программирования, например (не реклама, это просто одни из множества): PyCharm Community Edition и SSMS. В SSMS на сервере заведем таблицу-приёмник, а в Python разберем текст из файла с JSON, сформируем в цикле текстовую строку для каждого субъекта и забросим на сервер.

Как указал выше: по структуре JSON понимаем, что некоторые из субъектов состоят из нескольких многоугольных областей. Одна область (не субъект, а блок внутри субъекта) соответствует типу пространственных данных Polygon. А несколько связанных областей, поименованных одним названием субъекта - MultiPolygon.

Сконструировать и записать в таблицу объект типа Polygon или Multipoligon можно методами STPolyFromText и STMPolyFromText, которые принимают два аргумента - текст с описанием фигуры и SRID - параметр, который говорит о выбранной системе измерений и координат. Он должен быть в создаваемом объекте таким же, как и в предмете будущего сравнения или иной связанной обработки (в моем случае это таблица с данными в формате geography::Point, наполняемой системой съема GPS-координат транспортного средства). Номер SRID можно получить методом .STSrid. У меня получилось 4326. Значит и субъекты РФ будут сконструированы с этим SRID.

Поскольку областей в рамках одного субъекта может быть много, все их я решил признать мультиполигонами и конструировать инструкцией ... = geography::STMPolyFromText('text', 4326).

Текстовая строка для этого метода должна быть такой: 'MULTIPOLYGON(((список точек границ 1 полигона)), ((список точек границ 2 полигона)), ... , ((список точек границ последнего полигона)))'

Точки указываются парой в формате "долгота пробел широта", список перечисляется через запятую.

Создаем на сервере в БД таблицу-приемник.

CREATE TABLE [dbo].[geozones_RF_subj]([Subj_name] [nvarchar](250) NULL,[Polygon_geo] [geography] NULL,[List_of_coords] [nvarchar](max) NULL,) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO

# импортируем модули для работы с JSON и с SQL-serverimport jsonimport pyodbc#подключаемся к базе данных на сервере#используйте название своего сервера и БД#драйвер может отличаться, если версия сервера старшеcnxn = pyodbc.connect(    r'DRIVER={ODBC Driver 17 for SQL Server};'    r'SERVER=LAPTOP-7TOVC7TC\SQLEXPRESS_AMO83;'    r'DATABASE=sandbox;trusted_connection=yes')#объявляем курсорcursor = cnxn.cursor()#открываем файл и забираем содержимое в переменную datawith open(r"D:\координаты_субъектов_РФ_json.txt", "r") as read_file:    data = json.load(read_file)    #переменная data приняла словарь субъектов из вложенных словарей - полигонов,#вложеные словари - списки пар координат границ#цикл перебора словаря субъектовfor i in data.keys():    str_: str = 'MULTIPOLYGON('  # пишем в переменную стартовое слово          # вложенный цикл перебора словарей - полигонов    for j in data[i].keys():        str_ = str_ + '(('  # описание полигона начинается двумя скобками                # вложенный цикл перебора списков - пар координат точек границ полигона        for k in range(len(data[i][j])):            lat_ = str(data[i][j][k][1])  # широта указана на втором месте в паре            lon_ = str(data[i][j][k][0])  # долгота указана на первом месте в паре                    # описание полигона должно заканчиваться одинаковой парой координат        # в файле это не так, поэтому сохраним первую пару и потом добавим        # эту пару как завершающую перед закрытием полигона        if k == 0:            lat_beg = lat_            lon_beg = lon_                    # добавляем к строке пару координат и запятую на случай начала описания        if str_[-2:] == '((':            str_ = str_ + lat_ + ' ' + lon_ + ', '        # добавляем к строке пару координат        # и завершающую пару координат если это окончание описания полигона        if k == len(data[i][j]) - 1:            str_ = str_ + lat_ + ' ' + lon_ + ', ' + lat_beg + ' ' + lon_beg + '))'        # добавляем к строке пару координат и запятую, если это не начало        # и не конец описания, можно соединить с первым IF и вписать в блок else        # второго IF, но так детальнее        if str_[-2:] != '((' and k != len(data[i][j]) - 1:            str_ = str_ + lat_ + ' ' + lon_ + ', '    # если это не последний полигон субъекта - ставим запятую    if int(j) < (len(data[i]) - 1):        str_ = str_ + ', '# завершаем скобкой мультиполигон (субъект РФ)str_ = str_ + ')'# пишем SQL-инструкцию для добавления строки в таблицу,# добавляем только название субъекта в первый стобец# и полученную текстовую строку описания - в третий столбецcomm: str = 'INSERT INTO sandbox.dbo.geozones_RF_subj VALUES(' + \            "'" + i + "'" + ', NULL, ' + "'" + str_ + "'" + ')'# запускаем SQL-инструкцию на сервереcursor.execute(comm)#  записываем изменение таблицы (новую добавленную строку)cnxn.commit()#закрываем соединение с серверомcnxn.close()

В итоге получаем таблицу - заготовку для конструирования соответствующего субъекту РФ значения в формате Geography

Заполним значениями столбец Polygon_geo предварительными значениями

UPDATE [sandbox].[dbo].[geozones_RF_subj] SET Polygon_geo = geography::STMPolyFromText(List_of_coords, 4326)

Некоторые субъекты получились инвертированными - т.е. они состоят из всего земного шара, за исключением самого субъекта РФ. Также некоторые субъекты получились некорректными. Проверяется методом .STIsValid()

Инвертированный объект видно на вкладке Spatial results - он будет белым, а вся остальная область - цветная.

Для того, чтобы исправить обе проблемы: "неправильность" и инвертированность - сначала применим метод MakeValid() и перезапишем столбец Polygon_geo

UPDATE [sandbox].[dbo].[geozones_RF_subj] SET Polygon_geo=Polygon_geo.MakeValid()

Затем инвертируем те значения, площадь которых более 500 млн. км2

UPDATE [sandbox].[dbo].[geozones_RF_subj] SET Polygon_geo=Polygon_geo.ReorientObject() where Polygon_geo.STArea()/1000000>500000000

Результат: таблица субъектов РФ в формате geography::MiltiPolygon, которая готова служить для определения наименования субъекта РФ и часового пояса по координатам геопозиции объекта.

В таблице нет Крыма и Севастополя, не выделены Москва и СПб. Также некоторые границы субъектов немного друг друга пересекают или между ними есть небольшие "щели" пустоты. Это не очень критично для моей задачи и при необходимости можно убрать путем уточнения координат и повторного конструирования значения формата Geography.

Исходные данные:

1. Два SQL Server'а, которые находятся в прямой доступности между собой, на одном из которых настроен Linked Server.

2. SQL запрос вида:

insert into LocalDatabaseName.dbo.TableName (column1, column2, ..., columnN)select column1, column2, ..., columnNfrom LinkedServerName.RemoteDatabaseName.dbo.TableName

Задача: максимально быстро скопировать записи с одного сервера на другой

Столкнулся с тем, что подобный запрос выполняется на 40k (40000) записей больше минуты. С ростом количества подобных запросов или количества записей, производительность сильно падает и оптимизировать запрос средствами SQL никак нельзя. С использованием приложения ImportExportDataSql мне удалось ускорить этот запрос до 2 секунд, не используя Linked Server.

Приложение ImportExportDataSql создавал для себя и постоянно его дорабатывал на протяжении нескольких лет. Основные требования при создании приложения - портативность, работа под всеми версиями Windows без установки сторонних библиотек (кроме NET Framework 3.5), простой интерфейс и высокая производительность.

ImportExportDataSql - универсальный конвертер данных, как альтернатива "bcp"

При работе с данными очень часто требуется загружать файлы из разных файлов в БД (чаще всего CSV и Excel) и обратно (из БД в CSV). До этого пользовался утилитой bcp, но всегда не хватало графического интерфейса. Кроме этого у "bcp", есть недостатки, описанные в моей предыдущей статье.

В ImportExportDataSql кроме графического интерфейса, реализована возможность работы через командную строку. Пример командной строки:

ImportExportDataSql.exe -ConnectionName="Имя соединения с БД" -TaskName="Имя Задачи 1" -TaskName="Имя задачи 2" [-Log="C:\FolderName\LogFileName.log"]

Параметры командной строки:

-ConnectionName - Имя соединения с БД, которое должно быть сохранено на форме "Соединение с БД" по кнопке "Сохранить настройку соединения с БД"

Сохранить настройку соединения с БД

-TaskName - Имя задачи из пользовательского списка задач

-Log - имя лог файла. Необязательный параметр. По-умолчанию, используется лог файл в папке Logs\UserName\ImportExportDataSql.log

Список решаемых задач в ImportExportDataSql

1. Сохранить из БД в файл - если файлы хранятся в БД и их нужно сохранить на диск

2. Сохранить из БД в файл (утилитой bcp) - если файлы хранятся в БД и их нужно сохранить на диск с помощью утилиты bcp (создается bat файл)

3. Сохранить из файла в БД - если нужно загрузить файлы с диска в таблицу БД с полем типа varbinary

4. Сохранить из БД в скрипт SQL - сохраняет результат SELECT запроса в SQL файл

5. Из БД в скрипт SQL (только INSERT)

6. Из БД в скрипт SQL (только UPDATE)

7. Статический скрипт SQL

8. Сохранить из Excel в скрипт SQL

9. Сохранить из БД в CSV

10. Сохранить из CSV в SQL

11. Сохранить из CSV в БД

12. Сохранить конфигурацию БД в SQL - выгружает структуру БД в SQL файл

13. Сохранить из БД в БД - сохраняет результат SELECT запроса на другой или текущий сервер

Все типы обработки, которые заканчиваются словом SQL могут объединяться в один файл, если имя файла одинаковое в нескольких задачах. Это очень удобно для копирования данных из одной БД в другую (например, при переносе данных с прода на тест или наоборот).

Сохранить из БД в БД

Использование данного способа позволило оптимизировать запрос (приведенный в начале статьи) копирования данных через Linked Server, сократив время выполнения с 1 минуты до 2 секунд. Алгоритм копирования данных из одной БД в другую выполнен стандартными классами языка C# из пространства имен System.Data.SqlClient: SqlConnection, SqlDataReader, SqlCommand и SqlBulkCopy.

Чтобы не возникало ошибки нехватки памяти OutOfMemoryException, чтение и запись данных выполняется блоками (частями). Блок ограничивается максимальным количеством записей, который определяется пользователем. Параметры, которые задает пользователь:

1. SQL запрос - выполняется на БД источнике, с которой нужно копировать информацию

2. Настройки выгрузки в БД назначения:

   Имя соединения - выбирается из списка соединений, которые пользователь сохраняет на форме "Соединение с БД", отображаемая при запуске приложения. Точка (.) в параметре "Имя соединения" означает, что используется текущее соединение с БД.

   Имя таблицы - в которую нужно копировать записи. Таблицу можно выбирать из списка, либо указать вручную (может содержать не только имя схемы и имя таблицы, но и имя БД)

   Номер последней обрабатываемой строки - служит для ограничения количества копируемых строк, и применяется для отладки. Например, если запрос возвращает 100 записей, а "Номер последней обрабатываемой строки" = 10, то будет скопировано только 10 первых строк из результата запроса.

   Количество строк в блоке - количество строк сохраняемых одной транзакцией

Способом "Сохранить из БД в БД" я также пользуюсь, когда необходимо скопировать результат запроса с большим количеством записей. Ограничение количества записей, в этом случае, дает преимущество, перед обычным запросом копирования записей (insert into ... select ...), так как снижается нагрузка на диск, не сильно растет журнал транзакций и не используется база tempdb (если "Количество строк в блоке" оптимальное).

Преимущества и применение ImportExportDataSql

Приложение ImportExportDataSql постоянно помогает мне в работе. С помощью него удобно переносить данные из одной БД в другую.

В коде встроено множество проверок, чтобы достаточно быстро можно было понимать на какой строке возникла ошибка при импорте CSV файла или Excel.

Можно загружать большие CSV файлы (больше 1Гб) и добавлять свои поля, которых нет в CSV. Отсекать ненужные поля из CSV, не загружая их в БД.

Скрипты при выгрузке в SQL формат дополнены различными проверками, чтобы при выполнении скрипта на другой базе все ошибки отображались в одной таблице, а не списком ошибок на панеле "Messages" в SQL Server Management Studio.

С помощью типа обработки "Сохранить конфигурацию БД в SQL" и командной строки я автоматизировал создание резервных копий джобов (jobs), репликаций и других объектов БД, чего нельзя сделать стандартными способами.

Заключение

Используя язык C# и класс SqlBulkCopy можно существенно сократить время выполнения запроса, в котором используется Linked Server.

Ссылки

Скачать ImportExportDataSql

Статья с подробным описанием ImportExportDataSql

Статья "Быстрое чтение CSV в C#", в которой рассказывается о недостатках "bcp"

Сообщество VK, для желающих пообщаться с автором

Так случилось, что продукт, который мы разрабатываем работает с несколькими реляционными базами данных. Сейчас это MS SQL, Postgres и Oracle. Были запуски под много чем от MySQL до покойного, наверное, Firebird и экзотических Sybase с DB2, но сказ не об этом.

Если с MS SQL и Postgres все более мене понятное-привычное, то с Oracle каждый раз нас ждут какие-то сюрпризы. Проницательный читатель сразу заметит, что "руки у нас кривые" и мы "попросту не умеем его готовить", но если, уважаемому читателю захочется узнать чем varchar (а точнее varchar2) в Богоподобном Oracle отличается от его собратьев, то прошу под кат.

Как все современные системы, мы храним данные в Unicode формате (в данный момент это UTF-8). Почему это может быть важно для реляционных баз данных?

Ну, например, если у вас в базе данных mix unicode и non-unicode типов данных, то некоторые драйвера в такое не могут. Например, JTDS - JDBC драйвер для MS SQL сервера может работать либо в Unicode режиме, либо в Ansi. Соответственно, если Вы решите "сэкономить" и создать не unicode колонку (varchar/char), то получите преобразование unicode->ansi на уровне вставки данных в таблицу и, скорее всего, достигните обратного эффекта (как минимум замедления на вставке данных, а то и на поиске).

Итак, история. Наш сервер приложений проверяет максимальную допустимую длину полей до их вставки (здесь нужно оговориться, что проверка выполняется не по данным БД, а по нашим внутренним метаданным), но несмотря на это иногда под Oracle мы "ловим" ошибку вида ORA-12899: value too large for column.

Что за напасать? Причем, скрипты генерируются примерно одним и тем же способом под все базы данных, но проблема возникает только иногда и только под Oracle.

Не буду томить. Оказалось, что мы невнимательно прочитали спецификацию типа varchar2 в котором хранятся данные :)

Давайте изменим размер колонки, например, на следующий

alter table address modify street varchar2(150);

Как Вы думаете 150 - это длина в символах (как в других базах в общем-то)? Подсказка - нет :) Скорее всего в байтах.

А в символах это

alter table address modify street varchar2(150 char);

Т.е. не указывая спецификацию char-byte мы оказываемся в серой зоне настроек базы данных по умолчанию. Причем во всех базах до которых мы смогли дотянуться (включая продакшн и не только наши)настройка по умолчанию - это байты.

А теперь давайте вспомним, что в UTF-8, например, один символ может занимать от одного до 4 байт (обычно 1 байт ANSI, 2 русские символы и некоторые которым больше повезло и до 4 для иероглифов).

И что это за дикая настройка по умолчанию для Unicode баз!? Но ведь, именно она, зараза такая, включена "из коробки". Ну т.е. да, я все понимаю: legacy, обратная совместимость для тех времен, когда Unicode'а еще и "в проекте не было, гордость за то, что backup 86 года можно восстановить последней редакией imp - вот это вот все.

А почему ошибка возникала только иногда и только для некоторых колонок? Так как тот tool, которым мы генерируем базу изначально был настолько умным, что сразу в create table для всех колонок явно прописывал суффикс char :)

Выводы:

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

Скрипт для определения значения по умолчанию

SELECT value FROM NLSDATABASEPARAMETERS WHERE parameter='NLSLENGTHSEMANTICS';

Скрипт, который позволяет проверить, что у вас в базе "все ОК":

SELECT TABLE_NAME, COLUMN_NAME, DATA_LENGTH, CHAR_USED FROM USER_TAB_COLUMNS WHERE DATA_TYPE = 'VARCHAR2' AND CHAR_USED = 'B'ORDER BY TABLE_NAME, COLUMN_NAME

P.S. Сразу оговорюсь, это нормально, если там где Вы это ожидаете размерность в байтах (например, там где 100% ansi символы), но вот для Unciode текста Ушел плакать дальше на эту тему ...

P.P.S. Regexp которым можно попробовать найти скрипты "серой зоны" varchar2(\s\d+\s)

P.P.P.S. Поиск ответа на этот вопрос с помощьюStackOverflow

P.P.P.P.S.Авот,чтодумаетOracleпоповодуизменениязначения параметраNLSLENGTHSEMANTICSначто-тоболееразумное"Oracle strongly recommends that you do NOT set the NLSLENGTHSEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows." https://docs.oracle.com/cd/E2469301/server.11203/e24448/initparams149.htm

Эта статья не является ответом на множество вопросов по базам данных (БД) и системам управлениям базами данных (СУБД). Я как автор выражаю своё собственное мнение о трендах, стараясь опираться на беспристрастные показатели, статистики и т.д., но для примера приводя собственный опыт. Я не являюсь ангажированным представителем какой-либо компании и выражаю точку зрения опираясь на опыт более 25 лет работы с разными СУБД, в том числе, которую создавал своими руками. Не так много даже опытных программистов и архитекторов, которые знают все термины, технологии, какие подводные камни и куда идёт движение. Тема поистине огромная, поэтому в рамках одной статьи не раскрыть даже верхний уровень информации. Если кто-то не встретит свою любимую СУБД или её невероятный плюс, который стоит упомянуть, то прошу в комментариях указать и этим дополнить общую картину, что поможет другим разобраться и понять лучше предметную область. Поехали!

Open Source DBMS vs Commercial DBMS

Для начала приведу график с сайта, db-engines.com, по моим ощущениям, неплохо отслеживающим тренды БД. Именно этот график добавил желания написать статью о текущем положении дел. Когда мы говорим фразу база данных, то на самом деле чаще имеем в виду конкретную систему управления базами данных (СУБД), поэтому если в тексте встретится БД вместо СУБД, то это в силу такой привычки.

Open Source DBMS системы управления баз данных с открытым исходным кодом догнали коммерческие СУБД с закрытым исходным кодом. Open Source 49.98% против 50.02% у Commercial. Итогом 2020 года становится момент, когда можно будет сказать, что open source не менее популярны. Как вы видите, эта ситуация возникла не внезапно. Подсчёт на графике не в численном соотношении, а в очках, которые набирают те или иные системы.

Для интереса можете посмотреть на сайте где находится ваша любимая СУБД в рейтинге. Итогом последнего года стал вылет Microsoft Access из десятки, он вместе с языком программирования COBOL напоминает, что жизненный цикл технологий может быть очень длинным. Полагаю, что в следующем году IBM DB2 будет опускаться сильнее всего в топе. Топ 10 СУБД это 75% всех набранных баллов. За год топ 10 в очках почти не поменялся.

Open Source вырос качественно за последние годы и всё больше ИТ специалистов, принимающих решение задаются вопросом, а стоят ли лицензии Oracle, MS SQL, IBM DB2 и прочих коммерческих продуктов, чтобы в них вкладываться. Не в малой степени этому способствуют аппетиты одноимённых компаний. В последние годы стало модно в коммерческих продуктах продавать enterprise licenses (лицензии уровня предприятий без искусственных ограничений функционала) за ядра процессоров. Можете по ссылке посчитать, что выходит для сервера с 4 процессорами по 16 ядер - всего 64 ядра если вам не подходят лицензии за пользователей.

MS SQL - 439 936$

Oracle - 1 368 000$

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

Прошлый 2019 год и текущий 2020 проходили с растущим влиянием компании AMD и её центральных процессоров, включая серверные EPYC, которые кардинально меняют стоимость физических ядер, а это значит, что ядер будут покупать всё больше. Многоядерность AMD развилась с внедрением чиплетов и теперь 64 ядра в одном процессоре - это реальность. Готовы ли будут компании покупать платные лицензии платных СУБД на ядра в разы больше? Не уверен. Физический сервер будет получаться по стоимости на порядок ниже лицензий. Серверный рынок довольно инертный и на AMD не перейдут и за несколько лет, но Oracle, Microsoft и прочие начнут терять долю ещё быстрее если не изменят политику. Microsoft выпускал версию SQL Server для Linux, но опять же за деньги и не нашлось большого числа желающих её купить. IBM DB2 теряет долю рынка уже давно.

Переход многих компаний на микросервисы (наверняка слышали на российских конференциях и форумах мы пилим монолит на микросервисы) зачастую приводит к отделению данных физически на разные сервера, для коммерческих СУБД это платные лицензии, для Open Source нет. Чтобы не выйти из бюджета выбирают бесплатные решения.

Уместно будет сказать, что бизнес коммерческих СУБД выгоден и ими владеют богатейшие люди планеты. В топе самых богатых людей оказываются владельцы компаний, владеющих коммерческими СУБД: Билл Гейтс (Microsoft), Ларри Эллисон (Oracle), которые занимают значительную долю в топе СУБД. Ещё из топ 10 списка Forbes богатейших людей, у которых есть огромные или облачные БД присутствуют Джефф Безос (Amazon RDS), Ларри Пейдж и Сергей Брин (Google с их Bigtable).

В последнее время крупные ИТ компании с большими деньгами развивают open source проекты. Можно было бы подумать, что они прониклись идеями сообщества открытого кода, хотят сделать полезное для общества, их наполнило человеколюбие и прочее меценатство. Но нет, нужно понимать, что крупные акулы в бизнесе рассчитывают на некотором этапе монетизировать эти вложения, повысить положительное мнение о своих компаниях, занять рынки, воспользоваться бесплатным трудом сообщества, взять выстрелившие идеи. Шутка ли тысячи светлых голов коммитят в репозитории качественный код, тестируют на реальных проектах и денег не просят.

Тот же MySQL несмотря на название open source был уже продан за 1 млрд.$, Sun Microsystem, а потом поглощён Oracle. Основатель MySQL Майкл Видениус переоткрыл проект назвав его MariaDB. Я был на его выступлении в Москве где он призывал переходить на их продолжение оригинальной СУБД и надо сказать, что немалая часть разработчиков так и поступило. MariaDB уже на 12 месте.

Коммерческие СУБД дороги, но есть и масса плюсов, они стабильны, удобны, легко интегрируемы в ИТ инфраструктуру, есть система подготовки специалистов, сторонние компании расширяют функционал. Плюсом open source помимо бесплатности и растущей части возможностей платных СУБД можно считать и то, что вы можете взять код БД и поменять под свои нужды, как делал Facebook c MySQL. Для одной СУБД open source может быть несколько движков или несколько веток развития и вы можете выбирать любой вариант, который вам подходит. Влетевшая в топ 10 SQLite - мультиплатформенный продукт. Это персональная БД не использующая парадигму Сервер-Клиент, когда вам нужно с минимальными затратами просто локально хранить данные для приложения и пользоваться всеми плюсами СУБД.

Что в России: Нужно понимать, что когда в 70-80 годах году появились первые коммерческие продукты, например Oracle, у нас ещё был Брежнев, Олимпиада в Москве, перестройка, отставание в электронной промышленности и т.д. Мы пока догоняем или развиваем существующие системы.

Первые версии СУБД в мире были коммерческие, распространялись точечно, требовали присутствия специалистов. Покупать программное обеспечение (ПО) за валюту в нашей стране исторически не всегда принято (опустим обсуждение политических и экономических мотивов), поэтому альтернатива в виде бесплатных СУБД и пиратских версий коммерческих продуктов присутствуют. В институтах сейчас часто учат на бесплатных движках БД, открытое ПО это стильно, модно, молодёжно. Поэтому рынок очень быстро наполняется специалистами в области open source. В России есть разработки СУБД в виде ClickHouse, Tarantool, ветки PostgreSQL и т.д., коммерческих экспортируемых БД нет. Есть реестр российских программ где можно поинтересоваться текущим положением дел по отечественным СУБД. Состав правда вызывает сомнение, например, наряду с названиями, которые вы могли слышать встречаются и с названием типа Паспортный стол общежитий ВУЗа.

Переход на open source в России ускорился и в связи с санкциями. Помнится, выходившая новость об Oracle о возможном запрете продавать технологии для нефтегазовой отрасли России поменяло вектор видения будущего в умах и бюджетах некоторых наших компаний с хорошими бюджетами на ИТ. Как выше сказал фраза мы пилим монолит на микросервисы зачастую приводит в Open Source. В России, да как и во всём мире, растут PostgreSQL, MySQL, SQLite, MongoDB проекты.

Многим могло показаться, что open source давно обогнал commercial, но это мнение может сложиться если вы относитесь к миру online проектов, сайтов, приложений для смартфонов и т.д. Из этого следует следующее сравнение online vs. offline.

БД online проектов против offline

Есть 2 класса проектов, которые в чём-то похожи, а в чём-то нет. Это проекты с основным направлением онлайн продаж или оффлайн бизнес.

Если вы берёте классический бизнес оффлайн, связанный с добычей природных ресурсов, банковским делом, дистрибьюцией (оптовые продажи) или ритейлом (розничные продажи), то развивался он чаще всего из стека технологий на базе Windows решений. И переходя в онлайн он может использовать стек технологий WISA (Windows, IIS, MS SQL, ASP.Net). Есть и онлайн проекты изначально на WISA, для примера, всем известный StackOverflow. На текущий момент, в чисто онлайн проектах доминирует стек технологий типа LAMP (Linux Apache MySQL PHP). Новые молодые команды, приходящие в существующий бизнес, часто не работали с Windows стэком технологий и предлагают переписывать существующие системы. В России эта тенденция очень хорошо ощущается в последние годы.

Деньги любят счёт, и чтобы сравнить долю онлайн или оффлайн проектов давайте посмотрим рейтинг по выручке в мире. В топе ритейл, добыча ресурсов, автомобилестроение и т.д. В крупнейших компаниях (не только России) обычно зоопарк из технологий, тут будут ERP, CRM системы в виде SAP, Microsoft Dynamics NAV или AX, 1C в России и много чего ещё. Компании с большим оборотом используют разные системы управления предприятием, которые в свою очередь часто используют коммерческие БД, Oracle, MS SQL, IBM DB2.

Но капитализация компаний в мире за последние 10 лет изменилась и мы видим, что в топе теперь ИТ гиганты и всего 2 компании из прошлого топа Microsoft и Alphabet (Google). В динамике изменения тут. Это значит смещение денежного потока в онлайн. Мы все уже привыкли и платить онлайн и переводить деньги, покупать товары с доставкой и т.д. А текущий 2020 год принёс немало прибыли именно онлайн компаниям.

Рейтинг компаний России по выручке. На первых местах добыча ресурсов, банки, ритейл, а не ИТ корпорации. Яндекс на 113 месте. Правда по капитализации Яндекс входит в топ 20. Тенденции по внедрению большего числа решений с open source есть, причины описаны выше. Исторически многие крупнейшие онлайн-ритейлеры (магазины) в России на платных БД, но задумываются над переходом тестируя части функционала на open source. Банки начали миграцию в онлайн, пример Тинькофф банка подтверждает, что нужно развивать онлайн банкинг.

По итогам года из-за текущей ситуации с вирусом явно число и размер online проектов выросло. Растут и облачные решения, об этом ниже.

Реляционные СУБД против остальных

Обилие статей в том числе на Хабре про разные виды БД вводят в заблуждение, что весь мир уже перестаёт использовать классические реляционные СУБД. На самом деле RDBMS это подавляющее большинство всех СУБД в мире. На другие виды приходится около части и нужно понимать, что это чаще всего специфический функционал.

Для примера скажу, что в данный момент изо дня в день в работе типичной крупной компании может широко использоваться одновременно разные СУБД как платные MS SQL(Oracle) и ещё набор из MongoDB, Redis, MySQL, ClickHouse, ElasticSearch и т.д.

Рассмотрим очень кратко основные типы:

Relational: Главный тип, который ассоциируется с БД. Данные хранятся в виде 2-мерных таблиц с определёнными столбцами и строками, в которых хранятся значения. Индексы используются для ускорения поиска по указанному в создании индекса полю или полям. Связь между 2 таблицами идёт по одинаковым полям в них ключам (Key). Для добавления, изменения, удаления данных используется язык SQL (Structured Query Language), о нём ниже. Описание структуры данных хранится в самой же БД в данных системных реляционных таблиц. Эта простая идея с 2-мерными таблицами выдержала проверку временем и продолжает быть самой распространённой.

Document stores:

Отличие от реляционных БД в том, что данные хранятся в виде документов с любой структурой. То есть колонки таблицы жёстко не определены. Но тем не менее можно создавать индексы, которые вставляют ссылку на строку если находится указанный аттрибут документа. Типичный представитель MongoDB хранение документов используя синтаксис JSON (Java Script Object Notation). На самом деле BSON (Binary JSON), который компактнее, как и любые бинарные типы чем строковые.

Вот так выглядят строки в коллекции (это аналог таблиц)

Точно также таблицы могут ссылаться друг на друга.

Key-Value : появился этот тип NoSQL решения из-за необходимости быстро записывать, менять и получать значения по какому-то параметру. Не редко это используют для некритичных, быстроменяющихся значений, которые нет смысла записывать и хранить. Типичный представитель Redis. На старом ноутбуке вы можете получить десятки тысяч операций в секунду по записи, изменению данных. Достигается это тем что данные хранятся в памяти, с которой операции быстрее. Отсюда же и минус, что если памяти недостаточно, то скорость будет деградировать. Например, вы хотите измерять число запросов с 1 IP за минуту. Заводится строка где ключ это IP, любое обращение добавляет счётчику +1. Если запросов много, то троттлинг (ограничение). Ключ может иметь TTL и обнуляться раз в X минут.

Search Engines: Поиск это важная функция в любой системе. Если c поиском по точному совпадению в виде ID (кода, артикула, партномера и т.д.) реляционные БД справляются очень качественно и быстро, то поиск внутри документа по фразе, включая использование разных форм слова, множественного числа и прочих составляющих живого языка уже не выходит так быстро. Нужно сканировать данные от начала до конца и выискивать походящие документы. Поэтому поступают так как делают крупные поисковики индексируя страницы, если представить по простому, то они проходят по документам предварительно, составляют список слов, которые встречаются в документе и когда нужен поиск, то ищут по преподготовленным спискам слов с ссылками на документы, чем больше слов совпало тем вероятнее, что этот документ и нужен. Типичный представитель ElasticSearch его большое число инсталляций обусловлено ещё и тем, что существует типичный стек ELK (англ. лось) ElasticSearch+Logstash+Kibana для мониторинга событий, например, логов веб серверов или сервисов.

Wide column stores: Лучше всего представлять как среднее между реляционной БД и Key-Value БД. Есть таблицы, строки и колонки. Но колонки не имеют жёсткой структуры и могут иметь в разных строках разные названия и значения.

Представители этого типа БД Cassandra, HBase.

Graph : Тип БД, который призван обрабатывать данные, которые представлены в виде графов. В виде графов можно представить населённые пункты (вершины) и дороги между ними (ребро). Типичная задача поиск нахождения кратчайшего маршрута между пунктами путём обхода графа в ширину или более продвинутыми алгоритмами.

Также удобно представлять графами связи между людьми (вершины), что он знает кого-то (ребро) или их возраст и интересы. Формула химического соединения можно представить, что вершины графаатомы молекулы, а рёбра это связи между молекулам. Теория графов обширна и развивается с 18 века, так что математическая база накоплена большая. Типичный представитель графовой СУБД - Neo4j.

Columnstore

Хотя в рейтинге db-engines этот вид не идёт отдельно, а относится к реляционным, но стоит его упомянуть. Коммерческие реляционные СУБД включают в себя и этот вид как отдельную особенность, но и существуют специализированные отдельные решения. Основное отличие колоночных БД, что данные хранятся не в строках, а в столбцах. Если у вас в столбце одни и те же значения, то они очень сильно компрессируются и меньше места занимают на диске и в памяти. Представители этого типа ClickHouse, Vertica. Эту картинку с анимацией лучше смотреть на сайте ClickHouse.

В последнее время стала появляться в диаграммах СУБД ClickHouse от Яндекса. Цифры разные, но то что её стали замечать и включать уже хорошо для её развития.

Multi-model databases

Во многих СУБД, помимо основного исторического типа хранения добавлялись новые с течением времени. Мир не стоит на месте, поэтому если создатели СУБД видели необходимость поддержать другие типы хранения данных, то они добавлялись. Поэтому у большинства современных СУБД из топа в описании может присутствовать multi-model. Перевод крупных реляционных СУБД в разряд multi-model ограничила рост многих NoSQL решений в последние годы. Зачем использовать что-то ещё, если нужный тип включен в основную СУБД как вторичный.

SQL vs NoSQL

Сам термин NoSQL возник чуть более 10 лет назад примерно в 2009 году и как говорят сейчас пошёл хайп. Многие новые программные продукты, которые были призваны решить некую проблему присущую связке Реляционная БД + SQL гордо начали именоваться NoSQL, чтобы показать, что они новые и продвигают невиданные доселе технологии способные решить много проблем. А проблемы действительно были. Нужна была возможность легко горизонтально масштабировать решения в связи с ростом данных, которые могли прибывать в больших количествах, объёмы данных стали резко увеличиваться. Причём стали сохраняться данные, которые не были структурированы, например, с сайта информация на что кликал, куда переходил пользователь, что искал, какие элементы всплывали, баннер показывался и т.д. Всё это сваливается и хранится. Сейчас вы и не удивляетесь, что вам упорно показывают рекламу по теме, которая вас однажды заинтересовала, вас уверенно ведут к воронке принятия решения, о покупке, подписке и т.д.

График роста данных, он немного не свежий, но показывающий рост данных более 10 лет назад.

По своему опыту скажу, в 0-вых годах оффлайн компании генерили больше долларов выручки на 1 Gb данных в БД, чем сейчас онлайн компания. И соотношение примерно раз в 100-200 меньше долларов на Гб у онлайн.

Второй вытекающей из роста данных проблемой SQL были тяжелые транзакционные операции по записи в БД и не могли достигать показателя 10 или 100 тысяч в секунду на простом оборудовании. Я не буду сейчас распространяться, что есть плюс, а что минус транзакций, но оказалось, что можно ускорить транзакции, упростить их или часто они не нужны. Не нужны если данных так много, что потеряв некоторые вы восстановите всю картину запросто на оставшихся данных. Не все данные важны настолько, что их частичная потеря критична для бизнеса.

И появились простые, но эффективные NoSQL системы, которые решают проблемы, не связанные накопленными ограничениями SQL, работают быстро и чаще всего ещё и бесплатные. Но чудес не бывает, поэтому часто NoSQL решения имеют ограниченный функционал или работают быстро пока не кончится какой-нибудь ресурс, например, оперативная память или число коннектов.

Часто можно встретить такого рода картинки классификаций NoSQL БД по типам и с примерами СУБД. Выше мы их рассмотрели.

A что же SQL Structured Query Language структурированный язык запросов? Он существует с начала 1970-х годов, был стандартизирован и благодаря этим стандартам, которые поддерживают все создатели реляционных БД минимизирует разницу в работе с разными реляционными СУБД. Да, производители вставляют свои собственные фичи (features - особенности), которые могут выходить за пределы стандартов SQL, так как предлагают некую новые конкурентные технологии, если они будут поддержаны массово, то эти новые технологии и их описание будут включены со временем в стандарт SQL. Если вы пишете SQL запросы в одной СУБД, то вам не составит большого труда перейти на другую и продолжить работать с ней. Мало того, часто в клиентах NoSQL СУБД, есть фишка в виде запросов на SQL. Например, для MongoDB я часто использую Studio3T, где вы можете писать обычный SQL и он переводится в специализированные запросы MongoDB, для самого MongoDB есть SQL адаптер. ClickHouse и Tarantool (российские разработки) поддерживают SQL запросы. Также во многих NoSQL СУБД появились особенности присущие SQL, например, join-ы, схемы данных, логика NULL для значений и т.д.

Cloud DBs vs DBs

Облачные технологии постепенно растут. Рост этот составляет десятки и сотни процентов в год на разные облачные технологии. Среди них есть и облачные БД. Если в любой области экономики есть рост с такими цифрами значит всё больше усилий будут прилагать компании, чтобы занять этот рынок и получить свой кусок пирога, а дальше заинтересовать побольше клиентов и включить их в этот пирог растущий дальше. Да, мы с вами это часть их пирога. На Хабре много статей про облака, плюсы и минусы, чтобы не повторяться можно почитать например, тут или в другой статье по соответствующим тэгам.

По оценкам Gartner объём всего облачного рынка за 5 лет вырастет в 2 раза.

Вот такие распределения по компаниям если брать BPaaS и IaaS. По моим ощущениям очень похоже на правду. AWS лидер, Microsoft понемногу догоняет в последние годы, Alibaba растёт и дешевле всего на рынке Китая, который уже нельзя игнорировать глобальным компаниям.

Рынок БД в облаке (DBaaS) выглядит в цифрах гораздо скромнее по сравнению с цифрами всех облачных трат, при том, что каждая компания имеет свои БД и не малые.

Объясняется это такими факторами:

1. Зачастую компании не используют специфичные облачные БД провайдера, потому что нужно адаптировать приложения, есть особенности, которые не позволяют это делать. Чаще сейчас используется облачная инфраструктура, то есть вы получаете свои виртуальные хосты с CPU, RAM, SSD(HDD) и используете её, чтобы там установить экземпляры стандартных СУБД.

2. Компании не спешат переводить именно данные в облако, переводя туда другие сервисы. Встаёт вопрос безопасности данных. Одно дело, когда ваши данные в конкретном точном месте вашей сети, физических хостингов, пускай и распределённых по миру, а другое когда вы затолкали данные в чёрный ящик поставщика облачных услуг и вам непонятно как, где оно хранится, как охраняется и т.д.

3. Кто столкнулся с облаками, тот знает, что траты могут возникнуть откуда вы не ждёте и соответственно не просчитали стоимость. Приведу пример, положить данные на хранение в холодное облачное хранилище стоит не дорого, а вот скачать обратно уже в десятки или сотни раз дороже. Поэтому если вы делаете бэкапы и храните их не трогая, то это дешевле своих дисков, но вот когда захотите обратно скачать, то сначала вы подождёте много часов до извлечения, а потом заплатите значительную сумму за каждый Mb. И такая ситуация и для AWS и для Azure и остальных. Вот недавняя история про NASA, когда им аудиторы сказали, что будут платить гораздо больше. Случаи перерасходов бюджетов от роста функционала сплошь и рядом.

4. Скорости перемещения информации из облака иногда удручают. Например, вы сделали бэкап на одном хостинге в облако, а потом решили поднять на другом хостинге. Это же облако. Но если вы и подключили платную опцию, что у вас распределённое хранение данных во многих регионах, то вас может неприятно удивить скорость восстановления в соседнем штате США. Если бэкап на сотни Gb, то в разы будет быстрее сделать бэкап на локальный диск, скопировать по выделенному каналу бэкап и поднять его на другом хостинге.

5. Могут возникать небольшие изменения производительности, которые трудно чем-то объяснить как работой соседних виртуальных хостов, которые разделяют с вами физический хост провайдера. Если вы работали с виртуальными хостами в локальной сети, то наверняка сталкивались как могут влиять друг на друга загруженные виртуальные сервера на одной физике. В своей сети вы можете на это повлиять или сделать замеры вынося виртуальные сервера с физического сервера.

6. Если вас подсадили на облака, то вы зависите от этого провайдера облачных услуг, а это ещё одна точка отказа в вашей информационной инфраструктуре. Например, Alibaba выключает ресурсы не глядя, как только пробьёт 12 часов и что-то у вас превратится в тыкву по причине того, что на вашем счете в облаке нет заранее добавленных юаней.

7. В России к этому добавляется ситуация, что не так давно проводилась масштабная блокировка зарубежных ресурсов, блокировали многое включая IP адреса облачных БД. Это реальность и она может быть во многих странах.

8. В связи с санкциями применяемых к России у отечественных компаний возникают вопросы с хранением данных в облаках американских компаний из топа. Нет гарантий, что завтра все ваши данные не пропадут вместе с отключённым аккаунтом. Отключать облачные аккаунты можно очень быстро и удобно.

В России есть Яндекс.Облако, SberCloud, честно не пользовался ими в плане БД. Был опыт использования других сервисов Яндекса, которые потом перевели в облако, поменяли протоколы и сделали платными. Пока не заинтересовали платить деньги, так как есть другие поставщики как Microsoft, Google, которые имеют бесплатные квоты для небольших объёмов и есть ещё ряд преимуществ.

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

Если вам нужно развернуть быстро в любой точке мира функционал, то альтернативы облакам нет, но будьте готовы потом оплачивать счета соразмерные потраченным ресурсам, про которые вы и не подозревали. Нужно просчитывать заранее траты или пробовать в реалиях. И безопасность в облаках ещё не убедительна.

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

OLTP vs OLAP

Данные в БД могут использоваться для проведения текущих операций бизнеса Online Transaction Processing (OLTP): найти клиента, выписать счёт, оплатить ресурс, списать остаток со склада при заказе товара и т.д. Почти все эти операции должны проводиться в режиме реального времени. Если пользователь на сайте или во внутрикорпоративной системе ожидает по несколько секунд простые операции и это проблема с БД, то значит есть что оптимизировать. OLTP изначально проектируются для ведения бизнеса в реальном времени. Если компания имеет базы данных, то там есть OLTP.

Есть данные, которые используются для анализа работы компании Online Analytical Processing (OLAP). То есть для OLAP собираются большие массивы данных и чтобы их быстро просчитывать в любом разрезе нужна простая магия по предрасчитыванию всего, что с наибольшей вероятность может понадобится бизнесу. То есть если вы хотите знать количества кликов на вашем глобальном сайте по странам или страницам, то их нужно заранее просчитать да ещё делая эту группировку по времени, чтобы потом смотреть динамику во времени, сравнивать с историческими трендами. И OLAP хранилища могут быть не реляционными да и вообще не структурированными, использовать специализированные языки управления большими массивами данных, или языки для статистической обработки данных. В последнее время стало модно называть обычных специалистов по аналитике в бизнесе Data Scientist. Это не совсем верно, но термин уже прижился. Обычно это смесь из следующих ингредиентов SQL, Python, R, фреймворков для работы с нейронными сетями, математическими моделями разного вида и т.д.

Количество OLAP БД обычно меньше в количественном отношении чем OLTP, но размеры их больше. Для OLAP БД важна поддержка многопоточности, когда запрос распараллеливается между ядрами и каждое ядро делает свою часть работы. Если ваша OLAP СУБД умеет шардироваться на много серверов, хорошо работает с многопоточностью, поддерживает все последние SIMD (single instruction, multiple data) инструкции процессоров, когда за 1 операцию обрабатываются большие пакеты данных, то скорость обработки данных увеличивается кратно на все эти множители.

Общая тенденция такая, что аналитику отделяют от данных необходимы для операционной деятельности. Вынос аналитических данных, обычно может состоять из выноса на отдельные сервера где можно считать, что угодно не влияя на работу компании. Для просчётов нейросетей и прочих тяжёлых математических моделей используют облака с услугой облачных вычислений на специализированных платах, например, NVidia Tesla.

SSD vs HDD vs Storage vs Tape vs Other

Эта часть о том на каких хранителях хранить данные для БД.

В 2020 году не остаётся сомнений в том, что SSD побеждают в борьбе с HDD. В серверных системах с БД это понимание пришло гораздо раньше, чем где либо. Всё дело в том, что в большинстве типов БД, важно не последовательное чтение, а чтение в память из разных мест с диска. И такая же случайная запись для данных. С этим нет проблем у SSD, тогда как скорость доступа до случайного места на диске у HDD достигается скоростью вращения шпинделя и скоростью перемещения считывающего механизма между дорожками. Попробуйте одновременно копировать несколько десятков файлов на HDD из разных мест, скорость быстро деградирует до неприемлемых значений. Так и запросы данных от 1000 пользователей, которые лежат в разных местах диска быстро сведут на нет скорость любого HDD. Поэтому для операционных OLTP систем нет большого смысла использовать HDD. На картинке ниже обычные SSD c 6000 IOPS (операций считывания и записи на диск в секунду), в серверных решениях особенно с NVME есть гораздо больше, но стоит отделять маркетинговые цифры на коротких замерах, попадающих в кэш от реальной работы диска в таком режиме круглыми сутками.

HDD есть смысл использовать в OLAP системах, когда данные лежат последовательно и их нужно читать и записывать только так или есть смысл использовать для бэкапов данных, это крупная последовательная запись и чтение. Также в больших архивных БД и везде где стоимость хранения 1 Гб это решающая единица. HDD дешевле SSD по стоимости за 1 Гб.

По отказоустойчивости SSD лучше HDD если их рассматривать как отдельные устройства. Это личный опыт на тысячах экземплярах. Выходы из строя SSD гораздо реже HDD, но нужно понимать, что это статистика по серверным моделям, многие из которых производились по нормам SLC и MLC, стоящие дороже, позволяющие перезаписывать данные гораздо больше раз чем продвигаемые сейчас TLC и QLC, которые не рекомендуются для БД. Для серверных систем где хранятся БД используют диски и комплектующие с повышенной отказоустойчивостью. SSD диск в 1Tb и стоимостью 1000$ - это нормальная ситуация для БД. В них заложены возможности работать месяцами на пределе, не только много читая, но и много записывая, не перегреваясь или резко сбрасывая скорость. Не нашлось картинки по сравнению отказоустойчивости серверных SSD и HDD, но есть про обычные. SSD выходят из строя реже.

Форм-фактор SSD это 2.5 дюйма устройства для горячей замены, PCI-X карты, U.2 серверный аналог M.2, который в настольных компьютерах. Современный протокол SSD NVME.

Storage Система Хранения Данных (СХД) - это внешние хранилища данных, которые подключаются к серверам по оптоволокну или сетевому интерфейсу. Хранилища ставятся в те же серверные стойки, что и сервера и соединяются с ними. СХД это ещё один огромный пласт информации, которого хватит на 10 статей. Специализированное оборудование для хранения данных. Их основное предназначение это высокая отказоустойчивость, повышенная скорость обработки данных. Стоимости хранилищ данных начинаются от десятков тысяч долларов за продвинутые версии и это с минимальным набором дисков. Верхняя планка не ограничена, она может достигать и миллионов долларов и больше. Современные СХД могут иметь в названии слова типа AllFlash что подразумевает отказ в них от HDD и внутренние алгоритмы и код оптимизированы только под SSD.

После поглощения EMC компания DELL упрочила своё положение на рынке хранилищ уровня предприятий. Huawei растёт на глазах и становится заметным игроком несмотря на санкции США. В России нет своих хранилищ данных мирового уровня, все значимые игроки рынка просто перемаркируют готовые изделия своей торговой маркой или собирают из частей известных производителей или вендоров свой вариант.

Intel Optane (3D Xpoint) специфичный вид энергонезависимой памяти, самый быстрый на данный момент на случайное чтение, но на случайную запись нет такого явного преимущества, а в последовательном чтении и записи проигрывает топовым SSD. Не развился из-за высокой цены на накопители и отсутствия накопителей большого объёма. Так SSD+NVME обеспечивают лучшие показатели цена/качество. За цену Optane можно купить несколько SSD, которые в RAID будут давать большую скорость.

RAID Нет смысла повторяться для чего нужны объединения дисков в массивы, для скорости и для отказоустойчивости. Прочитать можно здесь. Смотря какую задачу вы решаете, тот RAID и используется. Для OLTP БД чаще всего встречается RAID10.

Tape ленточное хранение данных. Многие будут удивлены, но в 2020 году ленты ещё живы. Выходят новые версии картриджей с лентами, выпускаются огромные библиотеки хранения на сотни картриджей. Всё объясняется тем, что стоимость хранения на лентах продолжает быть самой низкой. Хранение плёнки не требует электричества, длительность хранения выше чем на дисках, но скорость доступа очень низкая и это последовательное чтение и запись. Есть подозрение, что в облаках самые холодные хранилища архивных данных могут использовать и ленты.

Возможности СУБД

Любые особенности СУБД это конкурентные преимущества, которые играют роль при выборе для развёртывания её в своей инфраструктуре. Я не буду рассматривать всем понятные темы как безошибочность работы СУБД, поддержка разных наборов символов, сортировок для любой страны и т.д., как должно быть лучше очевидно. Также про стоимость было сказано выше. Разговор будет про важные и востребованные технологии и части СУБД. На самом деле их больше.

Горизонтальное масштабирование из коробки: Horizontal scaling vs Vertical Scaling.

Это то, что в немалой степени определило появление множества видов БД и СУБД в последние 10-15 лет. Если в начале 2000-x Oracle, Microsoft, IBM вели обратную агитацию и призывали объединять разрозненные данные из множества филиалов компаний в единый центр где стоит мощный сервер с данными и все работают удалённо с этими данными, включая появившиеся корпоративные сайты, Web API, мобильных клиентов, то уже в конце 2000-x при взрывном росте данных стало понятно, что вертикально масштабировать (покупать всё больший сервер) стоит уже слишком дорого или уже невозможно. Упирались в число CPU, дисков, сеть, соединений и т.д. для центральных узлов инфраструктуры. Поэтому появились решения, позволяющие распределять данные на множество серверов БД.

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

Не стоит думать, что горизонтальное масштабирование решит все ваши проблемы. Наоборот оно привнесёт усложнение всего и вся: кода, инфраструктуры, поиска ошибок и много чего ещё. А это тоже всё деньги. Но вам уже не понадобятся очень мощные и дорогие сервера, ваш проект сможет пережить большие нагрузки. Отдельные узлы хранящие данные часто называют шардами, а процесс распределения данных и запросов между узлами шардированием. Если правильно выбирать ключ шардирования, то запросы за данными идут только к той шарде где они есть. Брать на себя распределение запросов может как само приложение так и движок СУБД. Ниже на картинке пример, когда используется hash функция для шардирования, чтобы определить какой сервер использовать. И ещё у каждой шарды могут быть копии (реплики).

В данный момент, не так просто купить новые 8 процессорные сервера для пиковой производительности, их число очень ограничено они не нужны рынку, вытеснены 4 процессорными, которые дешевле и не в 2 раза, а больше. И если брать реальный пример, то 2 процессорный современный сервер по мощности вычислений сопоставим или превосходит 8 процессорный сервер 10 летней давности. Помимо процессоров ускорились все компоненты серверов: память, шина и т.д. Тот же самый запрос будет работать в 2-3 раза быстрее, если все данные в памяти. СУБД очень хорошо умеют использовать ядра и параллелить выполнение запросов.

У облачных провайдеров при росте числа ядер в виртуальном сервере в 2 раза цена растёт быстрее чем в 2 раза и имеет место ограничение на число ядер, потому, что провайдеру не выгодны виртуалки, которым нужно выдавать большую часть физического сервера, что с ним делать когда вы не нагружаете его, мощности простаивают.

Итог: для реально больших проектов вам понадобится горизонтальное масштабирование, если вы не доросли до такого, то лучше не усложняйте сервисы и БД разнесением на множество узлов, если только в СУБД это не поддерживается совершенно прозрачно для приложений. Проще проводить действия в оперативной памяти одного сервера чем тащить разрозненные куски данных с разных узлов и объединять их, чтобы получать результат.

Отказоустойчивость из коробки - High Availability. Master-master, master-slave.

High Availability это термин когда ваш проект должен работать всегда. Любой простой в минуту грозит прямыми или косвенными убытками. В отказоустойчивых системах это достигается дублированием узлов. Так поступают в космической промышленности, предотвращая ситуацию, что выход из строя оборудования дорого обойдётся в космосе и так поступают и на земле с важной системой в продакшене. Дублируются важные узлы вашей инфраструктуры: сервера, хранилища данных, сетевые коммутаторы, каналы интернет и т.д.

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

Также для отказоустойчивости создаются online копии данных, которые позволяют содержать актуальную информацию. Данные синхронизируются в копию синхронно или асинхронно. Синхронно это когда операция изменения подтверждается всеми узлами, что ведёт к задержкам в сохранении или асинхронно, когда данные сохраняются и потом распространяются в другое место отдельно.

Выглядит примерно так. Есть одна БД с данными, они расходятся на остальные сервера, возможно в удалённом дата-центре. Slave-копии могут использоваться для чтения, запросы за данными могут направляться на копии. Называется Master-Slave.

Но рано или поздно начнёт возникать ситуация, что ваша БД не успевает записывать. Хочется, чтобы данные могли записываться в другой копии, она была бы не только для чтения. Эта схема посложнее, так как нужно ещё разрешать конфликты если изменения одинаковых данных происходит на разных узлах в одно время, а если у вас master копии далеко, то вероятность конфликтов увеличивается. Это называется Master-Master. Плюс у master могут быть slave копии.

Дублирования данных на другие сервера могут быть как по всей БД, так и по отдельным таблицам.

Хорошо, когда ваша СУБД поддерживает такое распространение данных, на вас не ложится груз проблем как зафиксировав изменение данных в одном месте добиваться, чтобы данные появились в другом. А если сеть моргнула, а если копия не отвечает, и ещё много если на себя берёт движок. Если master становится недоступным, то происходит автоматическое перераспределение ролей, вчерашний slave становится master, а все остальные копии принимают от него данные. Приложение не замечает переключение, потому что работает через роутер, который также переключается. У вас сломался сервер с БД, а простоя нет, всё продолжает работать как ни в чём не бывало. Также можно проводить работы с экземпляром БД выключая его из работы, установить обновление СУБД и потом обратно ввести в работу.

Продвинутые хранилища СХД умеют копировать данные на другое хранилище в фоновом режиме на уровне дисков. У вас просто копия диска в другом месте. В ряде случаев можно использовать такие копии, но обычно они не доступны даже для чтения до момента прекращения синхронизации.

Online maintenance - online alter

24/7/365 означает, что ваш проект работает всегда 24 часа, 7 дней в неделю и все 365 дней. У вас нет окна для работ по обслуживанию БД (maintenance). Значит все операции по созданию архивных копий, перестройки индексов, созданию таблиц, удалению колонок и много чего ещё, что должно проходить онлайн без заметной деградации производительности. То есть пока вы перестраиваете таблицу, например, удаляя колонку данных в реляционной БД, то таблица будет доступна, а будет создаваться копия, которая будет содержать все изменения пока идёт процесс перестройки. Не всегда есть возможность иметь много копий серверов с БД, для платных СУБД это ещё и деньги, чтобы проводить работы по очереди, поэтому возможность изменений структур без прерывания работы очень важно.

Мониторинг

Каждая интенсивно используемая и изменяемая БД рано или поздно сталкивается с вопросами производительности. Но как понять, что проблемы начались заранее не доводя до потери денег компаниями? Нужно мониторить состояние вашей СУБД. Если вы можете делать замеры текущего состояния, а ещё лучше смотреть статистику, которая собирает сама же СУБД, то вы из коробки можете решить множество проблем. В платных СУБД есть невероятно огромное количество метрик и статистик, есть сторонние компании, создающие инструменты мониторинга. Рынок очень конкурентный и в каждом инструменте есть свои особенности. Бесплатные СУБД в последние годы сильно улучшились в плане мониторинга и к ним тоже стали создавать мониторинги, только часто они уже не бесплатные.

Инструменты управления СУБД

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

Или для некоторых СУБД есть прекрасная возможность, востребованная у работающих с крупными данными, когда виден % исполнения запроса. Вы видите когда закончится запрос и выдаст результат.

Ещё из интересного: скриптование данных это создание инструкций SQL, которые создадут копию данных на другом сервере, миграция данных, сравнение структур данных, сравнение данных, экспорт в другие форматы, системы контроля версий и обновления product-среды и т.д.

Есть инструменты, которые используются для управления разными типами БД, например, DataGrip от JetBrains (те самые которые причастны к Kotlin, ReSharper, GoLand и т.д.) очень мощный и настраиваемый. Картинка СУБД, с которыми он работает.

Расширение функционала СУБД на другом языке программирования

Какой бы мощной не была СУБД, но рано или поздно может не хватить встроенных функций. Например, какой-то новый вид хэширования или сжатия данных, который нужно поддержать на уровне БД. Если в языках программирования вы скачиваете нужную библиотеку, исходники и можете вставить вызов этого кода и не нужно придумывать велосипед, тратить ресурсы компании, то такой же подход можно реализовать и в СУБД. Вы можете расширить функционал СУБД из коробки своими подключаемыми модулями и они будут работать так же как просто как встроенные команды. Мало того, часто такие расширения могут работать быстрее чем встроенный код самой СУБД. Конечно, есть ряд ограничений, который накладывается на такого рода расширения, но сам факт наличия возможности писать код на распространённых языках программирования немаловажный плюс.

Логирование изменений

Важным вопросом бывает вопрос, а что было с данными или структурами БД на некий момент назад. Логирование изменений пригодится, когда вы поменяете структуры или данные, а понадобится вернуть обратно сами данные, либо структуры таблиц, индексы, либо код SQL в запросах. Вы будете знать, что на такой-то момент было так. Также это предохраняет от уничтожения данных, чаще всего непреднамеренного. В каждой СУБД название технологий разное, для примера Flashback Data Archive, Temporal history, Change Tracking, Data Audit и т.д.

Если ваша СУБД умеет, просто включать или выключать такое логирование, то это то что обязательно пригодится. Но чудес не бывает, поэтому если вы начинаете логировать, то привносите нагрузку, например, загружаются диски, растёт размер логов, они могут быть в БД или отдельно, если БД имеет копию, то сохранение в БД расходится по копиям.

Бизнес-логика в БД или нет

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

Бизнес-логика - это ряд правил, которые влияют на всю информационную структуру компании. Есть несколько подходов по хранению бизнес-логики. Например, на уровне приложений (сервисов, микро-сервисов) или если бизнес-логика в БД, то помимо хранения данных ещё и решает интеллектуально как возвращать данные, как накладывать фильтры, объединять данные из разных источников и т.д. Это даёт ряд преимуществ по гибкости и скорости изменений, но растёт нагрузка и в итоге упирается в производительность. Или, например, вы решили запретить редактировать данные прошлых годов, это правило легко реализуется на БД вместо того, чтобы отслеживать все возможные способы сделать это из сервисов, программ, сайтов и т.д. Чем больше способов управлять бизнес-логикой на БД тем лучше, вы будете иметь разные способы решить бизнес-задачу.

Если БД нагружена, то выносится бизнес-логика на уровень сервисов, кэшируются всеми способами результаты и т.д. БД используется только для быстрого получения данных.

Не все БД позволяют хранить код запросов в БД, например, в ClickHouse нет даже полноценного языка скриптов. Как организовывать хранение бизнес-логики компания решает самостоятельно, здесь нет универсальных правил.

Поддержка JSON

Самый скачиваемый NuGet-ом пакет в Micrsoft Visual Studio для языка C# - это библиотека для работы с JSON (JavaScript Object Notation). Этот пример показывает, что если нечто востребовано, то оно будет пробиваться везде где сможет даже у Microsoft, который исторически развивал XML. Хотя хранение в JSON противоречит правилам реляционных БД, но реальность такова, что слишком много данных в JSON в ИТ инфраструктуре и поддержку этого формата вставляют в СУБД разного типа.

In Memory

Оперативная память RAM быстрее любых жёстких дисков. Поэтому для максимального ускорения операций, в том числе с БД нужно по максимуму использовать память. Скажу сразу, что во всех СУБД память стараются использовать по полной и БД любого типа работает быстрее если много памяти на сервере. Часто запись на диск данных откладывается и не происходит непосредственно во время проведения операции записи. Есть много технологий и в разных СУБД они разные или могут называться по-разному, чтобы снизить число обращений к дискам.

Какие-то СУБД поддерживают возможность In-Memory как вариант работы, а некоторые объявляют эту возможность как главную, например, Tarantool.

Сжатие данных

Немаловажный параметр, который позволит экономить на размерах дисков и памяти серверов. Кроме этого сжатие ускоряет в разы операции чтения данных, сохранения копий данных и т.д. Например для OLAP хранилищ это значит более быстрое получение результатов запросов по огромной массе данных. Нужно понимать, что сжатие не бесплатно для ресурсов, но плюсов больше чем минусов, алгоритмы сжатия используются быстрые, которые не сильно нагружают CPU. Обычно сжатие задаётся на уровне СУБД и в работе программного обеспечения никак не сказывается, то есть ничего не нужно менять в коде.

Временные (temporary) объекты

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

MapReduce

Под этим устоявшимся термином от Google будем обозначать класс задач по распределённым вычислениям. Название идёт от двух шагов Map распределяющего входные данные между распределёнными узлами и Reduce получение результатов от распределённых узлов и формирование итогового результата. Представители Apache Hadoop и Spark это целый набор библиотек, файловой распределённой системы HDFS и много чего ещё. Примером СУБД для работы с такими фреймворками является Hive, реляционная СУБД с поддержкой SQL. Тренды.

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

Работа с пространственными данными

Если необходимо находить объекты в реальном мире и чаще всего это задача нахождения ближайших объектов по отношению к некой точке в пространстве. Но как искать такие данные в реляционных данных? В принципе ничего не мешает делать свои собственные способы как искать в любом виде БД ближайшие точки, как подготавливать данные, чтобы поиск был быстрым. Разработчики СУБД тоже увидев спрос на такие поиски добавили технологии для пространственных индексов (spatial index) в виде сеток или часто можно встретить реализацию индексов с помощью R-tree дерева.

Graph data

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

Безопасность

Обычно БД находятся внутри защищённого контура сети. И напрямую никто не открывает порты наружу. Если вы всё же каким-то образом открыли доступ до сервера, то не сомневайтесь, что сначала будут прощупывать порты, нет ли там БД известного типа и потом пытаться сломать. Информация имеет ценность, поэтому открыть доступ к серверам БД наружу это как выставить сейф с деньгами в окно - странный вариант.

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

Использование GPU, NPU (Neural Processing Unit), Google TPU (Tensor Processing Unit)

На современном этапе развития БД каких-то массовых использований графических и специализированных процессоров в движках СУБД не наблюдается. Да, GPU и NPU используются для математических расчётов, обучения нейронных сетей, но размер оперативной памяти GPU и NPU меньше чем у обычных серверов, а задача выборки или обновления данных (наиболее частые в БД) не требуют огромной вычислительной мощности. Данные из БД можно подавать на вход специализированных фреймворков работающих с нейронными сетями для дальнейших итераций. DPU (Data Processing Unit) это класс процессоров не имеющего стандартов, обычно интегрированных в сетевые карты. Их будущее ещё под вопросом.

Community

Большое сообщество единомышленников, использующих то же ПО обеспечит вам ответы на вопросы, которые могут быть не так тривиальны. Первое, что стоит смотреть при непонятной ошибке это аналогичные вопросы с тем же текстом ошибки или описанием ситуации. Для разных СУБД есть множество сайтов с авторитетными авторами. Тем не менее приведу статистику с StackOverflow.com сколько вопросов есть по топовым БД, на каждый может быть несколько ответов. Наверняка Ваш вопрос будет уже с решением если сообщество большое. Такая вот накопленная KnowledgeBase.

Для общей картины, изображение связей БД, фреймворков, языков программирования, платформ взятых . Не смотрим % использования - это всегда причина для холиваров (holy war - священная война). Здесь больше интересны связи, что с чем чаще входит в связь. Красным отмечены СУБД. Куда делись Oracle и IBM DB2 это загадка на совести составителей диаграммы.

Подведём итоги: Все СУБД из топа завоевали это место в процессе естественного отбора и имеют свой кусок рынка. OpenSource побеждает Commercial СУБД, в России процесс ускорился в этом направлении. СУБД Online проектов растут в количестве отнимая долю в выручке у оффлайн бизнеса. Реляционные БД в ближайшее время не сдадут позиции и будут преобладать. Для управления БД будет доминировать язык SQL. Для хранения данных операционных БД используется флеш-память. Чем больше возможностей и особенностей у СУБД и большее число людей использует, то тем проще её интегрировать в инфраструктуру и поддерживать. Переход в облака БД идёт фрагментарно, в ближайшие годы большая часть данных будет храниться локально. Российские технологии по БД в мировом масштабе не особо заметны, но есть такие и пожелаем им успеха.

Привет!

Сегодня мы выпустили DataGrip 2021.1: наш самый мощный релиз за последние годы. И это не шутка!

Самое важное:

• Интерфейс для работы с правами доступа

• Контекстные шаблоны Live Templates

• Упрощенная навигация

• Легкое копирование источников данных

• Улучшенная сортировка

• Редактирование данных в MongoDB

• Поддержка Azure MFA

Редактирование прав

В окне редактирования объекта теперь можно изменять права на объект.

Также права можно добавлять и изменять в окне редактирования пользователя или роли. Напомним, что вызываются окна редактирования нажатием Cmd/Ctrl+F6.

Это работает в PostgreSQL, Redshift, Greenplum, MySQL, MariaDB, DB2, SQL Server и Sybase.

Контекстные шаблоны

Нас давно просили сделать как у других: чтобы для таблицы в проводнике можно было быстро сгенерировать простой запрос, например SELECT TOP 100 FROM %tableName%.

Мы всегда отвечали, что как только вы научитесь пользоваться шаблонами кода Live Templates, ваша жизнь изменится навсегда, и не надо будет тыкать мышкой во вложенные меню.

И это правда. Но бывает, что у пользователя открыт проводник и он находится в контексте нужного объекта в таком случае сгенерировать запрос к этому объекту на месте будет проще, чем переключаться в консоль, а потом находить этот объект, используя автодополнение. К тому же, многие все еще часто пользуются мышкой :)

В итоге мы сделали новый вид шаблонов кода контекстные шаблоны. Работают они так:

Посмотрим шаблон Select first N rows from a table. Найдите его в настройках:

Он выглядит, как обычный шаблон, и его можно использовать в таком качестве. Но у переменной $table$ есть специальное выражение dbObjectName(). Вы увидите это, нажав на Edit Variables. Так вот, именно это выражение делает шаблон контекстным, то есть значение в эту переменную можно подставить автоматически кликнув на любой объект в проводнике базы данных.

Важно отметить, что синтаксис этого шаблона работает не во всех базах, в нижней части окна перечислены диалекты, для которых такой код можно сгенерировать.

В настройках можно выбрать, будет ли запрос генерироваться в новую консоль или в активную.

Редактор данных

Редактирование данных в MongoDB

Этого нам не хватало для полноценной поддержки Монги, но теперь все на месте: вы можете редактировать данные а апдейт-запрос просматривать до того, как данные отправятся в базу.

<img src="http://personeltest.ru/aways/habrastorage.org/webt/1t/ux/8y/1tux8yvfyrs2eiha3byoj0ss2tu.png" />

Еще можно менять тип ячейки: из контекстного меню или панели инструментов при просмотре больших значений.

Сортировка

Сортировка стала более удобной:

• Мы добавили поле ORDER BY, а <Filter> переименовали в WHERE. Впишите в ORDER BY условия сортировки, чтобы получился работающий запрос.

• По умолчанию сортировка не накапливается: каждый клик по новой колонке сбрасывает сортировку по другим колонкам. Если хочется сортировать по нескольким колонкам сразу, используйте клик с зажатой клавишей Alt.

Если хотите отсортировать данные на стороне DataGrip, отключите Sort via ORDER BY. Конечно, в этом случае, сортируются данные только на текущей странице.

Теперь вы можете по умолчанию открывать таблицы отсортированными по числовому первичному ключу.

Панель инструментов

Мы немного обновили панель инструментов в редакторе данных: добавили кнопки Revert Changes и Find. Кнопки Rollback и Commit в режиме автоматического подтверждения транзакций скрываются.

Транспонирование однострочных результатов

Если в полученном результате одна строка, его удобно сразу смотреть в транспонированном виде. Мы сделали для этого настройку:

Навигация

Предсказуемые действия

Мы удалили эти настройки:

Если вы никогда не меняли их значения по умолчанию, то главное изменение в новой версии для вас такое: действие Go to declaration (Ctrl/Cmd+B) теперь открывает DDL этого объекта. Раньше оно подсвечивало объект в проводнике базы данных.

Для перемещения к объекту в проводнике мы представили новое сочетание клавиш: Alt+Shift+B для Windows/Linux и Opt+Shift+B для macOS.

Теперь нет сложной логики, и каждое нажатие горячих клавиш ведет в туда, куда вы ожидаете попасть:

• Ctrl/Cmd+B открывает DDL.

• F4 открывает данные.

• Alt/Opt+Shift+B подсвечивает объект в проводнике.

Удаление настроек всегда ломает привычки некоторому количеству людей. Мы постарались учесть это. Вот наши советы таким пользователям:

• Все сочетания клавиш можно менять. Если, например, вы не хотите отвыкать от того, что Ctrl/Cmd+B подсвечивает объект в проводнике, назначьте это сочетание клавиш действию Select in database tree.

• В то же время, если вам нравится, что внутри скрипта Ctrl/Cmd+B и Ctrl/Cmd+Click открывает определение CREATE, не убирайте эти сочетания с действия Go to declaration, если вы последовали предыдущему совету.

• Если вам нравилось, что при отключенной настройке Preview data editor over DDL editor двойной клик по таблице открывал DDL, это можно вернуть через ключ в реестре. Он называется database.legacy.navigate.to.code.from.tree. Но мы не советуем менять значения в реестре и надеемся, что те полпроцента людей, у которых эта галочка была снята, быстро привыкнут к новому поведению :)

Если мы про что-то забыли, пишите, пожалуйста, в комментах.

Вкладка Database

Тут мы ничего особо не сделали просто переименовали вкладку Tables в Database. Этим мы напоминаем, что по сочетанию клавиш Cmd+O/Ctrl+N можно искать не только таблицы, но и процедуры, функции, схемы.

Соединение

Поддержка Azure MFA

Мы поддержали интерактивную аутентификацию через Azure Active Directory. Если она включена, при соединении у вас автоматически откроется браузер, где вы сможете завершить аутентификацию.

Версия 2.x драйвера для Redshift

Этот драйвер можно скачать в DataGrip, начиная с версии 2021.1. Главное изменение состоит в том, что теперь запросы можно останавливать.

Полная поддержка Google Big Query

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

Поддержка диалекта CockroachDB

Теперь DataGrip правильно подсвечивает запросы и показывает ошибки в скриптах для CockroachDB. Соответствующую интроспекцию мы сделаем в одном из следующих релизов.

Улучшения в окне соединения

Сделали это окно чуть более дружелюбным:

• Источники данных и драйверы разделены на две вкладки.

• На странице каждого драйвера появилась кнопка Create data source.

• Кнопка Test Connection переехала вниз теперь ее видно из всех вкладок, а не только из General и SSH/SSL.

• Для источников данных на основе файлов (так называемых DDL Data Sources) теперь можно явно задать диалект.

• Поле JDBC URL расширяется, потому что адреса для подключения бывают очень длинными.

Проводник базы данных

Легкое копирование источников данных

Возможность копировать и вставлять источники данных мы сделали давно. Но с этого релиза вы можете использовать самые знаменитые сочетания клавиш в мире Ctrl/Cmd+C/V/X.

• Напоминаем, что когда вы копируете источник данных, в буфер обмена сохраняется XML. Его можно послать коллеге в мессенджере, а он вставит его в свою IDE все сработает.

• Если копирование и вставка происходит в одном проекте, вам не понадобится заново вводить пароль.

• Источник данных можно не только копировать, но и вырезать. Вырезание отменяется при помощи Ctrl/Cmd+Z.

Новый интерфейс

Объекты второстепенной важности (роли, пространства имен, внешние источники и др.) мы поместили в папки Server Objects и Database Objects.

Если хотите чтобы было, как раньше, включите настройку Group Database and Schemas.

[Oracle] Скрытие сгенерированных объектов

Если отключить Show generated objects, то из проводника пропадут:

• Таблицы материализованных представлений

• Логи материализованных представлений

• Вторичные таблицы

[SQLite] Новые типы объектов

В SQLite теперь отображаются функции, модули и виртуальные столбцы.

Улучшения для неподдерживаемых баз

Шаблоны для источников данных

Начиная с этой версии стало легче создавать источники данных для баз, которые мы не поддерживаем. Вам не придется самим качать драйвер мы предложим сделать это за вас. Драйверы мы кладем к себе на сервер и обновляем, когда нужно. При создании источников данных ищите шаблоны в секции Other.

Еще раз напомним, что поддержка для таких источников данных базовая. Скрипты подсвечиваются на основе стандарта SQL:2016, а информация об объектах берется из драйвера.

Написание запросов

Инспекция про избыточные имена в CTE

Если запрос не запустится из-за избыточных имен в общем табличном выражении, DataGrip сообщит об этом.

[SQL Server] Системные функции можно использовать без имени схемы

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

Поддержка JSON Lines

Формат JSON Lines используется для хранения данных и логов. И новая версия правильно подсвечивает файлы этого формата.

Толщина шрифта

Теперь вы можете настраивать толщину шрифта.

Импорт / Экспорт

Незагруженные данные

Если бинарные данные не были загружены полностью, вы увидите такое сообщение:

Настройка, которая определяет, какое количество данных DataGrip загружает по умолчанию, находится здесь: Settings/Preferences | Database | Data Views | Maximum number of bytes loaded per value.

Запрос в файле Excel

Когда вы экспортируете данные в Excel, на отдельном листе сохраняется изначальный запрос.

First row is header в контекстном меню импорта

Раньше было неудобно находить эту настройку, поэтому мы добавили ее в контекстное меню заголовков.

Интерфейс

Прикрепление папки при помощи drag-n-drop

Прикрепить папку, то есть открыть ее в панели Files, теперь можно, перетащив её.

Открытие вкладок в режиме разделенного редактора

Если редактор разделен вертикально на несколько вкладок, вы можете расширить окно редактора для любой из вкладок, кликнув по ней дважды. Чтобы вернуться в прежнее состояние, снова кликните дважды по окну.

Длинные названия вкладок

В одной из предыдущих версий мы укоротили названия вкладок. Это не всем понравилось, поэтому мы сделали настройку:

На этом все!

Скачать триал на месяц

Фидбек принимаем в комментариях к посту и здесь:

Трекер (это если точно нашли проблему)

Телеграм-канал

Твиттер

Почта

Команда DataGrip

Однажды мне потребовалось забирать регулярно относительно большие объемы данных в MS SQL из PostgreSQL. Неожиданно выяснилось, что самый очевидный способ, через Linked Server на родные ODBC к PostgreSQL, очень медленный.

История вопроса

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

SET STATISTICS TIME ONDECLARE  @sql_str nvarchar(max)DROP TABLE IF EXISTS #tCREATE TABLE #t (  N int,  T datetime)SELECT @sql_str='  SELECT N, T  FROM generate_series(1,1000,1) N  CROSS JOIN generate_series($$2020-01-01$$::timestamp,    $$2020-12-31$$::timestamp, $$1 day$$::interval) T'INSERT #t (N, T)EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Такой простейший пример выборки всего 366 тысяч записей оказался жутко медленным:

SQL Server Execution Times:   CPU time = 8187 ms,  elapsed time = 14793 ms.

Решение

В первую очередь, захотелось исключить самый подозрительный элемент - ODBC. К тому времени MS уже предоставлял утилиту bcp для Linux. Поэтому bcp был установлен на сервер, где работал PostgreSQL и проведен следующий тест:

SET STATISTICS TIME ONDECLARE  @sql_str        nvarchar(max),  @proxy_account  sysname='proxy_account',  @proxy_password sysname='111111'DROP TABLE IF EXISTS ##tCREATE TABLE ##t (  N int,  T datetime)SELECT @sql_str='  COPY (    SELECT N, T    FROM generate_series(1,1000,1) N    CROSS JOIN generate_series($$2020-01-01$$::timestamp,      $$2020-12-31$$::timestamp, $$1 day$$::interval) T )  TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '    +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##t'' '    +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'','\')    +' -U '+@proxy_account+' -P '''    +@proxy_password+''' -c -b 10000000 -a 65535; '    +'rm $tmp_file $pgm$ NULL $nil$$nil$;'EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Результат сразу порадовал, причем сильно:

SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 881 ms.

Реализация

Не сложно заметить, что такой подход требует явного указания логина и пароля. Причем, bcp для Linux до сих пор не умеет авторизоваться через Kerberos. Поэтому использовать его можно только указывая кредентиалы в командной строке.

Вторая проблема в том, что в обычную временную таблицу bcp записать не может. Он ее просто не увидит. Значит нужно использовать постоянную таблицу или глобальную временную.

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

В связи с тем, что процессы на сервере могут запускаться асинхронно и одновременно, использовать фиксированное имя глобальной временной таблицы опасно. Но тут нас опять спасает динамический SQL.

Итоговое решение следующее:

DECLARE  @sql_str        nvarchar(max),  @proxy_account  sysname='proxy_account',  @proxy_password sysname='111111'SELECT @sql_str='  DROP TABLE IF EXISTS ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+'  CREATE TABLE ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+' (    N int,    T datetime  )'EXEC (@sql_str)SELECT @sql_str='  COPY (    SELECT N, T    FROM generate_series(1,1000,1) N    CROSS JOIN generate_series($$2020-01-01$$::timestamp,      $$2020-12-31$$::timestamp, $$1 day$$::interval) T )  TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '    +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##proxy_table_'''    +CONVERT(nvarchar(max),@@SPID)+' '    +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'\','\\')    +' -U '+@proxy_account+' -P '''    +@proxy_password+''' -c -b 10000000 -a 65535; '    +'rm $tmp_file $pgm$ NULL $nil$$nil$;'EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Пояснения

В PostgreSQL команда COPY может писать в файл или на стандартный ввод вызываемой ей программы. В данном случае вместо программы использован скрипт на sh. Вывод COPY, поступающий на стандартный ввод, записывается во временный файл с уникальным именем, форимруемым mktemp. К сожалению, bcp не умеет читать данные со стандартного ввода, поэтому приходится ему создавать файл.

Для совместимости формата, формируемого командой COPY и формата, ожидаемого bcp, обязательно следует указывать в COPY параметр NULL $nil$$nil$

Остальные параметры bcp:

• -c - символьный формат, так как бинарный формат PostgreSQL не совместим с бинарным форматом MS SQL и мы вынуждены использовать только символьный;

• -b - количество записей, вставляемых одной транзакцией. В моей конфигурации десять миллионов оказалось оптимальным значением. В иной конфигурации это число, скорее, может потребоваться уменьшить, чем увеличить;

• -a - размер пакета. В нашем случае лучше указывать сразу максимальный. Если сервер не поддерживает указанную длину пакета, то просто будет использована максимальная длина пакета, поддерживаемая сервером.

Если кто-то знает более быстрый способ получения данных на MS SQL из PostgreSQL - буду очень рад увидеть описание этого способа в комментариях.

Для будущих учащихся на курсе "MS SQL Server Developer" подготовили перевод статьи.

Также приглашаем посмотреть открытый вебинар на тему Графовые базы данных в SQL Server. На занятии участники вместе с экспертом рассмотрят, что такое графовые базы данных и какие есть варианты работы с графами и иерархиями в SQL Server.

Я думаю, что все уже знают мое мнение о MERGE и почему я держусь от него подальше. Но вот еще один антипаттерн, который я постоянно встречаю, когда требуется выполнить UPSERT (UPdate inSERT обновить строку, если она существует, и вставить, если ее нет):

IF EXISTS (SELECT 1 FROM dbo.t WHERE [key] = @key)BEGIN  UPDATE dbo.t SET val = @val WHERE [key] = @key;ENDELSEBEGIN  INSERT dbo.t([key], val) VALUES(@key, @val); END

Это выглядит довольно логично и соответствует тому, как мы об этом думаем:

• Существует ли строка для данного ключа (key)?

  • ДА: Обновляем эту строку.

  • НЕТ: Тогда добавляем ее.

Но это расточительно

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

Более того, если строка не существует, не используются явные транзакции и не учитывается уровень изоляции, то многое может пойти не так (помимо производительности):

• Если ключ существует и две сессии будут выполнять UPDATE одновременно, то они обе выполнятся успешно (одна "выиграет", а "проигравшая" получит "потерянное обновление"). Само по себе это не проблема, системы с параллелизмом так и работают. Здесь Пол Уайт (Paul White) рассказывает более подробно о внутренней механике, а здесь Мартин Смит (Martin Smith) о некоторых других нюансах.

• Если ключ не существует и обе сессии пройдут этап проверки существования ключа одинаково, то при попытке выполнить INSERT может произойти все что угодно:

  • взаимная блокировка (deadlock) из-за несовместимых блокировок;

  • нарушение ключа (key violation), которого не должно быть;

  • вставка повторяющихся значений ключа, если для столбца нет корректных ограничений.

Последний вариант самый плохой, так как данные могут быть испорчены. С взаимоблокировками и исключениями можно легко работать с помощью обработки ошибок, XACT_ABORT или повторных попыток, в зависимости от того, как часто вы ожидаете коллизии. Но если вы думаете, что при проверке IF EXISTS вы в безопасности и защищены от дубликатов (или ошибок ключей), то здесь вас ждет сюрприз. Если вы ожидаете, что столбец будет ключевым, то сделайте его официально таким и добавьте ограничения.

Многие люди говорят...

Ден Гузман (Dan Guzman) говорил о состоянии гонки более десяти лет назад в Conditional INSERT/UPDATE Race Condition, а затем в "UPSERT" Race Condition With MERGE.

Майкл Сварт (Michael Swart) также затронул эту тему несколько лет назад в Mythbusting: Concurrent Update/Insert Solutions, включая тот факт, что сохраняя исходную логику и только повышая уровень изоляции, нарушения ограничения ключа меняются на взаимные блокировки. Позже он написал про MERGE в статье Be Careful with the Merge Statement. Обязательно прочитайте все комментарии к обоим постам.

Решение

За свою карьеру я исправил множество взаимных блокировок, используя следующий паттерн (убираем избыточную проверку, оборачиваем все в транзакцию и защищаем доступ к первой таблице соответствующей блокировкой):

BEGIN TRANSACTION; UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key; IF @@ROWCOUNT = 0BEGIN  INSERT dbo.t([key], val) VALUES(@key, @val);END COMMIT TRANSACTION;

Зачем нужны два хинта? Разве UPDLOCK не достаточно?

• UPDLOCK используется для защиты от взаимоблокировок на уровне выражения (пусть другая сессия ждет вместо того, чтобы провоцировать жертву повторить попытку).

• SERIALIZABLE используется для защиты от изменений исходных данных на протяжении всей транзакции (для уверенности, что отсутствующая строка продолжает отсутствовать).

Здесь немного больше кода, но он на 1000% безопаснее. И даже в худшем случае (когда строка еще не существует) он будет работать не хуже рассматриваемого антипаттерна. А в лучшем случае, если вы обновляете уже существующую строку, эффективнее, так как поиск строки выполняется только один раз. Давайте опять изобразим операции, которые происходят в базе данных:

Получается следующее:

• Если ключ существует и две сессии пытаются одновременно его обновить, то они обе по очереди обновят строку успешно, как и раньше.

• Если ключ не существует, то одна из сессий выиграет и вставит строку. Другая сессия будет вынуждена ждать (даже для проверки строки на существование), пока не будут сняты блокировки и выполнит UPDATE.

В обоих случаях сессия, выигравшая гонку, теряет свои данные из-за того, что проигравшая обновит их после.

Обратите внимание, что общая пропускная способность в системе с высокой степенью параллелизма может пострадать. Но это компромисс, на который вы должны быть готовы пойти. Ряд разработчиков хотели бы, чтобы блокировок никогда не было, но некоторые из них абсолютно необходимы для обеспечения целостности данных.

Но что, если UPDATE менее вероятен?

Очевидно, что приведенное выше решение оптимизировано для UPDATE и предполагает присутствие ключа в таблице как минимум с той же вероятностью как и отсутствие. Если вам, наоборот, нужно оптимизировать INSERT, когда INSERT более вероятен, чем UPDATE, то вы можете перевернуть логику и все еще сохранить безопасность UPSERT:

BEGIN TRANSACTION; INSERT dbo.t([key], val)   SELECT @key, @val  WHERE NOT EXISTS  (    SELECT 1 FROM dbo.t WITH (UPDLOCK, SERIALIZABLE)      WHERE [key] = @key  ); IF @@ROWCOUNT = 0BEGIN  UPDATE dbo.t SET val = @val WHERE [key] = @key;END COMMIT TRANSACTION;

Здесь также есть подход просто сделай это, если вы вслепую выполните INSERT и позволите коллизиям вызвать исключения:

BEGIN TRANSACTION; BEGIN TRY  INSERT dbo.t([key], val) VALUES(@key, @val);END TRYBEGIN CATCH  UPDATE dbo.t SET val = @val WHERE [key] = @key;END CATCH COMMIT TRANSACTION;

Стоимость подобных исключений часто превышает стоимость проверки. Важно знать приблизительную частоту попаданий / промахов. Я писал об этом здесь и здесь.

А как насчет обработки нескольких строк?

Все вышесказанное относится к одиночным INSERT / UPDATE, но Джастин Пилинг (Justin Pealing) спросил, как быть с несколькими строками, когда неизвестно, какие из них уже существуют?

Если вы передаете список строк через что-то вроде табличного параметра (TVP, Table-Valued Parameters), то сделайте UPDATE с JOIN, а затем INSERT, используя NOT EXISTS. Подход в целом здесь остается таким же, как описано выше:

CREATE PROCEDURE dbo.UpsertTheThings    @tvp dbo.TableType READONLYASBEGIN  SET NOCOUNT ON;   BEGIN TRANSACTION;   UPDATE t WITH (UPDLOCK, SERIALIZABLE)     SET val = tvp.val  FROM dbo.t AS t  INNER JOIN @tvp AS tvp    ON t.[key] = tvp.[key];   INSERT dbo.t([key], val)    SELECT [key], val FROM @tvp AS tvp    WHERE NOT EXISTS (SELECT 1 FROM dbo.t WHERE [key] = tvp.[key]);   COMMIT TRANSACTION;END

Если вы получаете список строк каким-то другим способом, отличным от TVP (XML, список с разделителями-запятыми и т.п.), то сначала преобразуйте их в таблицу и потом сделайте JOIN к нужным данным. Будьте осторожны при оптимизации этого кода под первоначальный INSERT потенциально можно выполнить UPDATE для некоторых строк дважды.

Выводы

Рассмотренные UPSERT-паттерны лучше того, с чем мне часто приходится сталкиваться, и, я надеюсь, что вы начнете их использовать. Я буду давать ссылку на этот пост всякий раз, когда буду видеть паттерн IF EXIST. И еще хочу передать привет Полу Уайту (Paul White, sql.kiwi | @SQK_Kiwi) он так хорошо умеет объяснять сложные концепции простыми словами.

Для использования MERGE у вас должна быть либо веская причина (возможно, вам нужна какая-то маловразумительная MERGE-функциональность), либо вы не восприняли вышеприведенные ссылки всерьез.

Узнать подробнее о курсе "MS SQL Server Developer".

Смотреть открытый вебинар на тему Графовые базы данных в SQL Server.

Пару лет назад, в разговоре с кем-то промелькнула примерно такая фраза: "Мы используем In-Memory OLTP - это очень быстро, зачастую даже вместо временных таблиц создаём In-Memory и всем советуем". Спустя какое-то время, мне задали вопрос как можно держать одну таблицу в памяти, чтобы работать с ней максимально быстро. Выяснив подробности - небольшая таблица, данные должны храниться только за последние несколько минут, суммарно не больше 10000 записей "приемлемых" (не LOB) типов данных, потеря данных при перезагрузке/файловере не страшна и даже приветствуется. In-Memory OLTP, без тени сомнения ответил я.

Перед запуском в продакшн я излазил всю документацию, проводил свои тесты - просто огонь. Работает реально быстро, таблица SCHEMA_ONLY и IO не генерирует вообще (я же умный, смотрю sys.dm_io_virtual_file_stats до и после). С обращениями через natively compiled stored procedures - не просто быстро работает, летает. Одним словом мечта.

Правда, оказалось, что у моей мечты есть тёмная сторона.

Сочиняем мечту

Про In-Memory написано уже очень много всего, в т.ч. и на Хабре (1, 2). И во всех материалах, которые я встречал, написано:

SCHEMA_ONLY

This option ensures durability of the table schema. When SQL Server is restarted or a reconfiguration occurs in an Azure SQL Database, the table schema persists, but data in the table is lost. (This is unlike a table in tempdb, where both the table and its data are lost upon restart.) A typical scenario for creating a non-durable table is to store transient data, such as a staging table for an ETL process. A SCHEMA_ONLY durability avoids both transaction logging and checkpoint, which can significantly reduce I/O operations.

Жирненьким текст в цитате выделил я, потому что во всех источниках видел эту фразу в том или ином виде. Если мы создаём таблицу как SCHEMA_ONLY, её содержимое никоим образом не попадает на диск - данные не пишутся ни в журнал транзакций, ни при чекпоинтах.

Но так ли это на самом деле?

Воплощаем мечту

Я делаю всё на SQL Server 2017, но всё описанное будет одинаковым для любой версии SQL Server 2014-2019 (правда, на 2016 не проверял).

Итак, чтобы насладиться быстродействием In-Memory OLTP нам нужна база данных, в этой БД нужно создать файловую группу, которая CONTAINS MEMORY_OPTIMIZED_DATA и в этой файловой группу нужно создать файл (на самом деле каталог). Поехали

CREATE DATABASE hekaton;GOALTER DATABASE hekatonADD FILEGROUP hekaton CONTAINS MEMORY_OPTIMIZED_DATA;GOALTER DATABASE hekatonADD FILE (    NAME=hekaton_hekaton,     FILENAME='D:\SQLServer\DEFAULT\hekaton_hekaton') TO FILEGROUP hekaton;GOALTER DATABASE hekaton    SET RECOVERY FULL;GOBACKUP DATABASE hekaton TO DISK = 'NUL';GOUSE hekatonGOSELECT name, type_desc, size * 8 / 1024 AS sizeMB FROM sys.database_files;

Я перевожу БД в полную модель восстановления, потому что то, о чём я буду говорить дальше, наиболее очевидно проявляется в ней. Последний запрос выводит список файлов и их размеры:

Да, каюсь, я никогда не работал с FILESTREAM. Да, везде написано, что In-Memory файловая группа, по сути, FILESTREAM. Если вы работали, то, наверное, уже понимаете, от чего у меня полыхает. Если нет - давайте я покажу.

Убиваем мечту

Теперь переходим к тому, из-за чего всё затевалось, я создаю чудесную, молниеносную, волшебную In-Memory таблицу с DURABILITY = SCHEMA_ONLY и даже вставляю в неё одну запись:

CREATE TABLE hekaton (    id int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1500000))WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);GOINSERT INTO hekaton (id) VALUES (1);GO

Ну и посмотрим, что у нас вставилось и сколько теперь файлы занимают на диске:

SELECT * FROM hekaton;SELECT name, type_desc, size * 8 / 1024 AS sizeMB FROM sys.database_files;

Воу, 105 мегабайт на диске для хранения 1 записи? Ну, ладно, я, в общем, готов с этим смириться - ведь больше расти не будет, правильно? Нет, не правильно. Для демонстрации я запущу вот это:

CHECKPOINT;GO 10SELECT name, type_desc, size * 8 / 1024 AS sizeMB FROM sys.database_files;

Господа, делайте ваши ставки! Что произойдёт с размером файлов после выполнения десяти чекпоинтов? Я бы зуб поставил, что ничего (и хорошо, что не поставил):

Но ведь это же не может быть правдой? У меня SCHEMA_ONLY-таблица с одной записью! Так, ладно. Насколько же глубока кроличья нора? В документации сказано, что нужно планировать место в размере размер in-memory таблицы * 4, где-то я видел, что нужно обеспечить свободное место в объём ОЗУ, доступного SQL Server, * 4. Давайте попробуем.

EXEC sp_configure 'show advanced options', 1RECONFIGUREGOEXEC sp_configure 'max server memory (MB)', 512RECONFIGUREGOEXEC sp_configure 'max server memory (MB)'

CHECKPOINT;GO 100SELECT name, type_desc, size * 8 / 1024 AS sizeMB FROM sys.database_files;

Ваши ставки?

Такие дела. In-Memory таблица с одной записью (которая даже не изменяется), оказывается может занимать на диске столько места, сколько доступно на этом диске.

Срываем покровы

Вам, наверняка интересно почему так получается. На самом деле, мне тоже интересно, почему так получается со SCHEMA_ONLY-таблицей. В целом, на msdn есть отличная статья про Durability Memory-Optimized таблиц (а если вам нужно детально разобраться с тем как устроен In-Memory OLTP, есть отличный whitepapper от Kalen Delaney). И, справедливости ради, нигде не написано, что для SCHEMA_ONLY таблиц поведение будет отличаться.

Суть в том, что для обеспечения Durability, SQL Server создаёт и хранит Data и Delta файлы для одной или нескольких таблиц. Оба файла append-only и есть специальный background-процесс, который отвечает за запись в них. Периодически эти файлы мёржатся, а "старые", "ненужные" уже Data и Delta файлы удаляются специальным сборщиком мусора. Исходя из цитаты в начале (ну и если наблюдать за sys.dm_db_xtp_checkpoint_files) - SQL Server ничего не пишет в журнал транзакций и при чекпоинтах, но всё равно создаёт эти файлы при каждом CHECKPOINT'е! Причём, если у вас больше 16 ГБ ОЗУ - файлы будут по 128 МБ, если меньше (как у меня сейчас) - по 16 МБ. Ну а в каких-то случаях, начиная с SQL Server 2016, могут использоваться "large checkpoint" по 1 ГБ!

Ключевая, для меня, фраза в статье (хотя, на мой взгляд, она не передаёт все нюансы):

CFPs transition through several states before they can be deallocated. Database checkpoints and log backups need to happen to transition the files through the phases, and ultimately clean up files that are no longer needed.

Исходя из написнного здесь (там про FILESTREAM) и собственного горького опыта, речь не столько о самих бэкапах и чекпоинтах (ВАЖНО! Не делайте чекпоинты без бэкапа журнала транзакций в полной модели восстановления, чтобы вызвать сборщик мусора - ситуация будет становиться только хуже), сколько о том, что для того, чтобы файлы могли совершить "transition through the phases" нужно чтобы тот VLF, в котором они были созданы, был помечен как неактивный.

Ну ладно, скажете вы, это же ерунда - бэкапы журнала транзакций в полной модели восстановления у вас снимаются регулярно, а в простой модели восстановления и волноваться не о чем. И я скажу вам да, но нет.

Давайте посмотрим как это выглядит:

ALTER DATABASE hekaton    SET RECOVERY SIMPLE;GOBACKUP DATABASE hekaton TO DISK = 'NUL';GOCHECKPOINT;GOSELECT name, type_desc, size * 8 / 1024 AS sizeMB FROM sys.database_files;

Повторим эксперимент. В одной сессии откроем транзакцию:

CREATE TABLE foo (bar int);GOBEGIN TRANINSERT INTO foo (bar) VALUES (1);

А во второй выполним:

CHECKPOINT;GO 30SELECT name, type_desc, size * 8 / 1024 AS sizeMB FROM sys.database_files;

Ой, в простой модели восстановления файловая группа для In-Memory продолжает расти, если что-то мешает "переводу" нужного VLF в статус "неактивный". Это может быть незакрытая транзакция, репликация, какой-нибудь REBUILD индексов - да много можно чего придумать.

В полной модели восстановления у вас, помимо перечисленного, может быть настроена Availability Group.

DANGER! Availability Group

А что не так с Availability Group? С ней всё так, не считая того, что она использует специальный механизм, который не отмечает забэкапленные VLF как неактивные. Почему? Потому что Microsoft заботится о нас. Потому что у нас может отвалиться реплика, а что же ей потом делать, если пока она была офлайн, прошёл бэкап журнала транзакций? Вот на этот случай, SQL Server и не отмечает неактивными VLF даже после бэкапа журнала транзакций (предполагаю, что тоже самое может касаться Database Mirroring). При этом для процесса, пишущего в журнал транзакций, нет проблемы понять какие "активные" VLF можно переиспользовать, а какие нет. Более подробно про Availability Group и FILESTREAM.

Честно говоря, даже удивительно, что какие-то механизмы могут понимать, что VLF, отмеченный как активный, на самом деле неактивный, а In-Memory OLTP не может. Но благодаря этому, моя чудесная SCHEMA_ONLY-таблица из 10 тысяч строк, о которой я говорил в начале, в нормальной жизни занимает порядка 2 МБ в памяти и 4 ГБ на диске, а во время "окна для обслуживания" может занимать на диске в десятки раз больше.

А это правда проблема?

It depends.

С одной стороны - проблема не самая большая. По сути, без Availability Group, проблема возникает только в том случае, когда за промежуток между бэкапами журнала транзакций, выполняется столько чекпоинтов, что файловая группа вырастает настолько, что это становится проблемой. В каких ситуациях это возможно? Наиболее вероятный вариант, кмк - это "обслуживание индексов". Документация говорит:

For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint. This 1.5 GB size includes transaction log records for both disk-based and memory-optimized tables.

Плюс, у нас есть recovery interval и target recovery time, которые тоже влияют на частоту чекпоинтов. Будет ли это проблемой для вас - я не знаю. В любом случае - заводя memory-optimized table (даже с DURABILITY = SCHEMA_ONLY) в большой БД, стоит начать обращать на это внимание и отслеживать чекпоинты. Availability Group значительно усугубляет ситуацию.

Нужно понимать, что в полной мере проблема с ростом in-memory файловой группы проявится (ну или наконец-таки станет проблемой) именно тогда, когда у вас и без того будет куча проблем. Вырастает журнал транзакций - железно вместе с ним вырастает In-memory файловая группа и если места на диске впритык - вас ждут незабываемые моменты. И нужно помнить, что эту файловую группу нельзя ограничить в размере, потому что если она уткнётся в этот лимит, БД перейдёт в состояние SUSPECT.

Решение?

У меня нет работающего "production-ready" решения.

В любой версии SQL Server Memory-Optimized таблицы могут занимать на диске на порядки больше места, чем в памяти, и могут занять всё свободное место вне зависимости от модели восстановления БД.

Единственное решение, которое помогает, в случае, когда место на диске приближается к концу - подрезать журнал транзакций, физически убирая, тем самым, часть VLF, чтобы сборщик мусора мог удалить лишние файлы.

Принимая решение об использовании In-Memory OLTP в промышленном окружении нужно обязательно учитывать, что:

1. Файловую группу In-Memory нельзя удалить из БД, она останется с вами навсегда.

2. Файловая группа In-Memory, при совпадении ряда условий, может быстро и значительно расти.

3. Таблицы, объявленные как SCHEMA_ONLY, не генерируют IO и не используют Data и Delta файлы на диске, но эти файлы для них создаются при каждом CHECKPOINT'е.

4. При использовании Availability Group, файловая группа In-Memory будет гораздо больше, чем в точно такой же БД, не входящей в Availability Group.

Мой Дорогой Друг Шон недавно написал пост, рассказывающий о том, как люди неправильно обслуживают индексы. Я собираюсь пойти немного дальше и поговорить о том, что сам метод, которым ваш скрипт, обслуживающий индексы, оценивает фрагментацию индексов - неправильный.

Если вы посмотрите на то, как ваш скрипт решает нужно или нет перестраивать индексы, и тоже самое касается планов обслуживания (я запускал ПРОФАЙЛЕР ДА ЗДРАВСТВУЕТ ПРОФАЙЛЕР ВПЕРЁД ПРОФАЙЛЕР чтобы проверить), вы увидите, что они выполняют запрос к sys.dm_db_index_physical_stats.

Все эти запросы используют столбец avg_fragmentation_in_percent, чтобы понять - нужно ли перестроить индекс. Документация (по ссылке выше) про этот столбец имеет сказать следующее:

Это мера логической фрагментации. Логическая фрагментация - это когда страницы "перемешаны" на диске.

Если вы используете приличные диски, даже на SAN, или у вас нормальный объём ОЗУ, вы можете понять из Великого Поста Шона, что фрагментация - это не самая худшая участь, которая может выпасть на долю ваших индексов. Если вы обслуживаете статистику, всё будет в порядке.

Кэши рулят

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

Вы могли бы сделать это с помощью столбца avg_page_space_used_in_percent.

НО...

Ваше любимое решение по обслуживанию индексов позаботится о вас и запустит, по умолчанию, dm_db_index_physical_stats в режиме LIMITED. Это всё потому что более подробные измерения могут быть очень тяжёлыми на сервере, где хранится множество данных, и, блин, даже LIMITED может выполняться очень долго.

Но, если бы я собирался принять решение о том нужно ли перестраивать индекс, это именно та метрика, которую я бы хотел использовать. Потому что этого неиспользуемого пространства может быть очень много.

Штука в том, что между avg_fragmentation_in_percent и avg_page_space_used_in_percent, нет особой корреляции.

Локальная БД

Посмотрим на фрагментацию в моей локальной БД Stack Overflow 2013:

Обе таблицы достаточно фрагментированы, чтобы привлечь внимание обслуживающего скрипта, но перестройка индекса, на самом деле, помогает только таблице Posts, несмотря на то, что мы перестроили оба.

В таблице Comments, avg_page_space_used_in_percent слегка уменьшается, а в Posts становится лучше примерно на 10%.

Количество страниц для Comments не изменяется, но уменьшается примерно на 500 тысяч для Posts.

Вот это то, что мне нравится. Я был бы рад читать на 500 тысяч меньше страниц при сканировании таблицы целиком.

Но при этом, я вообще-то не хочу сканировать целиком таблицу, если говорить не об отчётах или хранилищах данных.

Если мы говорим об OLTP, мы обычно избегаем сканирования больших таблиц и чтобы этого добиться, мы создаём некластерные индексы, которые помогают искать данные эффективно, и пишем запросы с чёткими условиями, которые обеспечивают эффективное использование этих индексов.

Правильно?
Правильно

Подумайте о настройках обслуживания индексов

Вероятно они на стандартных 5% и 30% для реорганизации и перестроения. Дело не только в том, что они абсурдно низкие, но и в том, что они даже измеряют не тот тип фрагментации. Даже при 84% "фрагментации" мы видели страницы, заполненные на 75%.

Это не идеально, но едва ли это катастрофа.

Да вы возможно размышляли о том, чтобы установить fill factor ещё меньше, чтобы избежать фрагментации.

Что ещё хуже, вы, вероятно, смотрите все таблицы > 1000 страниц, т.е. примерно 8МБ. Но если у вас проблемы с тем, чтобы прочитать и удержать в памяти 8 мегабайт - может пора сгонять в магазин?

Спасибо, что прочитали!

Примечание переводчика

Тема достаточно холиварная. Erik Darling и Brent Ozar достаточно давно относятся к той группе, которая топит за то, что, в общем случае, индексам не нужно обслуживание. В противовес им можно поискать посты Paul S. Randal и Paul White, которые наоборот считают, что индексы нужно регулярно обслуживать.

На Хабре не нашёл постов/переводов, представляющих такую точку зрения, поэтому решил сделать сам. Ну и интересно, как обслуживают индексы dba на Хабре - принимайте участие в опросе.

Для будущих учащихся на курсе "MS SQL Server Developer" преподаватель и эксперт по базам данных Евгений Туркестанов подготовил полезную статью.

Приглашаем также на открытый вебинар по теме Polybase: жизнь до и после. На занятии участники вместе с экспертом рассмотрят, как можно было взаимодействовать с другими базами данных до Polybase, и как это работает сейчас.

При всем моем двадцатилетнем опыте работы с MS SQL Server и SSIS (когда-то еще DTS), никогда не любил хранимые процедуры с возвращаемым значением. Не знаю, почему так сложилось. Может быть потому, что хранимки чаще приходилось использовать для реализации какой-то логики или возвращении набора записей, а для получения одного значения применял функции. Ну, так вот сложилось. Подразумеваю, что нелюбовь эта взаимная, что подтвердилось в последнем проекте, где хоть убей, но надо было с SSIS присваивать переменным возвращаемые значения процедуры. Изначально, пакет был не мой, а другого разработчика. Ничего плохого говорить не буду, все было сделано достаточно грамотно.

Процедура возвращала два значения типа DATETIME. В процессе работы я наступил на некоторые грабли, что и сподвигнуло на написание этой статьи.

Итак, дано:

1. Пакет SSIS

2. Переменные пакета:

Пакет, естественно, привожу тестовый, но сделанный по аналогу. Извините, но оригинальный, к сожалению, показать не могу.

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

Как видно, ничего сложного тут нет, даже слишком все просто. Можно было, в принципе, не делать возвращаемые параметры, а просто вернуть значения. Но, хозяин барин, что есть, то есть. Если мы запустим процедуру, то результат будет такой:

Теперь наш пакет.

Берем Execute SQL Command задание, настройки:

Как видим, возвращающий результат не выбран, так как у нас возвращаемые параметры. В выражение SQL ставим нашу процедуру:

И теперь назначаем параметры. Здесь самое интересное.

Для возвращаемых параметров выбираем Output и переменные пакета. Вопрос какой тип данных для этих параметров мы должны выбрать?

Для проверки того, что будет возвращаться, я создал скрипт задание, который будет показывать C# MessageBox со значением переменных. Настройки и код такие:

Вернемся к нашим параметрам, вернее, к их типу. Какой тип выбрать? У возвращаемых параметров процедуры тип DATETIME. Посмотрим, что предлагает SSIS.

SSIS package "F:\Projects\SSIS\TestMultiplePutput\TestMultiplePutput\TestMultiplePutput.dtsx" starting.Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query " exec dbo.testMultipleOutput ?, ? OUTPUT, ? OUTPUT" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Execute SQL TaskWarning: 0x80019002 at TestMultiplePutput: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.SSIS package "F:\Projects\SSIS\TestMultiplePutput\TestMultiplePutput\TestMultiplePutput.dtsx" finished: Failure.The program '[50800] DtsDebugHost.exe: DTS' has exited with code 0 (0x0)

Интересно. Причем ошибка вылезает из COM компонента проблемы с запросом, не настроен результат или параметры. Но, как мы видели, процедура отрабатывает. Если выражение просто скопировать в SSMS, поменять параметры и запустить, все работает. Честно говоря, ошибка меня заставила потерять какое-то время, но вменяемого ответа почему так происходит, я не нашел. Возможно, здесь происходит ошибка конвертации DATETIME в DATE. Причем, это происходило только, если я использовал OUTPUT параметры в компоненте.

Сам пакет выглядит таким образом:

Все настолько просто, что даже скучно. Смотрим дальше.

Выбираю DBDATE, сохраняю, запускаю. Как и следовало ожидать, вернулась дата.

DBTIME выбирать смысла нет, поэтому идем дальше. Хотя я все-таки попробовал. Вернулось ожидаемое время, но с сегодняшней датой. То же самое произошло и с DBTIME2. Осталось еще пара типов.

DBTIMESTAMP. Кто-то мне говорил, что не стоит с этим типом работать в SSIS. Дескать, неправильно отображает дату и время, так как это не совсем DATETIME. Сейчас мы это увидим. Вернулось то, что и ожидалось:

Если выбрать, ради эксперимента, последний временной тип DBTIMESTAMPOFFSET, который, в принципе, предназначен для работы с временными зонами, но чем черт не шутит, то он вернет правильную дату, но другое время:

Есть еще один вариант работы с возвращаемыми параметрами, но это, скажем, на любителя или для тех, кто сильно, как и я их не любит. В Execute SQL Command в тексте можно написать выражение T-SQL, примерно такое:

И настроить возвращаемый результат

И определить переменные для присвоения значений

Как говорится, дело вкуса. По производительности разницы нет, по стилю и самому решению, мне, даже при всей нелюбви к возвращаемым параметрам в пакете, больше импонирует первый вариант.

Узнать подробнее о курсе "MS SQL Server Developer".

Смотреть открытый вебинар по теме Polybase: жизнь до и после.