Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
Matroz
Member

Откуда: Ekaterinburg
Сообщений: 236
Есть таблица которая содержит документы - генерируемые, либо импортируемые сервером.
Documents(ID int identity(1,1) primary key, <набор полей - описание документа>, Body varbinary(max)).
Body - тело документа, в среднем по палате 7800 байт, наибольшая часть документов 300-500 байт.

Тормозит запрос к таблице, который который выбирает фильтрованный список документов для просмотра (размер списка ограничен 50 тыс. строк). Для фильтров есть индексы. Body не выбирается, его просмотр и редактирование отдельными средствами.

Понятно, что в принципе в ОП поднимается слишком много страниц, потом lookup и т.д.

Кроме индексирования, решено поделить таблицу на 2:
Documents_Main (ID int identity(1,1) primary key, <набор полей - описание документа>)
Documents_Data(ID int primary key, Body varbinary(max)))
ALTER TABLE Documents_Data ADD CONSTRAINT FOREIGN KEY (ID) references Documents_Main(ID) on delete cascade 


Изменения не должны сказаться на клиентах, поэтому сверху обновляемый VIEW
alter view Dоcuments  
as
select m.* /*здесь перечислены все поля вместо m.* */, d.Body 
from Documents_Main m inner join Documents_Data d on m.ID = d.ID


В итоге запрос на выборку, который выбирает необходимый список, все равно тормозит.
Из плана видно, что наибольшую нагрузку дает clustered index seek к Documents_Data. Но никаких полей из нее не выбирается, связь 1-1, PK и внешний ключ указан.
Подобный подход (надстройка в виде view) вообще уже используется в БД, лишние таблицы не тянутся в запросы (по крайней мере, там, где это наблюдали).
Почему здесь не так?
Пока объяснил на пальцах, без планов, может есть простой ответ.
14 янв 21, 11:40    [22263127]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 887
Matroz
Кроме индексирования, решено поделить таблицу на 2

Дешевле выкинуть Body в LOB
EXEC sp_tableoption 'Documents', 'large value types out of row', 1

Но это повысит производительность только если Body в подавляющем большинстве выборок не участвует.

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

Сообщение было отредактировано: 14 янв 21, 11:47
14 янв 21, 11:51    [22263135]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8346
Matroz,

уберите d.body и проверьте - будет ли тормозить.
14 янв 21, 12:07    [22263153]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
Matroz
Member

Откуда: Ekaterinburg
Сообщений: 236
Владислав Колосов
Matroz,

уберите d.body и проверьте - будет ли тормозить.


Так мне без d.body это представление и не нужно
14 янв 21, 12:34    [22263180]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8346
Matroz,

это очевидно, вы Вы хотите понять - в каком месте "не работает".
14 янв 21, 13:18    [22263217]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
Yasha123
Member

Откуда:
Сообщений: 1955
Matroz

Из плана видно, что наибольшую нагрузку дает clustered index seek к Documents_Data. Но никаких полей из нее не выбирается

вот и покажите план.
только не картинкой.
https://www.brentozar.com/pastetheplan/
14 янв 21, 13:25    [22263222]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
aleks222
Member

Откуда:
Сообщений: 1240
Matroz

Тормозит запрос к таблице, который который выбирает фильтрованный список документов для просмотра (размер списка ограничен 50 тыс. строк). Для фильтров есть индексы. Body не выбирается, его просмотр и редактирование отдельными средствами.

Понятно, что в принципе в ОП поднимается слишком много страниц, потом lookup и т.д.

Кроме индексирования, решено поделить таблицу на 2:

Пока объяснил на пальцах, без планов, может есть простой ответ.


Знатные борцы с тормозами.
Чтобы сервер "нетормозил" - заставим его делать лишнюю работу - соединение таблиц.

Бред же. Незамутненный.
14 янв 21, 13:37    [22263233]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
Yasha123
Member

Откуда:
Сообщений: 1955
да уж, товарищ, если в запросе вьюха, а не 1 таблица в явном виде,
соединение все равно надо делать, раз вы его заказали.

чтобы не читать таблицу с документами,
ПК по id должен быть некластерный, а у вас поди кластерный,
потому кластерный и фигурирует в плане
14 янв 21, 13:46    [22263250]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
Matroz
Member

