Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как ускорить работу View (в MsSql)?  [new]
Konstantin Dav
Member

Откуда:
Сообщений: 21
Здравствуйте.

Попробую словами, без кода, объяснить свою проблему.
Есть бизнес - объект, называемый Order. Каждый его экземпляр хранится в строке таблицы Orders.
В нем около 150 полей, значительная часть (около половины) из которых - это id элементов справочников (перечислений).
Чтобы сделать объект "доступным для отображения" есть View, которая называется OrdersView.
Исторически сложилось, что в самом начале в объекте было гораздо меньше полей,
потом новые поля добавлялись в объект, в таблицу(как NULLable поля) и во View (как LEFT OUTER JOIN).
И по всему этому хозяйству надо делать всякие сложные поиски, которые формулируются в терминах полей,
которые выдает View.
Со временем, по мере увеличения кол-ва полей, кол-ва справочников и кол-ва записей в таблицу Orders -
поиск работал все более медленно. В попытках ускорить его я попытался построить индексы по полям View.
И столкнулся с тем, что для View, использующего Left Outer Join - это невозможно
(получаю ошибку "Cannot create index on view "Test.dbo.OrdersView" because it uses a LEFT, RIGHT, or FULL OUTER
join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.").

Теперь у меня есть несколько выходов, я вижу такие:
1) отказываеться от использования Left Outer Join (что фактичеси потребует, чтобы у меня в строках таблицы не было
NULL - значений, ну и вообще потребует некоторого изменения логики на уровне извлечения и записи данных).

2) перейти на базу данных, которая не обладает таким странным ограничением на одновременное использование LEFT OUTER
JOIN и индексирование полей View (например, на одну из версий Oracle)

3) Отказаться в этой задаче от реляционной базы вообще, и использовать какую нибудь нереляционную, например, MongoDB.
Фактически этот объект Order - это документ, у которого много полей. Похоже на то, что его удобно хранить в
документно - ориентированной базе.

Как Вы посоветуете мне решать эту проблему - хранения вообще и медленного поиска в частности?
Любой Ваш совет будет принят с благодарностью.
6 дек 15, 22:23    [18521455]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Rankatan
Member

Откуда:
Сообщений: 250
Особо не вчитывался, но ответ обычно одинаковый:

1. переписать запрос
2. создать индексы на таблицах
3. создать индексированное представление
6 дек 15, 22:30    [18521469]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Konstantin Dav
В попытках ускорить его я попытался построить индексы по полям View.

Попытки ускорить что-нибудь надо начинать с поиска того, что тормозит.
Например с анализа плана выполнения запроса
6 дек 15, 23:32    [18521569]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Konstantin Dav
Member

Откуда:
Сообщений: 21
Что тормозит?
Матчится в одном View около 70 таблиц.
Получается View со 150 колонками, по некоторым из которых надо делать запрос по like '%искомое слово%'. Полнотекстовый поиск не помогает - надо искать по части названия, не обязательно по слову целиком.
По таблице Orders индексы построены.
Индексы по View - пытаюсь построить и получаю ошибку, как это написано в первом посте.

Когда я (для эксперимента) положил результаты View в плоскую таблицу - я получил практически мгновенный поиск. То есть тормозит именно склейка представления из 70 рахзных таблиц.

> 3. создать индексированное представление
- о том, как это сделать, большая часть вопроса.
7 дек 15, 01:11    [18521754]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
aleks2
Guest
Лучше помедитировать на тему: а нахера искать по View (соединению таблиц)?
7 дек 15, 06:05    [18521834]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Konstantin Dav
Member

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

вы правы, я медитировал на эту тему, в НЕКОТОРЫХ случаях можно искать по таблице.
Но есть такой паттерн:
Orders содержит поле CustomerId
OrdersView преобразует этот CustomerId в CustomerName.
Поисковый запрос выглядит как "where CustomerName like '%кастомер%'"

Вот такие вещи мне не удается засунуть в поиск по таблице.
7 дек 15, 09:06    [18522036]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Konstantin Dav
Что тормозит?
Матчится в одном View около 70 таблиц.
Планы лучше всё-таки посмотреть. Иногда тормозит один-единственный JOIN из-за отсутствующего индекса. Но подозреваю, что при таком количестве таблиц да ещё и со сложными поисками по присоединённым полям, хороших планов не будет. Вернее, самые лучшие планы будут медленными.

Konstantin Dav
Когда я (для эксперимента) положил результаты View в плоскую таблицу - я получил практически мгновенный поиск. То есть тормозит именно склейка представления из 70 рахзных таблиц.
Можно создать плоскую таблицу, состояние которой поддерживать триггерами на исходных таблицах (ага, 70 таблиц - 70 триггеров). Что проще будет: отказаться от NULL'абельность или рисовать кучу триггеров - смотрите сами.

А ещё можно вместо NULL использовать какое-то особое значение, которого заведомо не будет в справочниках (например, 0 для ключа типа int identity(1,1)), создать в справочнике запись с этим ключом - и LEFT JOIN можно поменять на INNER.
7 дек 15, 09:18    [18522073]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Konstantin Dav
Поисковый запрос выглядит как "where CustomerName like '%кастомер%'"

Для такого запроса невозможно использовать поиск по индексу
7 дек 15, 09:39    [18522163]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Glory
Konstantin Dav
Поисковый запрос выглядит как "where CustomerName like '%кастомер%'"

Для такого запроса невозможно использовать поиск по индексу
Ну не то, чтобы совсем невозможно. Используя таблицу с числами, можно построить индексированное представление для быстрого поиска подстроки. Примерно так:
create view dbo.CustomersIndex with schemabinding as
select a.id,b.N,substring(a.CustomerName,b.N,100) as CustomerPart
from dbo.Customers a
  inner join dbo.Numbers b on b.N between 1 and len(a.CustomerName)
go
create unique clustered index ix_CustIndex on dbo.CustomerIndex (CustomerPart,N,id)
go

И тогда "CustomerName like '%кастомер%'" превращается в "CustomerPart like 'кастомер%'", который уже может использовать индекс.
Только, скорее всего, оно здесь не понадобится.
7 дек 15, 09:55    [18522275]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
А как вы формируете запросы к вью? Все поля выбираете? Как фильтр собираете?
7 дек 15, 10:07    [18522380]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Konstantin Dav
Member

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

Фильтр собирается так:
есть форма, в которой пользователь набирает условия. Туда выведены все эти справочники, текстовые поля и т.п.
Из неё фомируется запрос (к OrdersView)
запрос может выглядеть примерно так:
SELECT * WHERE  [ДатаЗаказа]  >= @ДатаЗаказа0  AND [ДатаЗаказа]  <= @ДатаЗаказа1  AND [КатегорияСрочности]  = @КатегорияСрочности2  AND ( [ЭтапВыполнения]  = @ЭтапВыполнения3  OR [ЭтапВыполнения]  = @ЭтапВыполнения4  OR [ЭтапВыполнения]  = @ЭтапВыполнения5  OR [ЭтапВыполнения]  = @ЭтапВыполнения6  ) AND ( [Исполнитель1]  = @Исполнитель17  OR [Исполнитель1]  = @Исполнитель18  OR [Исполнитель1]  = @Исполнитель19  OR [Исполнитель1]  = @Исполнитель110  ) AND [НомерДокумента]  LIKE @НомерДокумента11  AND [Изготовитель]  LIKE @Изготовитель12  AND [Эксперт]  LIKE @Эксперт13  AND [Контрагент]  LIKE @Контрагент14  AND [Продукция]  LIKE @Продукция15  ORDER BY [КодЗаказа]  DESC
7 дек 15, 10:32    [18522526]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Konstantin Dav
Member

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

то есть, на чтение из базы - да, выбираются все поля OrdersView, а в условие выбора - вставляются те поля, которые пользователь выбрал как те, по которым еу надо установить какие то ограничения.
7 дек 15, 10:38    [18522569]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3752
Konstantin Dav
Mike_za,

Фильтр собирается так:
есть форма, в которой пользователь набирает условия. Туда выведены все эти справочники, текстовые поля и т.п.
Из неё фомируется запрос (к OrdersView)
запрос может выглядеть примерно так:
SELECT * WHERE  [ДатаЗаказа]  >= @ДатаЗаказа0  AND [ДатаЗаказа]  <= @ДатаЗаказа1  AND [КатегорияСрочности]  = @КатегорияСрочности2  AND ( [ЭтапВыполнения]  = @ЭтапВыполнения3  OR [ЭтапВыполнения]  = @ЭтапВыполнения4  OR [ЭтапВыполнения]  = @ЭтапВыполнения5  OR [ЭтапВыполнения]  = @ЭтапВыполнения6  ) AND ( [Исполнитель1]  = @Исполнитель17  OR [Исполнитель1]  = @Исполнитель18  OR [Исполнитель1]  = @Исполнитель19  OR [Исполнитель1]  = @Исполнитель110  ) AND [НомерДокумента]  LIKE @НомерДокумента11  AND [Изготовитель]  LIKE @Изготовитель12  AND [Эксперт]  LIKE @Эксперт13  AND [Контрагент]  LIKE @Контрагент14  AND [Продукция]  LIKE @Продукция15  ORDER BY [КодЗаказа]  DESC

