Решения разработки баз данных

[lair]

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

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

Например, в MS SQL реальные планы запросов у обоих ваших вариантов (что со вложенным SELECT, что с JOIN) — одинаковые.

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

Это в каких же?

Но вообще, при выборе между UNION и LEFT OUTER JOIN нужно в первую очередь руководствоваться семантикой таблиц и запроса.

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

Для INNER JOIN в MS SQL эти две записи эквивалентны и трактуются оптимизатором строго одинаково. Для OUTER JOIN их семантика и поведение отличается, поэтому одно не может быть заменой другого.

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

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

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

Стоимость создания/удаления временной таблицы вы не учитываете? А то, что в ней нет индексов? А почему не упоминаются таблицы в памяти?

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

Использованием адекватных инструментов.

приходится в обновлениях постоянно контролировать DROP-ы, ALTER-ы и т.д. в каждой СУБД по разному.

Проблема надуманна. В каждой СУБД вообще есть свои нюансы работы, их все равно надо учитывать, на этом фоне разница синтаксиса ALTER несущественна. А вот замусоривать каждый сервер сервисными процедурами — зло.

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

Так вы для всех СУБД пишете, или только для MS SQL?

[xzilgiz]

С первой частью поста конечно можно спорить вечно т.к. в каждой СУБД, есть свои нюансы, умные и не очень оптимизаторы. Да и писалась она не как 100% решения, а как возможные альтернативы решения проблем.

Стоимость создания/удаления временной таблицы вы не учитываете?

там было сказано, что

результат тяжелого запроса

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

А то, что в ней нет индексов?

для временной таблицы всегда можно создать индекс даже в MSSQL.

Вторая часть поста связанная с сопровождением.

Использованием адекватных инструментов

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

По поводу сервисных процедур, на мой взгляд запись вида

EXEC DropProcedure('SchemaName', 'ProcedureName1');
EXEC DropProcedure('SchemaName', 'ProcedureName2');
EXEC DropProcedure('SchemaName', 'ProcedureName3');

смотрится намного информативней и короче, чем

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'ProcedureName1')
  DROP PROCEDURE ProcedureName1;
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'ProcedureName2')
  DROP PROCEDURE ProcedureName2;
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'ProcedureName3')
  DROP PROCEDURE ProcedureName3;

А по поводу комментирования таблиц и процедур мой вариант очевидно лучше, чем предлагает MSSQL, для сравнения в других СУБД это делается проще

COMMENT ON TABLE  Schema.Table IS 'Комментарий таблицы';
COMMENT ON COLUMN Schema.Table.Column IS 'Комментарий колонки';
COMMENT ON PROCEDURE Schema.ProcedureName IS 'Комментарий процедуры';

[lair]

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

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

для временной таблицы всегда можно создать индекс даже в MSSQL.

… и еще накладные расходы.

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

Все зависит от ваших задач.

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

самописного

NIH?

По поводу сервисных процедур, на мой взгляд запись вида [...] смотрится намного информативней и короче, чем [...]

Во-первых, это происходит ценой замусоривания серверов сервисными процедурами. Во-вторых, в вашем варианте теряется возможность контроля корректности скрипта силами СУБД (в частности, указания, что процедуры нет) и автодополнения силами IDE. Ну и в-третьих, то, что вы делаете IF EXISTS (руками!), указывает на то, что вы неправильно контролируете состояние СУБД.

по поводу комментирования таблиц и процедур мой вариант очевидно лучше, чем предлагает MSSQL,

В MS SQL комментирование вообще сделано криво, и пользоваться им as is смысла нет. Либо вы используете дизайнер, и вас не интересуют все эти километры кода, либо вы все равно его не видите в нужные моменты.

[pihel]

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

[lair]

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

[xzilgiz]

NIH?, обёртки, IDE

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

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

[lair]

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

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

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

А, так вы из тех, кто держит бизнес-логику в БД. Это объясняет и ваши проблемы, и ваш подход.

Но в общем случае цена подхода «непонятна цена замусоривания полусотней» — это полторы тысячи процедур. Просто потому, что для одной задачи полсотни сервисных процедур, для другой, для третьей — вот и набралось.

[pihel]

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

Кстати, ORACLE, в данном случае, чаще всего сам создает временную таблицу, ну или ему можно подсказать хинтом /*+ materialize */ и преимущество использования WITH будет очевидно.

[maovrn]

  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 схлопывает дублирующиеся строчки. Поэтому если для одного M.Id суммы совпадут (P1.Sum = P2.Sum), строки схлопнутся и в итоге вместо двух сложений останется только одно. Нужно использовать UNION ALL.
Ну и вообще непонятно зачем в подзапросах соединяться с MainTable — оттуда все-равно ничего не выбирается.

[xzilgiz]

Согласен баг UNION, очень хорошее замечание, он даже позволил одной компании сэкономить пол миллиона. А MainTable на самом деле служит ограничивающей выборкой, не всегда нужно брать сумму по всем id, может даже и по одному конкретному оппоненту.

[zBit]

В запросе

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

Очень необычное решение ON 1 = 1. С таким условием у вас T2.Column3 будет всегда одинаковым. Впрочем в «не кошерном» варианте ситуация такая же. А в MS SQL нельзя вот так?

SELECT 
  T.Column1,
  MAX(T2.Column) AS Column3,
  T.Column2
FROM Table1 T
  JOIN Table2 T2 ON T2.T_id = T.id
GROUP BY T.Column1, T.Column2

Если так можно, то именно это решение будет являться наиболее приемлемым. ИМХО!

[lair]

Предложенное вами решение — от другой задачи. И так в MS SQL можно, просто результат будет не тот, который нужен.

[zBit]

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

[lair]

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

[xzilgiz]

Пример приближенный к реальности

SELECT 
  --Шапка отчёта
  --где UserInfo вьюха, которая выдаёт данные о пользователе который запускает запрос
  (SELECT FIO FROM UserInfo) as FIO,
  (SELECT PhoneNumber FROM UserInfo) as PhoneNumber,
  (SELECT Address FROM UserInfo) as Address,
  --Данные отчёта
  T.Column1, T.Column2, T.Column3
FROM Table1 T

преобразуем

SELECT U.FIO, U.PhoneNumber, U.Address, T.Column1, T.Column2, T.Column3
FROM Table1 T
  JOIN UserInfo U ON 1 = 1

ON 1 = 1 эквивалентно

SELECT U.FIO, U.PhoneNumber, U.Address, T.Column1, T.Column2, T.Column3
FROM Table1 T, UserInfo U

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

[lair]

Мда… «в реальности» данные о пользователе, который запускает запрос, выводятся сразу на клиенте, зачем сервер-то мучать?

(не говоря уже о том, откуда вы вообще собираетесь брать эти данные на стороне SQL)

[xzilgiz]

Пользователь заходит со своим логином, на сервере есть таблица с информацией о пользователе.
Клиент тонкий, его цель в данном случае:
— отдать идентификатор отчёта и какие либо параметры, если требуется
— принять результат запроса и шаблон
— отдать в FastReport
Отчётных форм тысячи, и контролировать их на клиенте проблематично

[lair]

на сервере есть таблица с информацией о пользователе.

И как там определяется информация о текущем пользователе?

Клиент тонкий, его цель в данном случае:
— отдать идентификатор отчёта и какие либо параметры, если требуется

Вот и передавайте информацию о пользователе в виде параметров.

Отчётных форм тысячи, и контролировать их на клиенте проблематично

Не надо контролировать отчетные формы, контролируйте контекстную информацию отдельно от прикладных данных из СУБД.

[xzilgiz]

Стандартная функция, для MS SQL это SUSER_NAME(), для IBM DB2 USER()

[lair]

И у каждого пользователя свой логин в БД? Со всеми прелестями управления десятком тысяч пользователей в MS SQL, сквозной аутентификацией и так далее?

[xzilgiz]

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

[lair]

Для фиксации изменений не обязательно прокидывать логин в SQL Server. Зато проблемы в поддержке совершенно невыносимые (я, собственно, выше описал часть).

[xzilgiz]

Не, я имею ввиду фиксацию информации о том, «кто, когда, что на что изменил, что добавил и удалил»

[lair]

Я тоже имею в виду это, и эта задача прекрасно решается без сквозной аутентификации (более того, без сквозной аутентификации ее можно сделать едва ли не лучше).

[xzilgiz]

Т.е. есть какая-то особая связь между Change Data Capture и как я понимаю виндовой аутентификацией? или в таблицу всё равно придётся добавить поле с информацией, кто трогал?

Сразу оговорюсь мой вариант напоминает схему Change Data Capture и был разработан для IBM DB2 8.2 задолго до появления MS SQL 2008 и IBM DB2 10(там тоже есть такое).

[lair]

CDC тут вообще не при чем. Информация о том, кто и какие данные менял, хранится в соответствии с архитектурой системы (можно в той же таблице, можно в другой таблице в той же БД, можно вообще на другом сервере — никаких ограничений).

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

[servekon]

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

[xzilgiz]

информацию можно формировать на клиенте

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

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

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

[lair]

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

Т.е. требование журналирования изменений — это у вас не бизнес-требование? Очень странно.

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

Таких «специалистов» надо всячески избегать.

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

[zBit]

Смею заметить, что

SELECT U.FIO, U.PhoneNumber, U.Address, T.Column1, T.Column2, T.Column3
FROM Table1 T
  JOIN UserInfo U ON 1 = 1

не эквивалентно

SELECT 
  (SELECT FIO FROM UserInfo) as FIO,
  (SELECT PhoneNumber FROM UserInfo) as PhoneNumber,
  (SELECT Address FROM UserInfo) as Address,
  T.Column1, T.Column2, T.Column3
FROM Table1 T

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

Вложенные запросы — это плохо. Запросы в JOIN'ах — это тоже не хорошо и используют их в крайних мерах.
Из моей практики, практически всегда можно решить задачи без вложенных запросов, используя только JOIN'ы, GROUP BY, HAVING и агрегирующих процедур, в таком случае планировщик запросов составит правльный план и использует все средства для оптимизации (индексы, кеш и т.п.). Именно такие решения будут являться правильными! Но это ИМХО. И я не знаю тонкостей работы MS SQL, может там всё наоборот.

[zBit]

И да, ещё, само по себе решение JOIN UserInfo U ON 1 = 1 какое-то странное.
Допустим у нас есть 10 пользователей и 10 записей в таблице UserInfo. Когда мы сделаем присоединение именно таким образом, то у нас в результате получится 100 строк. Разницу в производительности между 10 и 20 пользователями почувствовать сложно, но вот если у нас 10 000 пользователей, то наступит ад, а если их миллион, то сами подумайте)

Я бы не доверил вам проектировать БД своих проектов. Без обид ;)

P.S.: К слову, если у нас будет миллион пользователей и мы сделаем присоединение ON 1=1 без лимитов, то выборка будет состоять из 1 000 000 000 000 (1 триллион) строк.

[xzilgiz]

Вы не внимательно читали.

--где UserInfo вьюха, которая выдаёт данные о пользователе который запускает запрос

Стандартная функция, для MS SQL это SUSER_NAME(), для IBM DB2 USER()

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

[zBit]

Тут много до чего можно докопаться…
Но хочу сразу предупредить, что с MS SQL я не имел дел никогда и не знаю о тонкостях его работы вообще ничего. За-то есть большой опыт работы с PostgreSQL, Firebird, MySQL, SQLite (это из РСУБД).
1.

Замена Left Join на Union

Как эти подходы можно сравнивать? Ведь схема выборки изменится!
2. Решения

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

и

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

Должны быть идентичными. Вы выполните оба запроса (с EXPLAIN-ом) и посмотрите какая будет схема выполнения. Я уверен на 95%, что планировщик запросов сделает их практических идентичными. А для того, чтобы COALESCE(T2.Column, 0) = COALESCE(T1.Column, 0) «искалось» по индексу — нужно создать для этого функциональные индексы.

И определённо, запрос

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

будет более ресурсоёмким по сравнению с вариантом выше и функциональными индексами. Посмотрите сами на план запроса.

3.

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

Я, конечно, могу ошибаться, но в PostgreSQL будет задействован именно результат, а не будет подставляться запрос целиком каждый раз, когда вы его будете использовать. Во всяком случае, у меня есть основания так полагать, т.к. оптимизировал несколько раз таким образом запросы и план запроса был «легче» с WITH, чем с подставленными целыми запросами. Думаю, что в MS SQL должно быть так же, это же логично! Хотя я и могу ошибаться, т.к., выше уже писал, что ничерта не шарю в тонкостях работы MS SQL.

P.S.: буду рад, если меня поправят :)

[zBit]

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;

*facepalm* Отвратительное решение! Выше объяснили почему.

И я правильно понял, что вы создаёте процедуры «на лету» и дропаете их?

[xzilgiz]

И я правильно понял, что вы создаёте процедуры «на лету» и дропаете их?

Нет, зачем?

[zBit]

Вот и я тоже подумал, «зачем?»)))