Откуда: Ekaterinburg
Сообщений: 236
aleks222

Чтобы сервер "нетормозил" - заставим его делать лишнюю работу - соединение таблиц.

Бред же. Незамутненный.

Отличный совет!
Но серверу нет нужды соединять таблицы, если используются данные только одной из них.
При соблюдении определенных условий (когда разборщик понимает, что это не ограничивающий join)
14 янв 21, 13:48    [22263252]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
aleks222
Member

Откуда:
Сообщений: 1240
Matroz
(когда разборщик понимает, что это не ограничивающий join)

И как сервер это МОЖЕТ понять, кроме как выполнив соединение?
14 янв 21, 13:50    [22263253]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
Yasha123
Member

Откуда:
Сообщений: 1955
ну или если куча не нравится и вью особо хочется,
то по ид делайте и кластерный, и некластерный.
один как ПК, второй просто уникальный.
тогда где не надо боди, подцепит некластерный
14 янв 21, 13:51    [22263254]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
Matroz
Member

Откуда: Ekaterinburg
Сообщений: 236
aleks222
И как сервер это МОЖЕТ понять, кроме как выполнив соединение?

вот так. Натолкнуло на размышления, может ли.
Неверно условие связи записал, теперь может, при наличии уникальности с обеих сторон.
Так работает, Documents_Data в плане нет.
alter view Dоcuments  
as
select m.* /*здесь перечислены все поля вместо m.* */, d.Body 
from Documents_Main m LEFT OUTER join Documents_Data d on m.ID = d.ID

Yasha123
aleks222
спасибо за активацию мозга.

Этот вариант тоже посмотрю (но у нас генерируется скрипт на две БД, в т.ч. Postgres - как он хранит BLOB, пока не рабирался)
ptr128
EXEC sp_tableoption 'Documents', 'large value types out of row', 1
14 янв 21, 14:43    [22263304]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
Matroz
Так работает, Documents_Data в плане нет.
Таблица может быть в плане, но обращений к ней не будет.
Проверять нужно в результатх statistics io.
14 янв 21, 15:29    [22263336]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
aleks222
Member

Откуда:
Сообщений: 1240
Matroz

Так работает, Documents_Data в плане нет.
[src]
alter view Dоcuments
as
select m.* /*здесь перечислены все поля вместо m.* */, d.Body
from Documents_Main m LEFT OUTER join Documents_Data d on m.ID = d.ID


Только ЭТО - необновляемое представление.
14 янв 21, 16:45    [22263386]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 887
Matroz
Postgres - как он хранит BLOB, пока не рабирался

Похоже. Механизмом TOAST.
Отключить out-of-line для поля можно указанием SET STORAGE PLAIN или MAIN.
Форсировать - EXTERNAL или EXTENDED.
Граница выталкивания поля в TOAST, в зависимости от его размера, определяется выражением WITH ( toast_tuple_target = n )
14 янв 21, 17:13    [22263407]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
Matroz
Member

Откуда: Ekaterinburg
Сообщений: 236
aleks222

Только ЭТО - необновляемое представление.

Я не имел в виду, что оно обновляемое по умолчанию, просто не стал писать
На большинстве таблиц триггера висят, для аудита. Здесь будет INSTEAD OF триггер
14 янв 21, 20:30    [22263520]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8346
Matroz,

зачем Вы добавили в запрос колонку body, если она "не выбирается"?
15 янв 21, 02:42    [22263653]     Ответить | Цитировать Сообщить модератору
 Re: Запрос данных из представления - "лишние" таблицы в плане запроса  [new]
Matroz
Member

Откуда: Ekaterinburg
Сообщений: 236
Владислав Колосов,
Представление создано для клиентов, которые используют нашу БД.
Они работают с документами индивидуально (либо генерируют их небольшими пачками) в прежнем режиме,
только теперь будут работать не с таблицей, а с одноименным представлением. Естественно им требуется и описание и содержимое документа (Body).
У нас есть приложение, которое реализует в т.ч. групповые операции над документами. Тормозят несколько запросов, которые используются для сравнительно больших выборок, но им как раз и не требуется содержимое документа (на момент просмотра).
15 янв 21, 09:08    [22263678]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить