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

Откуда:
Сообщений: 11
Дарова всем! Есть вот такой тэйбл:
CREATE TABLE [dbo].[tbADGroupAccess](
[ID_ADGroupAccess] [int] IDENTITY(1,1) NOT NULL,
[ADDir] [nvarchar](256) NULL,
[ADGroupName] [nvarchar](64) NULL,
[AccessMode] [nvarchar](1) NULL,
[OtherFlags] [nvarchar](16) NULL
)
хранит информацию после сканирования дериктории на сервере:
ADGroupAccess - тупо ид
ADDir - физический путь к директории
ADGroupName - название группы пользователей, имеющих доступ(соот. названию AD)
AccessMode - флаг доступа(F-Full, R - read Only и т.д. - неважно...)
OtherFlags - методы заимствования/получения прав...
После сканирования получаю к примеру папки:
D:\exp
D:\exp\1
D:\exp\2
и все три папки будут иметь одинаковые права, нужно оставить только первую...для этого
дальше провожу укладку следующим запросом
delete from dbo.tbADGroupAccess
where ID_ADGroupAccess in
(
select tb2.ID_ADGroupAccess FROM dbo.tbADGroupAccess tb1
LEFT JOIN dbo.tbADGroupAccess tb2 ON tb2.ADDir like tb1.ADDir + '%'
AND tb1.ADGroupName = tb2.ADGroupName
and tb1.AccessMode = tb2.AccessMode
and LEN(tb2.ADDir) > Len(tb1.ADDir)
WHERE tb1.ID_ADGroupAccess <> tb2.ID_ADGroupAccess
)
который оставляет мне в конкретном примере только
D:\exp

так вот, этот запрос уж больно медленно отрабатывает...
а после последнего скана получил 125к записей, запустил "укладку" этим запросом, 10 минут выполнялось, не дождался и решил к вам обратиться...
З.Ы. при сканировании, по рекурсии ухожу не дальше 5ой вложенности, чтобы не плодить папки/записи
20 окт 09, 15:39    [7812476]     Ответить | Цитировать Сообщить модератору
 Оптимизация запроса  [new]
Рустам Сафиуллин
Member

Откуда:
Сообщений: 11
Кто что может посоветовать? желательно бы как-нибудь от like уйти, но у меня своих мыслей нет по сему поводу...
Заранее благодарен
20 окт 09, 15:42    [7812495]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iljy
Member

Откуда:
Сообщений: 8711
Рустам Сафиуллин,

план запроса покажите. Ну и заполнение таблицы данными неплохо бы.
20 окт 09, 15:46    [7812545]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36808
С такой структурой вы ничего путного не сделаете.
20 окт 09, 15:47    [7812556]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Индекс по [ADGroupName], [AccessMode], [ADDir] вам поможет. Like будет его по возможности использовать.
20 окт 09, 15:49    [7812574]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Рустам Сафиуллин
Member

Откуда:
Сообщений: 11
Гавриленко Сергей Алексеевич
С такой структурой вы ничего путного не сделаете.

А как можно выбраться из ситуации?
З.Ы. мне особо путного не нужно... указанное на мелких количествах директорий(<10k)прекрасно работает, да и запускаться это будет раз в месяц-два....
20 окт 09, 15:52    [7812598]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Рустам Сафиуллин
Member

Откуда:
Сообщений: 11
Anddros
Индекс по [ADGroupName], [AccessMode], [ADDir] вам поможет. Like будет его по возможности использовать.

Спасибо, опробую...
20 окт 09, 15:54    [7812620]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
delete tbl2 
dbo.tbADGroupAccess tb1
LEFT JOIN dbo.tbADGroupAccess tb2 
ON 
       tb2.ADDir like tb1.ADDir + '%'
AND tb1.ADGroupName = tb2.ADGroupName
AND tb1.AccessMode = tb2.AccessMode
AND tb2.ADDir > tb1.ADDir
-- можно убрать
AND tb1.ID_ADGroupAccess <> tb2.ID_ADGroupAccess
20 окт 09, 16:01    [7812682]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
delete tbl2 
dbo.tbADGroupAccess tb1 INNER JOIN dbo.tbADGroupAccess tb2 
ON 
tb2.ADDir like tb1.ADDir + '%'
AND tb2.ADDir > tb1.ADDir
AND tb1.ADGroupName = tb2.ADGroupName
AND tb1.AccessMode = tb2.AccessMode
-- можно убрать
AND tb1.ID_ADGroupAccess <> tb2.ID_ADGroupAccess
20 окт 09, 16:03    [7812704]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
Что-то савсем плохой стал...

delete tbl2 
FROM
dbo.tbADGroupAccess tb1 INNER JOIN dbo.tbADGroupAccess tb2 
ON 
tb2.ADDir like tb1.ADDir + '%'
AND tb2.ADDir > tb1.ADDir
AND tb1.ADGroupName = tb2.ADGroupName
AND tb1.AccessMode = tb2.AccessMode
-- можно убрать
AND tb1.ID_ADGroupAccess <> tb2.ID_ADGroupAccess
20 окт 09, 16:07    [7812741]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Рустам Сафиуллин
Member

Откуда:
Сообщений: 11
aleks2
Что-то савсем плохой стал...

delete tbl2 
FROM
dbo.tbADGroupAccess tb1 INNER JOIN dbo.tbADGroupAccess tb2 
ON 
tb2.ADDir like tb1.ADDir + '%'
AND tb2.ADDir > tb1.ADDir
AND tb1.ADGroupName = tb2.ADGroupName
AND tb1.AccessMode = tb2.AccessMode
-- можно убрать
AND tb1.ID_ADGroupAccess <> tb2.ID_ADGroupAccess

Ок,спс...как на работе окажусь, так опробую...
если ничего не выйдет то программно при сканировании директорий дополнительно в таблицу буду закидывать номер уровня вложенности текущей папки + ид предка, а там глядишь датасеты сузятся и все шустрее начнет бегать...
20 окт 09, 16:55    [7813133]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Рустам Сафиуллин
Member

Откуда:
Сообщений: 11
iljy
Рустам Сафиуллин,

план запроса покажите. Ну и заполнение таблицы данными неплохо бы.


|--Nested Loops(Inner Join, OUTER REFERENCES:([tb1].[ID_ADGroupAccess], [tb1].[ADGroupName], [tb1].[AccessMode], [Expr1006], [Expr1007], [Expr1008], [Expr1009], [Expr1011]))
       |--Compute Scalar(DEFINE:([Expr1006]=[USCHET_TEST].[dbo].[tbADGroupAccess].[ADDir] as [tb1].[ADDir]+N'%', [Expr1007]=LikeRangeStart([USCHET_TEST].[dbo].[tbADGroupAccess].[ADDir] as [tb1].[ADDir]+N'%'), [Expr1008]=LikeRangeEnd([USCHET_TEST].[dbo].[tbADGroupAccess].[ADDir] as [tb1].[ADDir]+N'%'), [Expr1009]=LikeRangeInfo([USCHET_TEST].[dbo].[tbADGroupAccess].[ADDir] as [tb1].[ADDir]+N'%'), [Expr1011]=len([USCHET_TEST].[dbo].[tbADGroupAccess].[ADDir] as [tb1].[ADDir])))
       |    |--Table Scan(OBJECT:([USCHET_TEST].[dbo].[tbADGroupAccess] AS [tb1]))
       |--Filter(WHERE:([USCHET_TEST].[dbo].[tbADGroupAccess].[ADDir] as [tb2].[ADDir] like [Expr1006] AND [Expr1010]>=[Expr1011]))
            |--Index Spool(SEEK:([tb2].[ADGroupName]=[USCHET_TEST].[dbo].[tbADGroupAccess].[ADGroupName] as [tb1].[ADGroupName] AND [tb2].[AccessMode]=[USCHET_TEST].[dbo].[tbADGroupAccess].[AccessMode] as [tb1].[AccessMode] AND [tb2].[ID_ADGroupAccess] > [USCHET_TEST].[dbo].[tbADGroupAccess].[ID_ADGroupAccess] as [tb1].[ID_ADGroupAccess]))
                 |--Compute Scalar(DEFINE:([Expr1010]=len([USCHET_TEST].[dbo].[tbADGroupAccess].[ADDir] as [tb2].[ADDir])))
                      |--Table Scan(OBJECT:([USCHET_TEST].[dbo].[tbADGroupAccess] AS [tb2]))
21 окт 09, 07:57    [7815104]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Рустам Сафиуллин
Member

Откуда:
Сообщений: 11
План подзапроса из DELETE:
select tb2.ID_ADGroupAccess FROM dbo.tbADGroupAccess tb1
LEFT JOIN dbo.tbADGroupAccess tb2 ON tb2.ADDir like tb1.ADDir + '%'
AND tb1.ADGroupName = tb2.ADGroupName
and tb1.AccessMode = tb2.AccessMode
and LEN(tb2.ADDir) >= Len(tb1.ADDir)
WHERE tb1.ID_ADGroupAccess < tb2.ID_ADGroupAccess
21 окт 09, 07:58    [7815106]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
С утра в бошку лезуть новые идеи

delete t
FROM
dbo.tbADGroupAccess t
WHERE exists(select * FROM dbo.tbADGroupAccess t1 WHERE t.ADDir like t1.ADDir + '%'
AND t.ADDir > t1.ADDir
AND t1.ADGroupName = t.ADGroupName
AND t1.AccessMode = t.AccessMode
-- можно убрать
AND t1.ID_ADGroupAccess <> t.ID_ADGroupAccess
)

Теоретически exists здесь могет быть эффективнее, чем JOIN.
21 окт 09, 09:05    [7815267]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Рустам Сафиуллин
Member

Откуда:
Сообщений: 11
aleks2
С утра в бошку лезуть новые идеи

delete t
FROM
dbo.tbADGroupAccess t
WHERE exists(select * FROM dbo.tbADGroupAccess t1 WHERE t.ADDir like t1.ADDir + '%'
AND t.ADDir > t1.ADDir
AND t1.ADGroupName = t.ADGroupName
AND t1.AccessMode = t.AccessMode
-- можно убрать
AND t1.ID_ADGroupAccess <> t.ID_ADGroupAccess
)

Теоретически exists здесь могет быть эффективнее, чем JOIN.

Спасибо! это работает и дает правильные результаты...но время выполнения в 31 минуту меня смущает...буду ломать дальше голову...еще раз спасибо)
21 окт 09, 11:29    [7816254]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
Рустам Сафиуллин
aleks2
С утра в бошку лезуть новые идеи

delete t
FROM
dbo.tbADGroupAccess t
WHERE exists(select * FROM dbo.tbADGroupAccess t1 WHERE t.ADDir like t1.ADDir + '%'
AND t.ADDir > t1.ADDir
AND t1.ADGroupName = t.ADGroupName
AND t1.AccessMode = t.AccessMode
-- можно убрать
AND t1.ID_ADGroupAccess <> t.ID_ADGroupAccess
)

Теоретически exists здесь могет быть эффективнее, чем JOIN.

Спасибо! это работает и дает правильные результаты...но время выполнения в 31 минуту меня смущает...буду ломать дальше голову...еще раз спасибо)


1. Индекс надо, аднако, (ADDir, ADGroupName, AccessMode)
2. Могет быть не УДАЛЯТЬ, а только помечать как УДАЛЕННЫЕ.
21 окт 09, 12:41    [7816955]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Рустам Сафиуллин
Member

Откуда:
Сообщений: 11
Изменил структуру на следующую
CREATE TABLE [dbo].[tbADGroupAccess](
[ID_ADGroupAccess] [int] IDENTITY(1,1) NOT NULL,
[ADDir] [nvarchar](256) NULL,
[ADGroupName] [nvarchar](64) NULL,
[AccessMode] [int] NULL,
[OtherFlags] [nvarchar](16) NULL,
[ADDirRelation] [int] NULL,
[ADDirLevel] [tinyint] NULL,
[ADGroupID] [int] NULL
) ON [PRIMARY]
Добавил:
ADDirRelation - нумеруются папки только первого уровня и подуровням даются эти номера, будь то 2, а будь то 5 уровень
ADDirLevel - уровень вложенности папки
ADGroupID - внешний ключ на справочник названия групп

В итоге написал такой запрос:
delete from dbo.tbADGroupAccess
where dbo.tbADGroupAccess.ID_ADGroupAccess in
(select DISTINCT t1.ID_ADGroupAccess from dbo.tbADGroupAccess t
left join dbo.tbADGroupAccess t1 ON
t1.ADDirRelation = t.ADDirRelation
AND t1.ADDirLevel > t.ADDirLevel
AND t1.AccessMode = t.AccessMode
AND t1.ADGroupID = t.ADGroupID
AND t1.ADDir like t.ADDir + '%'
where t1.ID_ADGroupAccess > t.ID_ADGroupAccess)
И он отработал на сервере за 1м 03с(старые вариации не меньше 25-30минут)....
++ от первоначальной реализации:
1) заменены ресурсоемкие сравнения названий групп на сравнения по числам: t1.ADGroupID = t.ADGroupID;
2) ограничение получаемых поддатасетов за счет t1.ADDirRelation = t.ADDirRelation AND t1.ADDirLevel > t.ADDirLevel;
3) изменен тип данных поля AccessMode на числовой.

За дельные советы спасибо, без Вашего наставления получилось бы гораздо хуже)
З.Ы. наверное можно и дальше оптимизировать, но время выполнения в данном случае устраивает более чем...
22 окт 09, 08:57    [7821458]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить