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

Исходные данные:
Стороннее ПО, код которого недоступен.
БД с которым работает ПО доступна.

С определнного времени работа ПО стала замедляться. Начали смотреть почему тормозит.
Выяснилось, что основные тормоза приходятся на представления, реализующие схему:
базовый класс - подкласс - подкласс.
Т.е. есть:
1. Таблица объектов, в которой хранятся все ID, UID и пр. технические данные об всех объектах системы
2. Таблица общих свойств большей части объектов
3. Таблица свойств частного класса, для которого стротся представление

Таблицы имеют отношения:
1.-2. 1->0..1
1.-3. 1->0..1

Соответсвенно строится объединение по ID объектов из каждой таблицы. В каждой таблице ID объекта объявлен, как первичный ключ.

количество записей по таблицам:
1. 210000
2. 200000
3. 20000

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


Предcтавления имеют вид:

select 
  t1.*
  ,t2.f1,t2.f2,...
  ,t3.f1,t3.f2,...
from
  t1
  inner join t3 on t1.id=t3.id
  left join t2 on t1.id=t2.id

Сервер:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Вопрос:
Как можно улучшить\оптимизировать быстродействие выборки? Может хинтов\индексов добавить? Только куда? Насколько я понял - с ростом числа записей время выполнения запроса будет только расти.
По форуму искал, но особо ничего не нашел (может не по тем словам искал), если кто знает где почитать - кинте ссылкой, пожалуйста.
6 июл 11, 09:22    [10929554]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
SQL_GUEST
По форуму искал, но особо ничего не нашел (может не по тем словам искал), если кто знает где почитать - кинте ссылкой, пожалуйста.
Нужно посмотреть тормозящие запросы и их планы.
Представления не могут тормозить, это же просто текст :-) Тормозит выполнение запросов.

Покажите запрос, его план, по необходимости DDL входящих в него объектов.
6 июл 11, 09:29    [10929582]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
SQL_GUEST
Guest
хм... запроcы к представлениям могут строиться разные, в основном это запросы вида select * from [view]
Имелся в виду запрос, формирующий представление.

Пример плана выполнения запроса с боевой БД во вложении.

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

Хотелось бы понять есть ли какой-то best practice объединения нескольких таблиц один к одному или хотябы в каком направлении копать. Или м.б. такая архитектура в принципе так работает и ее не оптимизировать.
6 июл 11, 11:59    [10930894]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
ЫЙД_ПГУЫЕ
Guest
Что-то файл не вложился

К сообщению приложен файл (sqlplan.rar - 5Kb) cкачать
6 июл 11, 12:04    [10930937]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35399
Блог
Там у вас сильно больше трех таблиц соединяется...
6 июл 11, 12:56    [10931458]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
SQL_GUEST
Имелся в виду запрос, формирующий представление.
То есть медленно выполняется команда CREATE VIEW? Странно как то.

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

Ну вот, тогда для него - текст запроса, план, сколько выполняется, определение таблиц, индексов.
6 июл 11, 13:30    [10931802]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
SQL_GUEST
Guest
Ну это с боевой базы взято.
Джойны таблицы справочников можно оптимизировать, выбирая из таблицы справочника только нужные типы справочных данных, но не в этом вопрос.

Вопрос именно в соединении трех основных таблиц, т.к. как побороть Clustered Index Scan у всех трех таблиц я пока не понимаю (и возможно ли это в принципе на такой реализации).

Во вложении план запроса с отключенными справочниками.

К сообщению приложен файл (sqlsimple.rar - 2Kb) cкачать
6 июл 11, 13:37    [10931864]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
SQL_GUEST
Вопрос именно в соединении трех основных таблиц, т.к. как побороть Clustered Index Scan у всех трех таблиц я пока не понимаю (и возможно ли это в принципе на такой реализации).
Зачем бороть, если это самый оптимальный план???

Вы в запросе из приложенного файла выбираете все записи из всех упомянутых в запросе ваших таблиц. Разве тут может быть что то оптимальнее предварительного скана всех этих таблиц?
6 июл 11, 14:01    [10932108]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
SQL_GUEST
Guest
alexeyvg
SQL_GUEST
Вопрос именно в соединении трех основных таблиц, т.к. как побороть Clustered Index Scan у всех трех таблиц я пока не понимаю (и возможно ли это в принципе на такой реализации).
Зачем бороть, если это самый оптимальный план???

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


Да вот в этом и весь вопрос - если идет объединение по первичному ключу 70 тыс записей (100% одной таблицы и 20-70% от других двух), то лучшего плана на такой архитектуре (тройного объединения по ПК) не добиться?
6 июл 11, 14:13    [10932255]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
SQL_GUEST
alexeyvg
Зачем бороть, если это самый оптимальный план???

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


Да вот в этом и весь вопрос - если идет объединение по первичному ключу 70 тыс записей (100% одной таблицы и 20-70% от других двух), то лучшего плана на такой архитектуре (тройного объединения по ПК) не добиться?
Да, не добиться.

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

Навреное, перезаполняет какой то кеш всеми данными из базы раз в минуту? :-)
6 июл 11, 14:29    [10932448]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
SQL_GUEST
Guest
alexeyvg, к сожалению все печальнее - оно это выплевывает пользователю в табличное предствление :(
Какой-то кеш и пейджинг там есть, но работает, по всей видимости, очень криво, т.к. пока не получит последнюю запись в выборке - пользователь курит бамбук. Поэтому и возникла идея попробовать что-то соптимизировать в БД. Судя по всему будем дальше кушать кактус.

Спасибо за разъяснение!
6 июл 11, 15:08    [10932817]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
SQL_GUEST
alexeyvg, к сожалению все печальнее - оно это выплевывает пользователю в табличное предствление :(
Я такого не понимаю. Что такое "выплёвывает"?
Вы показываете именно тот запрос, который посылает приложение серверу или придуманный вами (содержимое вьюхи; тем более что вы вначале говорили о вьюхах)?

Если первое, то во первых это слабо согласуется с
SQL_GUEST
Какой-то кеш и пейджинг там есть
, во вторых, тогда это ошибка приложения и её надо поправить. Никакими хинтами не ускорить запрос, забирающий все данные из всех таблиц, и выводящий на экран (передающий на клиента) 55000 широких строк.

Просто поменяйте приложение на правильное! :-)

Если второе, то не смотрите на вьюхи, забудьте о них. Анализируйте запросы из приложения. Там возможны и хинты, и оптимизация...
6 июл 11, 15:15    [10932883]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
SQL_GUEST
Guest
alexeyvg,
По сути каждое представление - это класс, тип документа.
Соотсветсвенно структура у таких представлений примерно одинаковая (две основные таблицы + таблица класса + таблицы-справочники по необходимости). На каждый тип документа заведен свой журнал (табличная форма).

Пример (не полный) запроса, формируемого приложением для отображения данных в табличной форме:
SELECT "DBO".VIEW_INDOCS.* --да, да именно так
FROM "DBO".VIEW_INDOCS
WHERE
<масса условий формируемых как динамически, так и жестко зашитых в код>
ORDER BY 
"RegDate" DESC 
,"ID" DESC

Там где у людей простые ограничения - запросы более-менее отрабатывает.
Но есть отдельные категории, которым доступ нужен "ко всем документам и сразу" (руководство, канцелярия и пр.).
У таких пользователей условий по-минимуму, т.е. запрос сводится к выборке из представления всех записей практически без ограничивающих условий.

Сортировка "ID" DESC вшита в код и преподностися разработчиком, как обязательное условие для работы пейджинга\кеша.

По факту - в SSMS запрос, копированный из профайлера при открытии приложением журнала, начинает показывать данные через секунду после запуска, завершает выполнение за 15-17 секунд.
Приложение же начинает отображать данные не менее чем через 17 секунд.

Вот такой вот пейджинг и кеш :(
6 июл 11, 16:06    [10933460]     Ответить | Цитировать Сообщить модератору
 Re: Соединение трех таблиц (класс-подкласс-подкласс), быстродействие  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
SQL_GUEST
У таких пользователей условий по-минимуму, т.е. запрос сводится к выборке из представления всех записей практически без ограничивающих условий.
Но всё таки какие то условия есть...
SQL_GUEST
По факту - в SSMS запрос, копированный из профайлера при открытии приложением журнала, начинает показывать данные через секунду после запуска, завершает выполнение за 15-17 секунд.
Анализируйте код такого запроса и его план.

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

Ну ещё можно, учитывая сортировку, с инедксом поиграть...
SQL_GUEST
Приложение же начинает отображать данные не менее чем через 17 секунд.
Опять же недостаток приложения. Можно было бы и сразу начинать проказывать.

Хотя правильнее правильно сделать пейджинг.

В общем, бага на баге :-)
6 июл 11, 16:18    [10933569]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить