ALTER function [spr].[ifn_GRBS]
(
	@StartDate datetime
	,@EndDate datetime
	,@Variant_ID uniqueidentifier
)
returns table 
as 
return
select
	[ГРБС].ID as [GRBS_ID]
	,[ГРБС].AID as [GRBS_AID]
	,[ГРБС].Catalog_ID
	,[ГРБС].Owner_ID
	,[ГРБС].[GRBS_Vers_ID]
	,[ГРБС].[GRBS_Vers_AID]
	,[ГРБС].StartDate
	,[ГРБС].EndDate
	,[ГРБС].Variant_ID
	,[ГРБС].[code]
	,[ГРБС].[Name]
	,[ГРБС].[NameFull]
	,[ГРБС].[Byudzhet_ID]
	,[ГРБС].Code as [Extended_Code_1]
	,[ГРБС].Code + isnull('  ' + [ГРБС].Name,'') as [Code_Name]
	,[ГРБС].Code + isnull('.' + [Бюджет].Code,'') as [Code_w_Budget]
	,[ГРБС].[Parent_GRBS_ID]
	,[ГРБС].Property
from
	(
	select
		E.ID
		,E.AID
		,E.Catalog_ID
		,E.Owner_ID
		,V.ID as [GRBS_Vers_ID]
		,V.AID as [GRBS_Vers_AID]
		,V.StartDate
		,V.EndDate
		,V.Variant_ID
		,V.[code]
		,V.[Name]
		,V.[NameFull]
		,V.[Byudzhet_ID]
		,V.[Parent_GRBS_ID]
		,V.Property
	from
		spr.[GRBS] as E
		inner join spr.[GRBS_Vers] as V on
			V.[GRBS_ID] = E.ID
	) as [ГРБС]
	left join spr.[ifn_Byudzhet](@StartDate, @EndDate, @Variant_ID) as [Бюджет] on
		[Бюджет].[Byudzhet_ID] = [ГРБС].[Byudzhet_ID]
where
	[ГРБС].Variant_ID = @Variant_ID
	and [ГРБС].StartDate = (
											select
												max(_VERS.StartDate) as StartDate 
											from
												spr.[GRBS_Vers] as _VERS
											where
												_VERS.[GRBS_ID] = [ГРБС].ID
												and _VERS.Variant_ID = @Variant_ID
												and (
														_VERS.StartDate < @EndDate
														or @EndDate is null
													)
										)
	and ([ГРБС].EndDate > isnull(@StartDate, '19000101') or [ГРБС].EndDate is null)

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
-- с перекладкой в таблицу		1 141 чтений	----------------------------------------
	
End of simplification, time: 0.03 net: 0.03 total: 0.03 net: 0.03
end exploration, tasks: 4648 no total cost time: 0.121 net: 0.121 total: 0.151 net: 0.151

end search(0),  cost: 24.4476 tasks: 13404 time: 0.108 net: 0.108 total: 0.26 net: 0.259
end exploration, tasks: 17500 Cost = 24.4476 time: 0.031 net: 0.031 total: 0.291 net: 0.291

end search(1),  cost: 14.2898 tasks: 29075 time: 0.165 net: 0.165 total: 0.457 net: 0.457
*** Optimizer time out abort at task 29075 ***

End of post optimization rewrite, time: 0.003 net: 0.003 total: 0.46 net: 0.46
End of query plan compilation, time: 0.007 net: 0.007 total: 0.468 net: 0.468

----------------------------------------------------------------------------------------
-- без перекладки в таблицу		76 248 чтений ------------------------------------------

End of simplification, time: 0.029 net: 0.029 total: 0.029 net: 0.029
end exploration, tasks: 4640 no total cost time: 0.12 net: 0.12 total: 0.15 net: 0.15

end search(0),  cost: 12.7613 tasks: 13384 time: 0.106 net: 0.106 total: 0.256 net: 0.256
*** Optimizer time out abort at task 15497 ***

end search(1),  cost: 12.7613 tasks: 15497 time: 0.015 net: 0.015 total: 0.272 net: 0.272
*** Optimizer time out abort at task 15497 ***

End of post optimization rewrite, time: 0.003 net: 0.003 total: 0.275 net: 0.275
End of query plan compilation, time: 0.007 net: 0.007 total: 0.283 net: 0.283


--------------- отключаем таймаут =======================================================
-- с перекладкой в таблицу		1 141 чтений ТАК И ОСТАЛОСЬ 	--------------------------

End of simplification, time: 0.026 net: 0.026 total: 0.026 net: 0.026
end exploration, tasks: 4648 no total cost time: 0.105 net: 0.105 total: 0.131 net: 0.131

end search(0),  cost: 24.4476 tasks: 13404 time: 0.092 net: 0.092 total: 0.223 net: 0.223
end exploration, tasks: 17500 Cost = 24.4476 time: 0.028 net: 0.028 total: 0.252 net: 0.252

end search(1),  cost: 14.2898 tasks: 29075 time: 0.151 net: 0.151 total: 0.403 net: 0.403
end exploration, tasks: 29268 Cost = 14.2898 time: 0.003 net: 0.003 total: 0.407 net: 0.406
end search(2),  cost: 14.2898 tasks: 30237 time: 0.006 net: 0.006 total: 0.413 net: 0.413
End of post optimization rewrite, time: 0.002 net: 0.002 total: 0.416 net: 0.416
End of query plan compilation, time: 0.005 net: 0.005 total: 0.422 net: 0.422

--------------------------------------------------------------------------------------------------
-- без перекладки в таблицу	Стало 1 141 чтений (а ведь мы же уже и данные на клиент послали)  ----

End of simplification, time: 0.029 net: 0.029 total: 0.029 net: 0.029
end exploration, tasks: 4640 no total cost time: 0.121 net: 0.121 total: 0.15 net: 0.15

end search(0),  cost: 12.7613 tasks: 13384 time: 0.106 net: 0.106 total: 0.256 net: 0.256
end exploration, tasks: 17474 Cost = 12.7613 time: 0.032 net: 0.032 total: 0.289 net: 0.289

end search(1),  cost: 2.60349 tasks: 29037 time: 0.17 net: 0.17 total: 0.459 net: 0.459
end exploration, tasks: 29224 Cost = 2.60349 time: 0.003 net: 0.003 total: 0.463 net: 0.463
end search(2),  cost: 2.60349 tasks: 30181 time: 0.007 net: 0.007 total: 0.47 net: 0.47

End of post optimization rewrite, time: 0.003 net: 0.003 total: 0.473 net: 0.473
End of query plan compilation, time: 0.006 net: 0.006 total: 0.479 net: 0.479


--------------- отключаем таймаут + включаем сортировки =======================================================
-- с перекладкой в таблицу		1 141

End of simplification, time: 0.026 net: 0.026 total: 0.026 net: 0.026
end exploration, tasks: 4649 no total cost time: 0.1 net: 0.1 total: 0.126 net: 0.126
end search(0),  cost: 24.5543 tasks: 13483 time: 0.093 net: 0.093 total: 0.22 net: 0.22
end exploration, tasks: 17580 Cost = 24.5543 time: 0.028 net: 0.028 total: 0.248 net: 0.248
end search(1),  cost: 14.3965 tasks: 31056 time: 0.166 net: 0.166 total: 0.414 net: 0.414
end exploration, tasks: 31250 Cost = 14.3965 time: 0.003 net: 0.003 total: 0.417 net: 0.417
end search(2),  cost: 14.3965 tasks: 32407 time: 0.007 net: 0.007 total: 0.425 net: 0.425
End of post optimization rewrite, time: 0.002 net: 0.002 total: 0.428 net: 0.428
End of query plan compilation, time: 0.005 net: 0.005 total: 0.434 net: 0.434

-- без перекладки			1 141
End of simplification, time: 0.031 net: 0.031 total: 0.031 net: 0.031
end exploration, tasks: 4640 no total cost time: 0.125 net: 0.125 total: 0.157 net: 0.157
end exploration, tasks: 13465 no total cost time: 0.112 net: 0.112 total: 0.27 net: 0.27
end search(0),  cost: 12.8679 tasks: 13472 time: 0 net: 0 total: 0.27 net: 0.27
end exploration, tasks: 17562 Cost = 12.8679 time: 0.034 net: 0.034 total: 0.304 net: 0.304
end exploration, tasks: 31029 Cost = 12.8679 time: 0.206 net: 0.206 total: 0.511 net: 0.511
end search(1),  cost: 2.71012 tasks: 31036 time: 0 net: 0 total: 0.511 net: 0.511
end exploration, tasks: 31223 Cost = 2.71012 time: 0.004 net: 0.004 total: 0.515 net: 0.515
end exploration, tasks: 32371 Cost = 2.71012 time: 0.009 net: 0.009 total: 0.525 net: 0.525
end search(2),  cost: 2.71012 tasks: 32378 time: 0 net: 0 total: 0.525 net: 0.525
End of post optimization rewrite, time: 0.003 net: 0.003 total: 0.528 net: 0.528
End of query plan compilation, time: 0.006 net: 0.006 total: 0.535 net: 0.535