1. Вью переделай в таблицу, таблицу собирай по регламенту если это возможно
2. Переделай запрос, из формы прекрасно можно передавать id а не имена.
3. на ДатаЗаказа кстати явно просится кластерный индекс
7 дек 15, 11:28    [18522929]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
Konstantin Dav,

Неясна проблема... во-первых, что там у Вас с нормализацией данных (NULL откуда), во вторых убедитесь, что время поиска плана запроса не превышает времени самого запроса. У Вас там явно не одна таблица, иначе откуда бы взяться join.
Представление - это сохраненный запрос на сервере (как правило), как напишите, так и будет работать.
7 дек 15, 11:32    [18522963]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Konstantin Dav,

А план можете выложить? В прикрепленном файле
7 дек 15, 11:44    [18523087]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
божественный отчет
Guest
Mike_za
Konstantin Dav,

А план можете выложить? В прикрепленном файле

на что там смотреть? вся база собрана в одну кучу.
7 дек 15, 12:37    [18523561]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
хмхмхм
Guest
Konstantin Dav,

Вьюху на процедуру возможно заменить?
7 дек 15, 13:28    [18523978]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
Konstantin Dav,

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

у меня была подобная задача
очень сложно заказчик одну функциональность сформулировал
путем надо было его переубедить, но не вышло
сначала сделал все с помощью трехуроневой вьюхи
на тестах все работало хорошо но когда стали копиться данные то стало тормозить
выяснилось что фильтры применяются после связывания всех данных в кучу
хотя если изловчиться сначала применить фильтры то связывать останется всего пару строк
как заставить сервер сделать именно так до сих пор не знаю
переписал вьюхи в функции в которых нужные для связывания строки таблиц
предналиваются в табличные переменные
а потом короткие таблички уже быстро связываются в любую сложную логику
7 дек 15, 14:09    [18524329]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
Konstantin Dav,

COLUMNSTORE INDEX на SQL 2014
HP Vertica -- колоночная субд

Но начать я вам рекомендую с прочтения вот этой книги. http://www.amazon.com/The-Data-Warehouse-Lifecycle-Toolkit/dp/0470149779
7 дек 15, 14:23    [18524405]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
хмхмхм
Guest
a_voronin
Konstantin Dav,

COLUMNSTORE INDEX на SQL 2014


Ага, если у человека OLTP база, то колоночный индекс ему самое то
7 дек 15, 16:05    [18525120]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Rankatan
Member

Откуда:
Сообщений: 250
хмхмхм
a_voronin
Konstantin Dav,

COLUMNSTORE INDEX на SQL 2014


Ага, если у человека OLTP база, то колоночный индекс ему самое то

В 2014 COLUMNSTORE уже не readonly
7 дек 15, 16:21    [18525212]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
хмхмхм
Guest
Rankatan
хмхмхм
пропущено...


Ага, если у человека OLTP база, то колоночный индекс ему самое то

В 2014 COLUMNSTORE уже не readonly


Да, я в курсе, только вы попробуйте создать columnstore индекс на 100+ колонок в операционной таблице, в которую постоянно идет вставка (OLTP) и посмотрите на производительность.
7 дек 15, 16:27    [18525240]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
хмхмхм
Rankatan
пропущено...

В 2014 COLUMNSTORE уже не readonly


Да, я в курсе, только вы попробуйте создать columnstore индекс на 100+ колонок в операционной таблице, в которую постоянно идет вставка (OLTP) и посмотрите на производительность.


А вы знаете, я вот пробовал. И могу дать вам подсказку. -- Партиционирование вам поможет.
7 дек 15, 16:53    [18525400]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
Konstantin Dav
Member

Откуда:
Сообщений: 21
Парни,
большое Вам человеческое спасибо.
Советы помогли, особенно совет помедитировать над тем, почему поиск идет по View, а не по таблице.

Ну и вообще в голове уложились подходы к такой задаче.

Заключительный вопрос:
в языках типа Java или C# Null - это нормально.
А почкму в базе - это плохо?
8 дек 15, 10:08    [18527826]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить работу View (в MsSql)?  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
a_voronin
хмхмхм
пропущено...


Да, я в курсе, только вы попробуйте создать columnstore индекс на 100+ колонок в операционной таблице, в которую постоянно идет вставка (OLTP) и посмотрите на производительность.


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



еще осталось только nosql неофиту посоветовать, и будет полный набор базвордов. 
8 дек 15, 19:21    [18531943]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить