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

Откуда: Москва
Сообщений: 1176
День добрый!
есть некая система раздачи прав на дерево.
Есть крутой пользователь, которому доступно все, и есть обычный, которому доступна маленькая часть.
Все это уже посчитано и положено в кеши.
Соответтвенно, при чтении для обычного пользователя надо лезть в 2 таблицы, а для крутого в одну.

Попытался завернуть в Инлайн функции.
Но тогда либо надо писать внутрь union, либо делать 2 отдельных вызова, что приводит к задвоение больших кусков кода. маленький пример

if @FullAccess = 1	

	insert into @Request_Source_Level
		select 
			Classifier_ID, 
			Parent_Classifier_ID,
			15
		from cache.ifn_Hierarchy_Items(@Hierarchy_ID)
		where
			Parent_Classifier_ID is null
			--Classifier_ID = @Root_Classifier_ID
else 
	
	insert into @Request_Source_Level
		select 
			Classifier_ID, 
			Parent_Classifier_ID,
			Access_Type
		from cache.ifn_Hierarchy_ItemsAccess(@HierarchyAccess_ID)
		where
			Parent_Classifier_ID is null


union будет ухудшать план. + возможно еще и неудачно компилиться попадая в разные части функции
select ...
where @FullAccess = 1	

union all

select ...
where @FullAccess = 0	


таблички будут большие, и предназначаются для оптимизации и ускорения работы)))
25 авг 15, 14:01    [18064673]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
собственно вопрос.
Как такое можно разрулить наиболее красиво?

tfn тоже не очень подходят, ибо их уже не фильтранешь внутри.
25 авг 15, 14:03    [18064692]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
Mike_za
union будет ухудшать план. + возможно еще и неудачно компилиться попадая в разные части функции
select ...
where @FullAccess = 1	

union all

select ...
where @FullAccess = 0	
В плане будет Concatenation + Filter'ы с Startup Expression Predicate.
В результате физически будет выполняться запрос только из одной части union all. Это хорошо видно в статистике IO.
25 авг 15, 14:29    [18064893]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31863
Mike_za
union будет ухудшать план
А uniion all не будет
25 авг 15, 15:46    [18065541]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
может я не туда смотрю?
25 авг 15, 15:50    [18065582]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
Mike_za
Member

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


К сообщению приложен файл. Размер - 109Kb
25 авг 15, 15:53    [18065612]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
Mike_za
Member

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

alter function cache.ifn_Hierarchy_Items_All(@Hierarchy_ID int, @HierarchyAccess_ID int)
returns table 
as
return 

	select 
		ItemAccess.Access_Type
		,ItemAccess.Child_Count
		,Item.Hierarchy_ID
		,Item.Classifier_ID
		,Item.AID
		,Item.Catalog_ID
		,Item.Owner_ID
		,Item.Vers_ID
		,Item.Vers_AID
		,Item.StartDate
		,Item.EndDate
		,Item.Variant_ID
		,Item.UBP_ID
		,Item.Email
		,Item.Order_Number
		,Item.Catalog_Code
		,Item.Code
		,Item.Name
		,Item.Parent_Classifier_ID
		,Item.Property
	from 
		cache.HierarchyItemAccess			ItemAccess	
		inner join cache.HierarchyItem		Item		on Item.Hierarchy_ID = @Hierarchy_ID and Item.Classifier_ID = ItemAccess.Classifier_ID
	where 
		@HierarchyAccess_ID is not null AND ItemAccess.HierarchyAccess_ID = @HierarchyAccess_ID
	
	union 

	
	select 
		 [Access_Type]		=	15
		,[Child_Count]		= 0
		,Item.Hierarchy_ID
		,Item.Classifier_ID
		,Item.AID
		,Item.Catalog_ID
		,Item.Owner_ID
		,Item.Vers_ID
		,Item.Vers_AID
		,Item.StartDate
		,Item.EndDate
		,Item.Variant_ID
		,Item.UBP_ID
		,Item.Email
		,Item.Order_Number
		,Item.Catalog_Code
		,Item.Code
		,Item.Name
		,Item.Parent_Classifier_ID
		,Item.Property
	from 
		cache.HierarchyItem		Item
	where 
		@HierarchyAccess_ID is null AND Item.Hierarchy_ID = @Hierarchy_ID



alter function cache.ifn_Hierarchy_ItemsWithAccess(@Hierarchy_ID int, @HierarchyAccess_ID int)
returns table 
as
return 

	select 
		ItemAccess.Access_Type
		,ItemAccess.Child_Count
		,Item.Hierarchy_ID
		,Item.Classifier_ID
		,Item.AID
		,Item.Catalog_ID
		,Item.Owner_ID
		,Item.Vers_ID
		,Item.Vers_AID
		,Item.StartDate
		,Item.EndDate
		,Item.Variant_ID
		,Item.UBP_ID
		,Item.Email
		,Item.Order_Number
		,Item.Catalog_Code
		,Item.Code
		,Item.Name
		,Item.Parent_Classifier_ID
		,Item.Property
	from 
		cache.HierarchyItemAccess			ItemAccess	
		inner join cache.HierarchyItem		Item		on Item.Hierarchy_ID = @Hierarchy_ID and Item.Classifier_ID = ItemAccess.Classifier_ID
	where 
		ItemAccess.HierarchyAccess_ID = @HierarchyAccess_ID
25 авг 15, 15:58    [18065670]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4831
Mike_za
union будет ухудшать план. + возможно еще и неудачно компилиться попадая в разные части функции



UNION может ухудшить (и то не факт), а вот UNION ALL не должен

я бы пошел таким путём

WITH CTE AS
(
		select 
			Classifier_ID, 
			Parent_Classifier_ID,
			Access_Type
		from cache.ifn_Hierarchy_ItemsAccess(@HierarchyAccess_ID)
		where
			Parent_Classifier_ID is null
)
SELECT 
	Classifier_ID, 
	Parent_Classifier_ID,
	15
FROM CTE 
WHERE @FullAccess = 1	
UNION ALL 
SELECT 
	Classifier_ID, 
	Parent_Classifier_ID,
	Access_Type
FROM CTE 
WHERE @FullAccess = 0	
25 авг 15, 15:59    [18065694]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
разумеется, я имел ввиду Union ALL, что и написано в примере в первом посте
25 авг 15, 16:39    [18066103]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31863
Mike_za
разумеется, я имел ввиду Union ALL, что и написано в примере в первом посте
А вот в функции cache.ifn_Hierarchy_Items_All просто Union
25 авг 15, 16:54    [18066208]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
alexeyvg
Mike_za
разумеется, я имел ввиду Union ALL, что и написано в примере в первом посте
А вот в функции cache.ifn_Hierarchy_Items_All просто Union

блиииин.... пошел перемерять)
25 авг 15, 17:02    [18066274]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
Mike_za
Member

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

К сообщению приложен файл. Размер - 148Kb
25 авг 15, 17:08    [18066312]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
Mike_za
все равно. сильно лучше не стало.
Какие у вас критерии оценки "лучшести"?
25 авг 15, 17:17    [18066367]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
invm, количество чтений, CPU, Duration
на текущий момент данных в таблицах почти 0, но уже разница видна
25 авг 15, 17:23    [18066402]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31863
Mike_za
Оптимизатор перестраивает порядок соединений, и делает мерже вместо лууп....
Эээ, не вижу.
После изменения на union all теперь вместо merge работает concatenation, а с таблицей HierarchyItemAccess как раз идёт лууп.

Всё, как вы и хотели.

А то, что итоговая количество чтений то же, значит, просто merge двух результатов в union был очень дешёвый.
25 авг 15, 17:25    [18066427]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
Mike_za
Member

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

https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1172378&msg=18066312

в одном случае отдельная функция, во втором общая с юнионом алл.
количество чтений отличается в 1.5 раза
25 авг 15, 17:28    [18066443]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
152 - это общая
25 авг 15, 17:29    [18066449]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
alexeyvg
Member

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

https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1172378&msg=18066312

в одном случае отдельная функция, во втором общая с юнионом алл.
количество чтений отличается в 1.5 раза
А, понял, я думал, вы наоборот от мерджа хотите избавится, а он выгоднее, чем лууп.
25 авг 15, 17:33    [18066488]     Ответить | Цитировать Сообщить модератору
 Re: дублирование кода  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
Mike_za
invm, количество чтений, CPU, Duration
на текущий момент данных в таблицах почти 0, но уже разница видна
Я не знаю как и что вы проверяете. На простом репро видны все спецэффекты:
+
use tempdb;
go

create table dbo.t1 (id int primary key, v int);
create table dbo.t2 (id int primary key, v int);

insert into dbo.t1 values (1, 1), (2, 2);
insert into dbo.t2 values (1, 3), (2, 4);
go

create function dbo.fn1
(
 @f int
)
returns table as
return (
 select id, v from dbo.t1 where @f = 1
 union all
 select id, v from dbo.t2 where @f = 2
);
go


1.
select * from dbo.fn1(1);
go
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgument
21
select * from dbo.fn1(1);
110NULLNULLNULL
00
  |--Compute Scalar(DEFINE:([Union1006]=[tempdb].[dbo].[t1].[id], [Union1007]=[tempdb].[dbo].[t1].[v]))
121Compute ScalarCompute ScalarDEFINE:([Union1006]=[tempdb].[dbo].[t1].[id], [Union1007]=[tempdb].[dbo].[t1].[v])
21
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t1].[PK__t1__3213E83F4948CC7D]))
132Clustered Index ScanClustered Index ScanOBJECT:([tempdb].[dbo].[t1].[PK__t1__3213E83F4948CC7D])

Таблица "t1". Число просмотров 1, логических чтений 2, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

2.
select * from dbo.fn1(2);
go
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgument
21
select * from dbo.fn1(2);
110NULLNULLNULL
00
  |--Compute Scalar(DEFINE:([Union1006]=[tempdb].[dbo].[t2].[id], [Union1007]=[tempdb].[dbo].[t2].[v]))
121Compute ScalarCompute ScalarDEFINE:([Union1006]=[tempdb].[dbo].[t2].[id], [Union1007]=[tempdb].[dbo].[t2].[v])
21
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t2].[PK__t2__3213E83F4D195D61]))
132Clustered Index ScanClustered Index ScanOBJECT:([tempdb].[dbo].[t2].[PK__t2__3213E83F4D195D61])

Таблица "t2". Число просмотров 1, логических чтений 2, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

3.
declare @f int = 1; select * from dbo.fn1(@f);
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgument
21
select * from dbo.fn1(@f);
110NULLNULLNULL
21
  |--Concatenation
121ConcatenationConcatenationNULL
21
       |--Filter(WHERE:(STARTUP EXPR([@f]=(1))))
132FilterFilterWHERE:(STARTUP EXPR([@f]=(1)))
21
       |    |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t1].[PK__t1__3213E83F4948CC7D]))
143Clustered Index ScanClustered Index ScanOBJECT:([tempdb].[dbo].[t1].[PK__t1__3213E83F4948CC7D])
01
       |--Filter(WHERE:(STARTUP EXPR([@f]=(2))))
162FilterFilterWHERE:(STARTUP EXPR([@f]=(2)))
00
            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t2].[PK__t2__3213E83F4D195D61]))
176Clustered Index ScanClustered Index ScanOBJECT:([tempdb].[dbo].[t2].[PK__t2__3213E83F4D195D61])

Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "t1". Число просмотров 1, логических чтений 2, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

4.
declare @f int = 2; select * from dbo.fn1(@f);
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgument
21
select * from dbo.fn1(@f);
110NULLNULLNULL
21
  |--Concatenation
121ConcatenationConcatenationNULL
01
       |--Filter(WHERE:(STARTUP EXPR([@f]=(1))))
132FilterFilterWHERE:(STARTUP EXPR([@f]=(1)))
00
       |    |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t1].[PK__t1__3213E83F4948CC7D]))
143Clustered Index ScanClustered Index ScanOBJECT:([tempdb].[dbo].[t1].[PK__t1__3213E83F4948CC7D])
21
       |--Filter(WHERE:(STARTUP EXPR([@f]=(2))))
162FilterFilterWHERE:(STARTUP EXPR([@f]=(2)))
21
            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t2].[PK__t2__3213E83F4D195D61]))
176Clustered Index ScanClustered Index ScanOBJECT:([tempdb].[dbo].[t2].[PK__t2__3213E83F4D195D61])

Таблица "t2". Число просмотров 1, логических чтений 2, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

Само-собой, такое написание функции ведет к перекомпиляциям.
С union практически тоже самое, только в (3) и (4) вместо Concatenation будет Merge (Union).
25 авг 15, 19:09    [18067086]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить