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

Откуда: Киев
Сообщений: 2611
Есть табличная функция текст ее под спойлером, очень жутко тормозит ( Clustered Index Scan судя по плану). Подскажите что можно предпринять.

1. Статистика пересчитывается регулярно
2. Индексов вроде бы достаточно
3. Средствами SQL запрос проверялся , рекомендаций никаких не дает.
4. План запроса прилеплен.
5. Мануал , зачитан. Фрагментация , заполнение и т.д. в норме вроде бы.

+ Запрос
SELECT n.*
,r.RubricName,
r.Rubricator,
r.EditionID,
r.RubricShortName,
r.SortOrder,
r.IsActive,
r.Gnizdo_rayon,
e.DepartmentAccountID,
dbo.GetAccessRight(uta.AccessTypeEdit,c.CanEdit) as CanEdit,
dbo.GetAccessRight(uta.AccessTypeDelete,c.CanDelete) as CanDelete
FROM Notice n            
inner join dbo.Rubric r on r.RubricID = n.RubricID
inner join dbo.Edition e on e.EditionID = r.EditionID
inner JOIN dbo.GetAccessClientsDepartments(1138) c  ON  c.ClientID = n.ClientID and e.DepartmentAccountID = c.DepartmentAccountID
INNER JOIN UserTypeAccessView uta on uta.NoticeTypeID=n.NoticeTypeID and uta.UserID=1138
WHERE n.DeletedFlag=0 and uta.AccessTypeRead = 1 and c.CanRead = 1


Тормозит при добавлении связки
inner JOIN dbo.GetAccessClientsDepartments(1138) c  ON  c.ClientID = n.ClientID and e.DepartmentAccountID = c.DepartmentAccountID



попробовал разбить запрос на 2 вот так(создал хранимку), срабатывает мгновенно, но хранимка не подходит. Принципиально использование табличной функции.
+ Альтернативный вариант запроса

   Declare @access TABLE (ClientId int, DepartmentAccountID int ,CanEdit int ,CanDelete int) ;
 
	INSERT INTO @access
	SELECT ClientId, DepartmentAccountID,CanEdit,CanDelete 
	FROM 
	dbo.GetAccessClientsDepartments(@UserID) 
	where CanRead = 1
    
    SELECT n.*,r.RubricName,r.Rubricator,r.EditionID,r.RubricShortName,r.SortOrder,r.IsActive,r.Gnizdo_rayon,e.DepartmentAccountID,
    dbo.GetAccessRight(uta.AccessTypeEdit,c.CanEdit) as CanEdit,
    dbo.GetAccessRight(uta.AccessTypeDelete,c.CanDelete) as CanDelete
    FROM Notice n
    inner join dbo.Rubric r on r.RubricID = n.RubricID
    inner join dbo.Edition e on e.EditionID = r.EditionID
    INNER JOIN UserTypeAccessView uta on uta.NoticeTypeID=n.NoticeTypeID and uta.UserID=@UserID
    INNER JOIN @access c  ON  c.ClientID = n.ClientID and e.DepartmentAccountID = c.DepartmentAccountID
    where n.DeletedFlag=0 and   uta.AccessTypeRead = 1



Заранее спасибо за рекомендации.

К сообщению приложен файл (План выполнения.rar - 13Kb) cкачать
1 мар 13, 17:23    [14000355]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
тормозит так
bo.GetAccessClientsDepartments(1138)


А где код етой ф-ции здесь ?
1 мар 13, 17:25    [14000364]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Andrey1306
Member

Откуда: Киев
Сообщений: 2611
Maxx,

+ код GetAccessClientsDepartments

ALTER FUNCTION [dbo].[GetAccessClientsDepartments]
(	
	-- Add the parameters for the function here
	@UserID int
)
RETURNS TABLE 
AS
RETURN 
	(
		SELECT cc.ClientID,ISNULL(AccessClientAdd,CanAdd) as CanAdd,ISNULL(AccessClientRead,CanRead) as CanRead,
		ISNULL(AccessClientEdit,CanEdit) as CanEdit,
		ISNULL(AccessClientDelete,CanDelete) as CanDelete,da.DepartmentAccountID,ISNULL(ac.IsManager,0) as IsManager,cc.IsActive,cc.ClientShortName,cc.ChiefConfirm,da.DepartmentID
		from ClientCard cc
		cross join DepartmentAccount da
		left outer join AccessClientDepartmentView ac on ac.AccessClientID = cc.ClientID and ac.UserID = @UserID and da.DepartmentAccountID=ac.DepartmentAccountID
		left outer join GetAccessObjectsDepartments(@UserID) ao on ao.ClientID = cc.ClientID and da.DepartmentAccountID=ao.DepartmentAccountID
		
	)



Сама по себе , она быстро выполняется

К сообщению приложен файл (План выполнения 1.rar - 10Kb) cкачать
1 мар 13, 17:29    [14000380]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Maxx
Member [скрыт]

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

выб схемы обьектов научились писать и алисы тоже,а то что откуда у вас береться вообще не понятно откровенно
1 мар 13, 17:31    [14000390]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Andrey1306
Member

Откуда: Киев
Сообщений: 2611
Maxx,

Все в одной базе, схема объектов одна, алисы вроде бы указываются. Если что не так, готов исправить , подскажите только как )
1 мар 13, 17:34    [14000408]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Andrey1306,

Стопроцентный вариант только один, избавление от функции, они всегда были непредсказуемыми.
1 мар 13, 17:35    [14000410]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Andrey1306,

и тормозит у вас скорре всего тут
автор
dbo.GetAccessRight(uta.AccessTypeEdit,c.CanEdit) as CanEdit,
dbo.GetAccessRight(uta.AccessTypeDelete,c.CanDelete) as CanDelete
1 мар 13, 17:36    [14000416]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
да и ф-ция в ф-ции..однако
GetAccessObjectsDepartments

+cross +2 left
При всем етом возращаеться все произведение ,а не фильтрованое по правам юзверя
Чет вы недодумали слегка,как по мне
Зачем вам вся простыня из кросс джойна,если вы проверяете права определенного пользователя ?
1 мар 13, 17:38    [14000423]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Andrey1306
Member

Откуда: Киев
Сообщений: 2611
WarAnt,

Нет , эти скалярки не несут никакой нагрузки , отключал их.
1 мар 13, 17:38    [14000425]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Andrey1306
WarAnt,

Нет , эти скалярки не несут никакой нагрузки , отключал их.


они может и не несут, но усугубляют точно.
1 мар 13, 17:44    [14000449]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Andrey1306
Member

Откуда: Киев
Сообщений: 2611
Maxx
да и ф-ция в ф-ции..однако
GetAccessObjectsDepartments

+cross +2 left
При всем етом возращаеться все произведение ,а не фильтрованое по правам юзверя
Чет вы недодумали слегка,как по мне
Зачем вам вся простыня из кросс джойна,если вы проверяете права определенного пользователя ?


Да права, конкретного пользователя, на доступные ему обьекты ("фирмы") та еще в зависимости от подразделения где он числится.
Но срабатывает быстро сама по себе, без соединения с основной функцией.
1 мар 13, 17:45    [14000454]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Maxx
Member [скрыт]

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

..... как хотите.... сама отрабатывает "быстро" потому как там 3 таблицы + ф-ция
в запросе еще 3 таблицы и 2 скалярки - разницу улавливаете ?
покажите код
GetAccessObjectsDepartments
1 мар 13, 17:47    [14000474]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Andrey1306
Member

Откуда: Киев
Сообщений: 2611
WarAnt
Andrey1306
WarAnt,

Нет , эти скалярки не несут никакой нагрузки , отключал их.


они может и не несут, но усугубляют точно.


Время выполнения , от этого не зависит и разницы в плане выполнения я не вижу
1 мар 13, 17:47    [14000475]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Andrey1306
WarAnt
пропущено...


они может и не несут, но усугубляют точно.


Время выполнения , от этого не зависит и разницы в плане выполнения я не вижу


Дело ваше видно там чтото или нет, практика (по крайне мере моя) всегда показывала что избавление от функций в высоко нагруженных запросах стабилизирует их работу.
1 мар 13, 17:50    [14000496]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Andrey1306
Member

Откуда: Киев
Сообщений: 2611
Maxx
Andrey1306,

..... как хотите.... сама отрабатывает "быстро" потому как там 3 таблицы + ф-ция
в запросе еще 3 таблицы и 2 скалярки - разницу улавливаете ?
покажите код
GetAccessObjectsDepartments


Это Вам еще меньше понравится. Но другого варианта представления иерархии не нашли.

+
ALTER FUNCTION [dbo].[GetAccessObjectsDepartments]
(	
	-- Add the parameters for the function here
	@UserID int
)
RETURNS TABLE 
AS
RETURN 
	(
SELECT TOP 1 with ties c.objectid as ObjectID,c.ClientID,
   AccessObjectRead as CanRead,
   AccessObjectEdit as CanEdit,
   AccessObjectAdd as CanAdd,
   AccessObjectDelete as CanDelete,DepartmentAccountID
FROM AccessObjectDepartmentView 
  CROSS APPLY GetChildOjects(AccessObjectID) c
where UserID = @UserID
order by row_number() OVER ( partition BY c.objectid,DepartmentAccountID ORDER BY LevelNum )



ALTER FUNCTION [dbo].[GetChildOjects]
(	
	-- Add the parameters for the function here
	@ObjectID int
)
RETURNS TABLE 
AS
RETURN 
	(
WITH C (ObjectID,ClientID,  LevelNum) AS
(
	SELECT B.ObjectID,B.ClientID,  0 FROM Object AS B WHERE ObjectID = @ObjectID
	UNION ALL
	SELECT B.ObjectID,B.ClientID, (LevelNum + 1) FROM Object AS B
		INNER JOIN C ON C.ObjectID = B.ObjectParentID
)

SELECT * FROM C
	)


	)
1 мар 13, 17:51    [14000502]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Crimean
Member

Откуда:
Сообщений: 13148
сдается мне, сервер не может определить качество условия ибо ему для выполнения запроса в итоге надо решить

isnull
(
[Notice].[dbo].[AccessClient].[AccessClientRead] as [ao].[AccessClientRead],
[Notice].[dbo].[AccessObject].[AccessObjectRead] as [ao].[AccessObjectRead]
)=(1)

а это решить априори нельзя без скана. вот он и тянет из [Notice].[dbo].[Notice].[PK_Notice] [n] аж 1`652`141 строк, что как бы немало + широкий датасет дает нам только для этой выборки необходимость "прокачать" 6`796 MB, ага?

а ваш "альтернативный вариант" - совсем не альтернативный и остальные танцы вокруг вам не помогут, пока не перестанете "мешать" серверу эффективно выбирать данные, пряча самые эффективные условия в функции, все равно какие
1 мар 13, 17:51    [14000505]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
процедура не подходит.... 1138 - откудь берется?
GetAccessObjectsDepartments(@UserID) - а её код где?
Функции это очень красиво... и там поддерживаемо и прочее..., но за все нужно платить... еще тема view не раскрыта...
И плана нема...
На правах "эротической фантазии":
+

SELECT n.*
,r.RubricName,
r.Rubricator,
r.EditionID,
r.RubricShortName,
r.SortOrder,
r.IsActive,
r.Gnizdo_rayon,
e.DepartmentAccountID,
dbo.GetAccessRight(uta.AccessTypeEdit,c.CanEdit) as CanEdit,
dbo.GetAccessRight(uta.AccessTypeDelete,c.CanDelete) as CanDelete
from 
	(select * from dbo.GetAccessClientsDepartments(1138) where CanRead = 1) c
	join Notice n ON  c.ClientID = n.ClientID
	join dbo.Rubric r on r.RubricID = n.RubricID
	join dbo.Edition e on  e.DepartmentAccountID = c.DepartmentAccountID and e.EditionID = r.EditionID
	join UserTypeAccessView uta on uta.NoticeTypeID=n.NoticeTypeID and uta.UserID=1138
where n.DeletedFlag=0 and uta.AccessTypeRead = 1
1 мар 13, 17:51    [14000506]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Andrey1306
Member

Откуда: Киев
Сообщений: 2611
WarAnt,

Согласен не хорошо это, но так как , функции достались в наследство, переделывать не стал. честно говоря, сразу тоже грешил на эти вычисления скалярных функций для каждой строки...
1 мар 13, 17:54    [14000522]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Andrey1306
Member

Откуда: Киев
Сообщений: 2611
buser
процедура не подходит.... 1138 - откудь берется?
GetAccessObjectsDepartments(@UserID) - а её код где?
Функции это очень красиво... и там поддерживаемо и прочее..., но за все нужно платить... еще тема view не раскрыта...
И плана нема...
На правах "эротической фантазии":
+

SELECT n.*
,r.RubricName,
r.Rubricator,
r.EditionID,
r.RubricShortName,
r.SortOrder,
r.IsActive,
r.Gnizdo_rayon,
e.DepartmentAccountID,
dbo.GetAccessRight(uta.AccessTypeEdit,c.CanEdit) as CanEdit,
dbo.GetAccessRight(uta.AccessTypeDelete,c.CanDelete) as CanDelete
from 
	(select * from dbo.GetAccessClientsDepartments(1138) where CanRead = 1) c
	join Notice n ON  c.ClientID = n.ClientID
	join dbo.Rubric r on r.RubricID = n.RubricID
	join dbo.Edition e on  e.DepartmentAccountID = c.DepartmentAccountID and e.EditionID = r.EditionID
	join UserTypeAccessView uta on uta.NoticeTypeID=n.NoticeTypeID and uta.UserID=1138
where n.DeletedFlag=0 and uta.AccessTypeRead = 1


Подобную фантазию пробовал, не помагает (((
1 мар 13, 17:56    [14000536]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Andrey1306
Это Вам еще меньше понравится

как ето вы догадались
   
   AccessObjectDepartmentView
   AccessClientDepartmentView

надеюсь ,хоть ети вью без ф-ций внутри ?
Я так понимаю у вас совершенно "вертикальные" обьекты и права - жестянка однако
1 мар 13, 17:58    [14000544]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Andrey1306
WarAnt,

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


А я вот наоборот, все что нашел в унаследованном коде исстребил и теперь сплю спокойно:)
1 мар 13, 18:00    [14000553]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Andrey1306
Member

Откуда: Киев
Сообщений: 2611
Maxx
Andrey1306
Это Вам еще меньше понравится

как ето вы догадались
   
   AccessObjectDepartmentView
   AccessClientDepartmentView

надеюсь ,хоть ети вью без ф-ций внутри ?
Я так понимаю у вас совершенно "вертикальные" обьекты и права - жестянка однако


и не говорите, с правами доступа пользователей иногда хочется застрелиться....
1 мар 13, 18:01    [14000558]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Crimean
Member

Откуда:
Сообщений: 13148
ну так "isnull() = 1" уберите или хотя бы на OR замените - все лучше будет
1 мар 13, 18:03    [14000564]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Andrey1306
Member

Откуда: Киев
Сообщений: 2611
WarAnt
Andrey1306
WarAnt,

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


А я вот наоборот, все что нашел в унаследованном коде исстребил и теперь сплю спокойно:)


Истребить не могу, вызовы функций прошиты в клиентском приложении, а на такую кардинальную переделку , я пойти не могу )
1 мар 13, 18:03    [14000565]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос  [new]
Andrey1306
Member

Откуда: Киев
Сообщений: 2611
Crimean,

Убрал не помогло

К сообщению приложен файл (test.rar - 12Kb) cкачать
1 мар 13, 18:50    [14000701]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить