Добрый день! В процессе развития проекта приходится сталкиваться с проблемой производительности баз данных, т.к. объём данных растёт, и волей неволей выплывают проблемные участки.

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

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

Select в результирующих полях

Многие разработчики при написании запросов применяют следующую конструкцию

SELECT    T.Column1,   (SELECT MAX(Column) FROM Table2) as Column3,   T.Column2 FROM Table1 T 

Дело в том, что на каждую строку из таблицы Table1 производится выборка «SELECT MAX(Column) FROM Table2», что требует дополнительных ресурсов для новой выборки. В данном случае производительность будет падать как при увеличении количества данных в Table1 так и Table2.

Приемлемый вариант.

SELECT    T.Column1,   T2.Column3,   T.Column2 FROM Table1 T   JOIN (SELECT Max(Column) as Column3 FROM Table2) T2 ON 1 = 1 

В таком случае выборка из таблицы Table2 выполнится один раз, и результат подставится для каждой строки из таблицы Table1.

Замена Left Join на Union

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

SELECT   M.Id, COALESCE(P1.Sum, 0) + COALESCE(P2.Sum, 0) + COALESCE(P3.Sum, 0) + ... + COALESCE(PN.Sum, 0) as Sum FROM MainTable M   LEFT JOIN PayTable1 P1 ON P1.Id = M.Id   LEFT JOIN PayTable2 P2 ON P2.Id = M.Id   LEFT JOIN PayTable3 P3 ON P3.Id = M.Id   .   .   .   LEFT JOIN PayTableN PN ON PN.Id = M.Id 

У данного подхода несколько проблем:
1. Производятся, перемножения таблиц PayTableN и с увеличением таблиц производительность будет падать, даже если данных немного.
2. Необходимость использования LEFT JOIN, что во многих СУБД не работают индексы.

Повысить производительность можно путём отказа от операции перемножения в пользу объединения и использования JOIN вместо LEFT JOIN.

SELECT R.Id, SUM(R.Sum) as Sum FROM   (   SELECT M.Id, P1.Sum   FROM MainTable M     JOIN PayTable1 P1 ON P1.Id = M.Id   UNION   SELECT M.Id, P2.Sum   FROM MainTable M     JOIN PayTable2 P2 ON P2.Id = M.Id   UNION   SELECT M.Id, P3.Sum   FROM MainTable M     JOIN PayTable3 P3 ON P3.Id = M.Id     .     .     .   UNION   SELECT M.Id, PN.Sum   FROM MainTable M     JOIN PayTableN PN ON PN.Id = M.Id   ) R GROUP BY R.Id 

К тому же такую конструкцию проще сопровождать, т.е. при добавлении новых таблиц PayTableN нужно добавить соответствующий блок только в одном месть.

Использование скалярных операций в условии объединения Join

Есть ситуации, когда в условии объединения необходимо использовать дополнительные операции над полем, в таком случае индекс не используется

SELECT   T1.*, T2.* FROM Table1 T1   JOIN Table2 T2 ON T2.Id = T1.Id AND COALESCE(T2.Column, 0) = COALESCE(T1.Column, 0) 

Возможные варианты решения:
1. Вынести в условие WHERE, но этот вариант не всегда даст положительный результат, т.к. за вас это мог сделать оптимизатор.

SELECT   T1.*, T2.* FROM Table1 T1   JOIN Table2 T2 ON T2.Id = T1.Id  WHERE COALESCE(T2.Column, 0) = COALESCE(T1.Column, 0) 

2. Разделить запрос на два.

SELECT   T1.*, T2.* FROM Table1 T1   JOIN Table2 T2 ON T2.Id = T1.Id AND T2.Column = T1.Column UNION SELECT   T1.*, T2.* FROM Table1 T1   JOIN Table2 T2 ON T2.Id = T1.Id AND T2.Column IS NULL AND T1.Column IS NULL 

Запросы с использованием With

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

WITH   MainSubQuery AS (     SELECT * FROM Table1   ),    SubQuery AS (     --Здесь на самом деле, какой либо сложный запрос, требующий больших ресурсов     SELECT * FROM Table2   )    SELECT *   FROM MainSubQuery M     LEFT JOIN SubQuery Q1 ON Q1.id = M.id AND Q1.Param = 1     LEFT JOIN SubQuery Q2 ON Q2.id = M.id AND Q2.Param = 2     LEFT JOIN SubQuery Q3 ON Q3.id = M.id AND Q3.Param = 3     LEFT JOIN SubQuery Q4 ON Q4.id = M.id AND Q4.Param = 4 

Проблема в том, что многие думают, что подзапрос SubQuery выполнится один раз, и далее будет браться только результат и подставляется там, где он далее потребуется.
Но на деле получается следующее:

SELECT * FROM (SELECT * FROM Table1) M   LEFT JOIN (SELECT * FROM Table2) Q1 ON Q1.id = M.id AND Q1.Param = 1   LEFT JOIN (SELECT * FROM Table2) Q2 ON Q2.id = M.id AND Q2.Param = 2   LEFT JOIN (SELECT * FROM Table2) Q3 ON Q3.id = M.id AND Q3.Param = 3   LEFT JOIN (SELECT * FROM Table2) Q4 ON Q4.id = M.id AND Q4.Param = 4 

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

INSERT Session.MainSubQuery   SELECT * FROM Table1;  INSERT Session.SubQuery   SELECT * FROM Table2;  SELECT * FROM Session.MainSubQuery M   LEFT JOIN Session.SubQuery Q1 ON Q1.id = M.id AND Q1.Param = 1   LEFT JOIN Session.SubQuery Q2 ON Q2.id = M.id AND Q2.Param = 2   LEFT JOIN Session.SubQuery Q3 ON Q3.id = M.id AND Q3.Param = 3   LEFT JOIN Session.SubQuery Q4 ON Q4.id = M.id AND Q4.Param = 4; 

Сопровождение

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

В процессе развития проекта приходится пересоздавать процедуры, функции, таблицы и другие объекты баз данных, но
не в каждой СУБД есть конструкция CREATE OR REPLACE PROCEDURE[FUNCTION, TABLE, VIEW, TRIGER, …] и приходится
в обновлениях постоянно контролировать DROP-ы, ALTER-ы и т.д. в каждой СУБД по разному.

Решением данного вопроса может стать ввод собственных сервисных процедур, например:

CALL[EXECUTE] DropProcedure(SchemaName, ProcedureName) CALL[EXECUTE] DropTable(TableName, TableName) CALL[EXECUTE] DropView(SchemaView, ProcedureView) 

и т.д.

А далее уже производим создание соответствующих объектов не зависимо от того созданы были ранее или нет.

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

Решения могут быть разные, например:
Поддерживать скрипт выдачи прав в актуальном состоянии и загружать после каждого обновления (неудобно).
Вообще не использовать групповую политику (нехорошо).

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

--Сохранение всех прав CALL[EXECUTE] StoreRights  --Удаление и создание объектов  --Восстановление прав CALL[EXECUTE] RestoreRights 

Следующий вопрос касается сопровождения структуры БД СУБД MS SQL, а именно в связи с крайне неудобным методом комментирования таблиц, колонок. Что в свою очередь делается редко либо совсем не делается.

Пример:

  EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Идентификатор' , @level0type=N'SCHEMA',@level0name=N'main', @level1type=N'TABLE',@level1name=N'TextTable', @level2type=N'COLUMN',@level2name=N'id'; 

Также можно создать набор сервисных процедуру обёрток

  EXEC Service.CommentOnTable N'SchemaName', N'TableName', N'TableComment';   EXEC Service.CommentOnColumn N'SchemaName', N'TableName', N'ColumnName', N'ColumnComment';   EXEC Service.CommentOnProcedure N'SchemaName', N'TableName', N'ProcedureComment'; 

Так уже короче, информативней и приятней работать.

Этим набор сервисных процедур, конечно, не ограничивается, можно много чего придумать.

Всего доброго приятной работы!