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

Откуда:
Сообщений: 13148
Двухзвенка. Важно.
В базу могут лезть "лишние" приложения. Важно.
Привязка имени пользователя сделана по user_name(). Важно.

Есть таблица. Большая. Очень. Есть пользователи. Вредные. Много. Из таблицы надо кое-кому показывать кое-что. Все как обычно. Критерии достаточно простые и давно формализованы. Упираемся только в затрачиваемые ресурсы сервера на эту простую, в общем-то, операцию. В итоге сделал уже таблицу Ид / Ид. Но! Пользователи делятся на условно 3 категории. Кому видно 25% , 50% и 75% от всей инфы. Есть уже функция, которая возвертает в апликуху имя вьюхи, из которой надо читать... Но все без толку - упираемся в любимую проблему, когда с константой все ок, а с переменной ... Ну очень большие таблицы - сказать, что серверу хреново - ничего не сказать.

И чего, остается только роль приложения?

И "вернуться" из роли приложения никак?
29 апр 04, 19:07    [658141]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
процедура?
динамика? раз уж на то пошло...? совсем тяжко?


для спящего время бодрствования равносильно сну
29 апр 04, 19:12    [658148]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Динамика исключается. Я же писал - прочие приложения лазят.
Проца - тоже. Запросов ОЧЕНЬ много.
99% из них с сортировками...
29 апр 04, 19:15    [658153]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
попробуй exec Proc @param WITH RECOMPILE
недавно экспериментировал - вроде получается получать нормальные планы запросов
29 апр 04, 19:45    [658192]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Я ш пишу - выборки из вьювов , в них - сортировки . Это не переписать . Процы тут не катят . Остается только сделать App роли , сделать вьюхи , из которых торчит наружу Ид пользователя , инитить App роль из апликухи и динамиком писать Ид пользователя ... Тада оптимальность планов гарантируется ... Иначе - болт .
29 апр 04, 19:47    [658194]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Glory
Member

Откуда:
Сообщений: 104760
Indexed views ???
29 апр 04, 19:54    [658200]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Indexed не покатит по куче причин .

1.Нестабильно работают (и не только по данным этомго форума)
2.У 99% клиентов не ЕЕ версии - а в не ЕЕ версиях оптимизатор прохладно относится к индексам на вьювы
3.Вьювы не самые простые - будет сложно их проиндексить . Начнем с того, что они от user_name() зависят. Вот и приплыли.

Ну и мне неочевидно, что в вопросах RowLevelPermissions индексированные вьювы вообще могут как-то помочь - для каждого пользователя что , будут свои индексы?! :O Даже в 3 - звенке это как бы "не тот случай" ИМХО .

Или ошибаюсь где? Будет приятно услышать :) Как раз тот случай!

Вариант со сведением прав в таблицу вида Ид / Ид - крайний , пока рассматриватеся как запасной , но обписывается , обписывается . А голова болит от того , что очень много запросов пошло к этим вьювам и запросы начинают кушать полсервера , что совершенно непозволительно для них .
29 апр 04, 20:07    [658215]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Glory
Member

Откуда:
Сообщений: 104760
2.У 99% клиентов не ЕЕ версии - а в не ЕЕ версиях оптимизатор прохладно относится к индексам на вьювы

Ну а хинты для указания индекса на что ?

для каждого пользователя что , будут свои индексы?!
Почему каждому свои ?? разговор впроде был про "Пользователи делятся на условно 3 категории. Кому видно 25% , 50% и 75% от всей инфы."
29 апр 04, 20:09    [658221]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
YellowMan
Member

Откуда: острова
Сообщений: 1047
Торможу я - для каждой категории пользователей свой view или один view для всех ?
И что на каждого юзера есть связь user_id() и id из таблицы данных что он может видеть ?
ИМХО как-то все оно неправильно, легкости нет...
Напиши чуть поподробнее, с примерами - будем думать...
29 апр 04, 20:21    [658237]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Crimean
Member

Откуда:
Сообщений: 13148
2 Glory

SET QUOTED_IDENTIFIER on

SET ARITHABORT on
go

alter view v2
with SCHEMABINDING
as
select id, Code, CurrencyTag , fullname as name
from dbo.amounts
-- where Ctrls = user_name()

go

create unique clustered index iv2_code on v2( Code, CurrencyTag )
go


Мне ш не жалка для дела :)

select * from v2 with (index(iv2_code)) where code like '1%'


select top 128 * from v2 where code like '1%' order by code

select top 128 * from v2 with (index(iv2_code)) where code like '1%' order by code


В результате получаем "Warning: Index hints supplied for view 'v2' will be ignored." ну и планы я приводить не буду, там используется индекс на ТАБЛИЦУ. Это мы уже проходили. Или привести? :) Я об этом предупреждал - у мя не ЕЕ версия, а стандарт.
Ну и при попытке сделать

SET QUOTED_IDENTIFIER on

SET ARITHABORT on
go

alter view v2
with SCHEMABINDING
as
select id, Code, CurrencyTag , fullname as name
from dbo.amounts
where Ctrls = user_name()
go

create unique clustered index iv2_code on v2( Code, CurrencyTag )
go


Получаем

Server: Msg 1949, Level 16, State 1, Line 1

Index on view 'SCROOGE.dbo.v2' cannot be created because function 'user_name' yields nondeterministic results.


А у нас четко из вьюва должно высыпаться то , что доступно пользователю. Как это сделать без привязки к user_name() и прочим похожим функциям я не знаю. Обманывать сиквел в этом случае, оборачивая user_name() в свои функции тоже не хочется - слабо представляю результат. Даже в теории.

2 YellowMan

Да все просто. Есть боооольшая таблица, есть пользователи, есть очень мутные правила показа строк этой таблицы пользователям. Все как у всех. Только 2 - звенка и необходимость на 100% закрыть сами данные. То есть только вьюв с вмурованным user_name() . А это в случае указанного разброса селективностей для пользователей и наличия сортировок (будь они неладны, но пользователям НАДО и действительно НАДО) приводит к пожиранию ресурсов. Правила можно сделать более или менее мутными, можно даже, как я сейчас пробую, сбросить пересчитанную таблицу UserId / ItemId в постоянную таблицу, пересчитывать ее определенным образом (головняк, но решаемый) и ... получаем те же причиндалы, вид сбоку.

Прям как вот тут
Нецензурно, но к месту
29 апр 04, 20:32    [658246]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Glory
Member

Откуда:
Сообщений: 104760
Я об этом предупреждал - у мя не ЕЕ версия, а стандарт.
А вот если базу сделать в ЕЕ. И в нем же создать индексированное представление. А потом деатачем/аттачем перенести на другой сервер. То тогда хинт будет работать. Но это так к слову. Ибо по nondeterministic конечно же индекс не создать.
29 апр 04, 20:38    [658254]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
YellowMan
Member

Откуда: острова
Сообщений: 1047
А если правила довести до ума ? В принципе, если т.н. групп юзеров немного то может собрать их (юзеров) в группы, для каждой создать вьюху с прошитым кодом группы и к ней уже лезть ?
А на каком поле самая большая селективность ? Code,CurrencyTag или Code,CurrencyTag, user_name() ?

И что значит - from dbo.amounts where Ctrls = user_name()
Для каждого user_name() своя строчка в amounts с одним и тем-же ?
29 апр 04, 20:47    [658267]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
SkyWalker
Member

Откуда:
Сообщений: 320
Если количество групп фиксированное, то можно попробовать создать view для каждой группы. Внутри делать join на вот ету самую таблицу где будет GroupId, UserId, и выбирать только записи относяшиеся к данной группе. А вот уже ето view проиндексировать.
29 апр 04, 21:12    [658295]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
partioned views не могут помочь?
30 апр 04, 02:01    [658409]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Всё же не врублюсь - чем же роль приложения то не катит? Нормальное решение...
Кстати (мож я просто просмотрел здесь), а инлайновую ф-ию вместо представления не пробовал?
30 апр 04, 07:04    [658475]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Crimean
Member

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

кстати , а планы выполнения запросов из вьювов компилятся или нет ? и если да , то почему в профилере этого не видно ?

* для каждой категории пользователей свой view или один view для всех

один виев для всех. приложение у всех одно , но видно в ем каждому свое

* И что на каждого юзера есть связь user_id() и id из таблицы данных что он может видеть

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

* А вот если базу сделать в ЕЕ. И в нем же создать

нереально :) я же не буду у всех клиентов данные качать :) да и не дадут :)

* для каждой создать вьюху с прошитым кодом группы

это все настраивается . весьма персонально . можно , конечно , лазить в настройки и создавать объекты , но как потом имена объектов передать в приложения ? :) оно , конечно , можно , но не более ли это криво ?

ну и , боюсь , в итоге скатимся до один пользователь - одна вьюха ? а поддерживать это как ? :O

* И что значит - from dbo.amounts where Ctrls = user_name()

не более , чем способ показать , что используется user_name() , как аргумент против индексированных вьювов

* partioned views не могут помочь

чем ?

* Всё же не врублюсь - чем же роль приложения то не катит

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

* а инлайновую ф-ию вместо представления не пробовал

а какая разница ? :) инлайновая функция <> константа
30 апр 04, 11:11    [658962]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
BBX
Member

Откуда: Днепропетровск
Сообщений: 22
http://www.osp.ru/win2000/sql/master/402_1.htm
Кэлен Дилани
... По мере того как оптимизатор запросов оценивает все более и более сложные планы, он вычисляет соотношение выгоды в производительности и потраченного времени на продолжение поиска наилучшего плана. Например, предположим, что оптимизатор тратит 1 мс на поиск плана, который предполагает выполнить за 30 мс, затем выясняет, что можно найти план, который будет выполняться за 25 мс, но составить такой план займет еще 2 секунды. Обычно SQL Server решает, что затраты на продолжение поиска не принесут выгоды...

Очевидно приведенный ниже типовой взгляд автора "Любимый оптимизатор" слишком сложен для оптимизатора. Во всяком случае комбинацией таких скриптов автору и Ко удалось парализовать работу нашей конторы

CREATE VIEW dbo.sv_Bills
AS
SELECT x.Id ,
x.MasterId ,
...
x.PayDate ,

-- Дебет счет
d.TopicId as DebitTopic ,
d.ShortName as RealDebitShort ,
d.FullName as RealDebitFull ,

-- Кредит счет
c.TopicId as CreditTopic ,
c.ShortName as RealCreditShort ,
c.FullName as RealCreditFull ,

-- Валюта
y.Id as CurrencyId ,
y.Name as CurrencyName ,

-- Тип операции
( SELECT Name
FROM Actions a (NOLOCK)
WHERE a.Id = x.ActionId
) as ActionName ,

-- Тип НБУ
( SELECT Name
FROM BillKind k (NOLOCK)
WHERE k.Id = x.Kind
) as KindName ,

-- Банк - отправитель
( SELECT Name
FROM Banks b (NOLOCK)
WHERE b.Code = x.SourceCode
AND b.BankType = 0 -- x.SourceType
) as SourceName ,

-- Банк - получатель
( SELECT Name
FROM Banks b (NOLOCK)
WHERE b.Code = x.TargetCode
AND b.BankType = 0 -- x.TargetType
) as TargetName ,

-- Владелец
( SELECT Code
FROM Users u (NOLOCK)
WHERE u.Id = x.CreatorId
) as CreatorCode ,
( SELECT Name
FROM Users u (NOLOCK)
WHERE u.Id = x.CreatorId
) as CreatorName ,

-- Реальные состояния
x.PermitFlag & 0xFF as RealPermit ,
x.TreatFlag & 0xFF as RealTreat

FROM Bills x (NOLOCK FASTFIRSTROW)

JOIN Users u (NOLOCK)
ON u.Code = user_name()

JOIN Currency y (NOLOCK)
ON y.Tag = x.CurrencyTag

LEFT OUTER JOIN Amounts d (NOLOCK)
ON d.Id = x.DebitId

LEFT OUTER JOIN Amounts c (NOLOCK)
ON c.Id = x.CreditId

WHERE ( ( EXISTS
( SELECT 1
FROM GlobalParam (NOLOCK)
WHERE OwnerId = 0
AND Code = 'IF_VISIBLE'
AND Kind = 1
AND Flag & 4 <> 0
)
OR u.ViewFlag & 4 <> 0
)
AND EXISTS -- IN
( SELECT 1
FROM Visible v (NOLOCK)
WHERE v.UserId = x.CreatorId
AND v.OwnerId = u.Id
)
)
OR ( NOT
( EXISTS
( SELECT 1
FROM GlobalParam (NOLOCK)
WHERE OwnerId = 0
AND Code = 'IF_VISIBLE'
AND Kind = 1
AND Flag & 4 <> 0
)
OR u.ViewFlag & 4 <> 0
)
AND EXISTS
( SELECT 1
-- MAX( i.IsView ),
-- MAX( i.IsWork )
FROM AccessItems i (NOLOCK)
JOIN Access a (NOLOCK)
ON a.GroupId = i.GroupId
AND a.UserId = u.Id
WHERE i.Kind = 0
AND i.ItemId = y.Id
/*
HAVING MAX( i.IsView ) = 1
OR MAX( i.IsWork ) = 1
*/
)
AND EXISTS
( SELECT 1
-- MAX( i.IsView ),
-- MAX( i.IsDebit )
FROM AccessItems i (NOLOCK)
JOIN Access a (NOLOCK)
ON a.GroupId = i.GroupId
AND a.UserId = u.Id
WHERE i.Kind = 8
AND i.ItemId = x.ActionId
HAVING MAX( i.IsView ) = 1
OR ( x.CreatorId = u.Id
AND MAX( i.IsDebit ) = 1
)
)
AND ( ( x.DebitId = 0
AND x.CreditId = 0
)
OR ( d.Id IS NULL
AND x.CreditId = 0
)
OR ( x.DebitId = 0
AND c.Id IS NULL
)
OR ( d.Id IS NULL
AND c.Id IS NULL
)
OR EXISTS
( SELECT 1
-- MAX( i.IsDebitView ),
-- MAX( i.IsDebit )
FROM AccessItems i (NOLOCK)
JOIN Access a (NOLOCK)
ON a.GroupId = i.GroupId
AND a.UserId = u.Id
WHERE i.Kind IN (3,4,5,6,7,9,12)
AND i.ItemId =
CASE i.Kind
WHEN 3 THEN d.TopicId / 1000
WHEN 4 THEN d.TopicId / 100
WHEN 5 THEN d.TopicId / 10
WHEN 6 THEN d.TopicId
WHEN 7 THEN d.OwnerId
WHEN 9 THEN d.UserId
WHEN 12 THEN d.BranchId
END
HAVING MAX( i.IsDebitView ) = 1
OR ( x.CreatorId = u.Id
AND MAX( i.IsDebit ) = 1
)
)
OR EXISTS
( SELECT 1
-- MAX( i.IsCreditView ),
-- MAX( i.IsCredit )
FROM AccessItems i (NOLOCK)
JOIN Access a (NOLOCK)
ON a.GroupId = i.GroupId
AND a.UserId = u.Id
WHERE i.Kind IN (3,4,5,6,7,9,12)
AND i.ItemId =
CASE i.Kind
WHEN 3 THEN c.TopicId / 1000
WHEN 4 THEN c.TopicId / 100
WHEN 5 THEN c.TopicId / 10
WHEN 6 THEN c.TopicId
WHEN 7 THEN c.OwnerId
WHEN 9 THEN c.UserId
WHEN 12 THEN c.BranchId
END
HAVING MAX( i.IsCreditView ) = 1
OR ( x.CreatorId = u.Id
AND MAX( i.IsCredit ) = 1
)
)
)
)
30 апр 04, 11:39    [659099]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Какие люди! Будем вспоминать убиение "совершенно ненужного индекса"?
30 апр 04, 11:44    [659118]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
BBX
Member

Откуда: Днепропетровск
Сообщений: 22
Ага

На таблице из пары мильйонов строк более 15-ти комбинированных индексов из 5-7 полей каждый
Один из них построен на поле у которого около 40 тыс. уникальных значений ( остальные NULL)
И именно индекс заХинтован в процедурке

И еще много какой чепухи, которую гад-Билли не научился оптимизировать
30 апр 04, 11:51    [659159]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
автор
И еще много какой чепухи, которую гад-Билли не научился оптимизировать

Вот и Билли досталось. Не нравится продукт - не используйте.
30 апр 04, 11:54    [659172]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
BBX
Member

Откуда: Днепропетровск
Сообщений: 22
... Вот и Билли досталось

Это - цитата. К тому, что все вялые просьбы конторы оптимизировать систему, которая при переходе с 6.5 н 7.0 (на одной и той же базе) практически остановилась при одновременной работе 4-5 юзерей заканчивалась отказами разработчиков типа
1) купите новое железо
2) билли лучше не позволяет
3) не сметь трогать индексы ...

PS

А приведенный выше запрос работал в конструкции типа

select count(*) from dbo.sv_Bills where ...

так, что с Билли все в порядке
30 апр 04, 12:04    [659227]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Я правильно понимаю , что при использовании ролей приложений если "злоумышленнику" в руки попадает контроль над приложением (он имеет исходные тексты и может пересобрать его на свое усмотрение) , то все , безпека пошла одним местом?
30 апр 04, 12:06    [659236]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
автор
"злоумышленнику" в руки попадает контроль над приложением (он имеет исходные тексты и может пересобрать его на свое усмотрение)

А исходники что, лежат на серваке в расшаренной папке public с описанием, чего с ними делать?
Прятать надо всё! И даже немного больше.
30 апр 04, 12:10    [659253]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
Hibernate
Member

Откуда: Киев
Сообщений: 1670
Я правильно понимаю , что при использовании ролей приложений если "злоумышленнику" в руки попадает контроль над приложением (он имеет исходные тексты и может пересобрать его на свое усмотрение) , то все , безпека пошла одним местом?

да. даже если пароль app-роли прятать очень глубоко - он ведь константа, значит вычислить можно, а уж имея исходники - чтоб ты не сомневался.
30 апр 04, 12:20    [659312]     Ответить | Цитировать Сообщить модератору
 Re: Любимый оптимизатор  [new]
YellowMan
Member

Откуда: острова
Сообщений: 1047
Давайте попробуем немного потеоритезировать - что храниться в таблице dbo.amounts ? Судя по названию какие-то значения . Какие, или что более важно чьи значения ? Из того кусочка что нам видно я осмелюсь предположить что результаты каких-то действий господина fullname ? Или г-на Code над fullname ?
Теперь идея такая - что может видеть user_id() ? Если все операции г-на fullname то укрупняй связь до уровня user_id()-fullname и соединяй таблицу связей с основной - то же самое если в случае с Code.
Если записи в основной таблице атомарные по своей сути то тогда мы скатываемся на отношение id-id, но тогда непонятно как раздавать права ? На каждую запись в отдельности ? Или по группировке признаков ? Тогда и укрупняй связь до этой группировки.
В любом случае в итоге ты получишь две таблицы которые можно сджойнить во view с нормальным планом...




автор
Кэлен Дилани
Например, предположим, что оптимизатор тратит 1 мс на поиск плана, который предполагает выполнить за 30 мс, затем выясняет, что можно найти план, который будет выполняться за 25 мс, но составить такой план займет еще 2 секунды. Обычно SQL Server решает, что затраты на продолжение поиска не принесут выгоды...


Меня терзают смутные сомнения что уважаемый дон не знает про то что сервер кеширует планы и перед кешированием он таки ищет до упора...
Кстати, планы views не кешируются.

автор

This is similar to a stored procedure; when a view is executed for the first time, only its query tree is stored in the procedure cache. Each time a view is accessed, its execution plan is recompiled.


Так что дело похоже не в кривом плане...
30 апр 04, 14:29    [659759]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить