Оптимизация selecta - большаи таблици

Vitaliy_Honcharuk
Дата: 12.02.2013 13:13:26
Всем привет

Как можна оптимизуровать?

SELECT     cInv_Nomer, 
nKilk,
nId_Client,
dbo.FN_NEA_NOMER_CEH(nId_ceh) AS nId_Ceh, 
cNaz_OZ,
cHarakt_Oz, 
dDat_Vvodu,  
dbo.FN_NEA_DATA(FN_NEA_OZA_ALL_1.dDat_Vyb) AS dDat_Vyb,
nPoc_Vart_Buh,
dbo.FN_NEA_OZA_PEREOC(@data,cInv_Nomer) AS nDooc1, 
nBal_Vart_Buh, nLik_Vart_Buh,nSum_Znos_Buh, nMis_Amor,
nGr_OZ_Buh, 
dbo.FN_DATA_KOD_AMOR(cinv_nomer,@data) as cKod_GOZ_Buh, 
dDat_Kin_Buh,
cRah_Oblik,
rtrim(cRah_D)+dbo.fn_nea_da(nda1)+dbo.fn_nea_da(nda2)+dbo.fn_nea_da(nda3)+dbo.fn_nea_da(nda4)+dbo.fn_nea_da(nda5)+dbo.fn_nea_da(nda6) as cRah_D, 
rtrim(cRah_K)+dbo.fn_nea_da(nka1)+dbo.fn_nea_da(nka2)+dbo.fn_nea_da(nka3) as cRah_K,
dbo.fn_nea_name_client(nId_Client) as cName_Org, 
nId_Klas,
nId_ClientMisce,
dbo.FN_MONTH_BETWEEN(@data, ddat_kin_buh) AS nZal_Mis, 
nDooc_Buh as nDooc
FROM         dbo.FN_NEA_OZA_ALL_ARHIV(@data, @vyr, @ceh, @grupa, @kod_amort, @rah, @kimnata, @mvo, @misce) 
                      AS FN_NEA_OZA_ALL_1 LEFT OUTER JOIN
                      GD_TOP_PROV ON FN_NEA_OZA_ALL_1.nId_TOp_Oderg = GD_TOP_PROV.nId_TOp_GOP AND 
                      FN_NEA_OZA_ALL_1.nId_PROV_Oderg = GD_TOP_PROV.nId_TOp_PROV 
WHERE     (GD_TOP_PROV.cK LIKE @prov) AND (FN_NEA_OZA_ALL_1.cRah_D LIKE @rah_d) AND (FN_NEA_OZA_ALL_1.cRah_K LIKE @rah_k) AND 
                      (CAST(FN_NEA_OZA_ALL_1.nDa1 AS varchar(10)) LIKE @da1) AND (CAST(FN_NEA_OZA_ALL_1.nDa2 AS varchar(10)) LIKE @da2) AND 
                      (CAST(FN_NEA_OZA_ALL_1.nDa3 AS varchar(10)) LIKE @da3) AND (CAST(FN_NEA_OZA_ALL_1.nKa1 AS varchar(10)) LIKE @ka1) AND 
                      (CAST(FN_NEA_OZA_ALL_1.nKa2 AS varchar(10)) LIKE @ka2) AND (CAST(FN_NEA_OZA_ALL_1.nKa3 AS varchar(10)) LIKE @ka3) and cast(FN_NEA_OZA_ALL_1.nId_Sfera as varchar(2)) like @sfera and FN_NEA_OZA_ALL_1.cinv_nomer like @nomer



Вот функции что в селекте - через которие очень долго виполняєится запрос:

CREATE FUNCTION dbo.FN_NEA_OZA_PEREOC (@data datetime, @nomer varchar(13))
RETURNS decimal(14,2)
AS
begin
declare @p decimal(14,2)
set @p=0
	SELECT @p=SUM(L_OZA_RUH.nPoc_Vart_Buh)
FROM         L_OZA_RUH LEFT OUTER JOIN
                      GD_TYPE_OPER ON L_OZA_RUH.nId_TypeOper = GD_TYPE_OPER.nId_TypeOper
WHERE     (L_OZA_RUH.cInv_Nomer = @nomer) AND (GD_TYPE_OPER.cPriznak like 'ц%') and (dbo.fn_nea_data(dDat_Oper)<=dbo.fn_nea_data(@data))
return @p
end
go


CREATE function dbo.FN_DATA_KOD_AMOR(@cInv_Nomer varchar(13),@data datetime)
returns int
as begin
declare @client char(6)
declare @count int
declare @is_ruh int
declare @max_data datetime  -- дата останньої зміни клієнта
declare @min_data datetime  -- дата першої зміни клієнта

-- перевиримо, чи взагалі міняли кліента
select @is_Ruh = count(cInv_Nomer),@max_data = max(dbo.fn_nea_data(ddat_oper)),@min_data = min(dbo.fn_nea_data(ddat_oper)) from l_oza_ruh where cInv_Nomer=@cInv_Nomer and cKod_Goz_Buh is not null -- чи є номер в архіві на вказану дату

if(@is_ruh<=0) -- ніякої зміни клієнта не було, повертаємо клієнта прямо з карточки
BEGIN
select @client=cKod_Goz_Buh from l_oza_kart where cInv_Nomer=@cInv_Nomer
END
ELSE
BEGIN --були зміни клієнта
-- перевіримо, чи є номер в архіві на вказану дату. Якщо є то вертаємо клієнта з архіву
select @count = count(cInv_Nomer) from l_oza_kart_arhiv where cInv_Nomer=@cInv_Nomer and ddat_period=@data -- чи є номер в архіві на вказану дату

declare @is int -- чи є номер в аріві
set @is = dbo.fn_is_number_in_arhiv(@cinv_nomer)

if(@count>=1) -- номер є прямо в архіві
begin
	-- номер є в архіві на вказану дату - повертаємо клієнта з архіву за цей місяць
	select @client=cKod_Goz_Buh from l_oza_kart_arhiv where cInv_Nomer=@cInv_Nomer and ddat_period=@data
end


if(@is=1 and @count=0)-- номер є в архіві але на вказану дату немає - дата звіту більша за архів
begin
	--визначу чи були зміни клієнта від дати останнього архіву до дати звіту
	select @count = count(cinv_nomer) from l_oza_ruh where cInv_Nomer=@cInv_Nomer and cKod_Goz_Buh is not null and  
			dbo.fn_nea_data(ddat_oper)>dbo.FN_MAX_DATA_IN_ARHIV(@cInv_Nomer) and dbo.fn_nea_data(ddat_oper)<=dbo.fn_nea_data(@data)
	if(@count>0) -- були зміни клієнта
	begin
		--вичислю дату, найближчу до дати звіту
-- 		select top 1 @client = nId_ClientMisce from l_oza_ruh where cInv_Nomer=@cInv_Nomer and nId_ClientMisce!=0 and  
--          			dbo.fn_nea_data(ddat_oper)>dbo.FN_MAX_DATA_IN_ARHIV(cInv_Nomer) and dbo.fn_nea_data(ddat_oper)<=dbo.fn_nea_data(@data)
-- 		order by ddat_oper desc,nId_operruh desc
		if(@data<@min_data)
		begin
			select top 1 @client = cKod_Goz_Buh from l_oza_ruh where cInv_Nomer=@cInv_Nomer and cKod_Goz_Buh is not null and 
				dbo.fn_nea_data(ddat_oper)=@min_data
			order by ddat_oper desc,nid_operruh desc
		end		
	if(@data>=@min_data and @data<@max_data)
		Begin
			select top 1 @client = cKod_Goz_Buh from l_oza_ruh where cInv_Nomer=@cInv_Nomer and cKod_Goz_Buh is not null and
				dbo.fn_nea_data(ddat_oper)>@data and dbo.fn_nea_data(ddat_oper)<=@max_data
			order by ddat_oper desc,nid_operruh desc
		End		
		if(@data>=@max_data)
		Begin
			select @client = cKod_Goz_Buh from l_oza_kart where cInv_Nomer=@cInv_Nomer 

		End

	end
	else
	begin
		-- дата звіту більша за дату архіву і змін клієнта не було - вертаємо клієнта з останнього архіву
		select @client=cKod_Goz_Buh from l_oza_kart_arhiv where cInv_Nomer=@cInv_Nomer and ddat_period=dbo.FN_MAX_DATA_IN_ARHIV(@cInv_Nomer)
	end

end

if(@is=0 and @count=0)-- номера в архіві немає
Begin
	if(@data<@min_data)
	begin
		select top 1 @client = cKod_Goz_Buh from l_oza_ruh where cInv_Nomer=@cInv_Nomer and cKod_Goz_Buh is not null and 
			dbo.fn_nea_data(ddat_oper)=@min_data
		order by ddat_oper desc,nid_operruh desc
	end
	if(@data>=@min_data and @data<@max_data)
	Begin
		select top 1 @client = cKod_Goz_Buh from l_oza_ruh where cInv_Nomer=@cInv_Nomer and cKod_Goz_Buh is not null and
			dbo.fn_nea_data(ddat_oper)>@data and dbo.fn_nea_data(ddat_oper)<=@max_data
		order by ddat_oper desc,nid_operruh desc
	End
	if(@data>=@max_data)
	Begin
		select @client = cKod_Goz_Buh from l_oza_kart where cInv_Nomer=@cInv_Nomer

	End

End
END
return @client
end
go


Можете помочь?
Мистер Хенки
Дата: 12.02.2013 13:23:38
почитать про sargable предикаты, переделать на inline table функции или вообще без них написать запрос, создать подходящие индексы
aleks2
Дата: 12.02.2013 15:02:15
CREATE FUNCTION dbo.FN_NEA_OZA_PEREOC (@data datetime, @nomer varchar(13))
RETURNS decimal(14,2)
AS
begin
return (SELECT SUM(L_OZA_RUH.nPoc_Vart_Buh)
FROM         L_OZA_RUH LEFT OUTER JOIN
                      GD_TYPE_OPER ON L_OZA_RUH.nId_TypeOper = GD_TYPE_OPER.nId_TypeOper
WHERE     (L_OZA_RUH.cInv_Nomer = @nomer) AND (GD_TYPE_OPER.cPriznak like 'ц%') and (dbo.fn_nea_data(dDat_Oper)<=dbo.fn_nea_data(@data))

)
end
go
-- dbo.fn_nea_data(dDat_Oper) Чо это фуфло делает с датой?


CREATE function dbo.FN_DATA_KOD_AMOR(@cInv_Nomer varchar(13),@data datetime)
returns int
as begin
declare @client char(6)
declare @max_data datetime  -- дата останньої зміни клієнта
declare @min_data datetime  -- дата першої зміни клієнта

-- перевиримо, чи взагалі міняли кліента
select top(1) @client=cKod_Goz_Buh from l_oza_kart where cInv_Nomer=@cInv_Nomer;
if @client is not null  return @client;

--були зміни клієнта
-- перевіримо, чи є номер в архіві на вказану дату. Якщо є то вертаємо клієнта з архіву
select top(1) @client=cKod_Goz_Buh from l_oza_kart_arhiv where cInv_Nomer=@cInv_Nomer and ddat_period=@data;
if @client is not null return @client;

declare @is int -- чи є номер в аріві
set @is = dbo.fn_is_number_in_arhiv(@cinv_nomer)

if(@is=1)-- номер є в архіві але на вказану дату немає - дата звіту більша за архів
begin
    -- дальше мне лень разгребать этот говнокод...
    -- Но суть - та же.
...
End
END
return @client
end
go
_ч_
Дата: 12.02.2013 15:14:56
Vitaliy_Honcharuk
Всем привет

Как можна оптимизуровать?

SELECT     cInv_Nomer, 
nKilk,
nId_Client,
dbo.FN_NEA_NOMER_CEH(nId_ceh) AS nId_Ceh, 
cNaz_OZ,
cHarakt_Oz, 
dDat_Vvodu,  
dbo.FN_NEA_DATA(FN_NEA_OZA_ALL_1.dDat_Vyb) AS dDat_Vyb,
nPoc_Vart_Buh,
dbo.FN_NEA_OZA_PEREOC(@data,cInv_Nomer) AS nDooc1, 
nBal_Vart_Buh, nLik_Vart_Buh,nSum_Znos_Buh, nMis_Amor,
nGr_OZ_Buh, 
dbo.FN_DATA_KOD_AMOR(cinv_nomer,@data) as cKod_GOZ_Buh, 
dDat_Kin_Buh,
cRah_Oblik,
rtrim(cRah_D)+dbo.fn_nea_da(nda1)+dbo.fn_nea_da(nda2)+dbo.fn_nea_da(nda3)+dbo.fn_nea_da(nda4)+dbo.fn_nea_da(nda5)+dbo.fn_nea_da(nda6) as cRah_D, 
rtrim(cRah_K)+dbo.fn_nea_da(nka1)+dbo.fn_nea_da(nka2)+dbo.fn_nea_da(nka3) as cRah_K,
dbo.fn_nea_name_client(nId_Client) as cName_Org, 
nId_Klas,
nId_ClientMisce,
dbo.FN_MONTH_BETWEEN(@data, ddat_kin_buh) AS nZal_Mis, 
nDooc_Buh as nDooc
FROM         dbo.FN_NEA_OZA_ALL_ARHIV(@data, @vyr, @ceh, @grupa, @kod_amort, @rah, @kimnata, @mvo, @misce) 
                      AS FN_NEA_OZA_ALL_1 LEFT OUTER JOIN
                      GD_TOP_PROV ON FN_NEA_OZA_ALL_1.nId_TOp_Oderg = GD_TOP_PROV.nId_TOp_GOP AND 
                      FN_NEA_OZA_ALL_1.nId_PROV_Oderg = GD_TOP_PROV.nId_TOp_PROV 
WHERE     (GD_TOP_PROV.cK LIKE @prov) AND (FN_NEA_OZA_ALL_1.cRah_D LIKE @rah_d) AND (FN_NEA_OZA_ALL_1.cRah_K LIKE @rah_k) AND 
                      (CAST(FN_NEA_OZA_ALL_1.nDa1 AS varchar(10)) LIKE @da1) AND (CAST(FN_NEA_OZA_ALL_1.nDa2 AS varchar(10)) LIKE @da2) AND 
                      (CAST(FN_NEA_OZA_ALL_1.nDa3 AS varchar(10)) LIKE @da3) AND (CAST(FN_NEA_OZA_ALL_1.nKa1 AS varchar(10)) LIKE @ka1) AND 
                      (CAST(FN_NEA_OZA_ALL_1.nKa2 AS varchar(10)) LIKE @ka2) AND (CAST(FN_NEA_OZA_ALL_1.nKa3 AS varchar(10)) LIKE @ka3) and cast(FN_NEA_OZA_ALL_1.nId_Sfera as varchar(2)) like @sfera and FN_NEA_OZA_ALL_1.cinv_nomer like @nomer



Вот функции что в селекте - через которие очень долго виполняєится запрос:

CREATE FUNCTION dbo.FN_NEA_OZA_PEREOC (@data datetime, @nomer varchar(13))
RETURNS decimal(14,2)
AS
begin
declare @p decimal(14,2)
set @p=0
	SELECT @p=SUM(L_OZA_RUH.nPoc_Vart_Buh)
FROM         L_OZA_RUH LEFT OUTER JOIN
                      GD_TYPE_OPER ON L_OZA_RUH.nId_TypeOper = GD_TYPE_OPER.nId_TypeOper
WHERE     (L_OZA_RUH.cInv_Nomer = @nomer) AND (GD_TYPE_OPER.cPriznak like 'ц%') and (dbo.fn_nea_data(dDat_Oper)<=dbo.fn_nea_data(@data))
return @p
end
go


CREATE function dbo.FN_DATA_KOD_AMOR(@cInv_Nomer varchar(13),@data datetime)
returns int
as begin
declare @client char(6)
declare @count int
declare @is_ruh int
declare @max_data datetime  -- дата останньої зміни клієнта
declare @min_data datetime  -- дата першої зміни клієнта

-- перевиримо, чи взагалі міняли кліента
select @is_Ruh = count(cInv_Nomer),@max_data = max(dbo.fn_nea_data(ddat_oper)),@min_data = min(dbo.fn_nea_data(ddat_oper)) from l_oza_ruh where cInv_Nomer=@cInv_Nomer and cKod_Goz_Buh is not null -- чи є номер в архіві на вказану дату

if(@is_ruh<=0) -- ніякої зміни клієнта не було, повертаємо клієнта прямо з карточки
BEGIN
select @client=cKod_Goz_Buh from l_oza_kart where cInv_Nomer=@cInv_Nomer
END
ELSE
BEGIN --були зміни клієнта
-- перевіримо, чи є номер в архіві на вказану дату. Якщо є то вертаємо клієнта з архіву
select @count = count(cInv_Nomer) from l_oza_kart_arhiv where cInv_Nomer=@cInv_Nomer and ddat_period=@data -- чи є номер в архіві на вказану дату

declare @is int -- чи є номер в аріві
set @is = dbo.fn_is_number_in_arhiv(@cinv_nomer)

if(@count>=1) -- номер є прямо в архіві
begin
	-- номер є в архіві на вказану дату - повертаємо клієнта з архіву за цей місяць
	select @client=cKod_Goz_Buh from l_oza_kart_arhiv where cInv_Nomer=@cInv_Nomer and ddat_period=@data
end


if(@is=1 and @count=0)-- номер є в архіві але на вказану дату немає - дата звіту більша за архів
begin
	--визначу чи були зміни клієнта від дати останнього архіву до дати звіту
	select @count = count(cinv_nomer) from l_oza_ruh where cInv_Nomer=@cInv_Nomer and cKod_Goz_Buh is not null and  
			dbo.fn_nea_data(ddat_oper) > dbo.FN_MAX_DATA_IN_ARHIV(@cInv_Nomer) and dbo.fn_nea_data(ddat_oper) <= dbo.fn_nea_data(@data)
	if(@count>0) -- були зміни клієнта
	begin
		--вичислю дату, найближчу до дати звіту
-- 		select top 1 @client = nId_ClientMisce from l_oza_ruh where cInv_Nomer=@cInv_Nomer and nId_ClientMisce!=0 and  
--          			dbo.fn_nea_data(ddat_oper)>dbo.FN_MAX_DATA_IN_ARHIV(cInv_Nomer) and dbo.fn_nea_data(ddat_oper)<=dbo.fn_nea_data(@data)
-- 		order by ddat_oper desc,nId_operruh desc
		if(@data<@min_data)
		begin
			select top 1 @client = cKod_Goz_Buh from l_oza_ruh where cInv_Nomer=@cInv_Nomer and cKod_Goz_Buh is not null and 
				dbo.fn_nea_data(ddat_oper)=@min_data
			order by ddat_oper desc,nid_operruh desc
		end		
	if(@data>=@min_data and @data<@max_data)
		Begin
			select top 1 @client = cKod_Goz_Buh from l_oza_ruh where cInv_Nomer=@cInv_Nomer and cKod_Goz_Buh is not null and
				dbo.fn_nea_data(ddat_oper) > @data and dbo.fn_nea_data(ddat_oper) <=@max_data
			order by ddat_oper desc,nid_operruh desc
		End		
		if(@data>=@max_data)
		Begin
			select @client = cKod_Goz_Buh from l_oza_kart where cInv_Nomer=@cInv_Nomer 

		End

	end
	else
	begin
		-- дата звіту більша за дату архіву і змін клієнта не було - вертаємо клієнта з останнього архіву
		select @client=cKod_Goz_Buh from l_oza_kart_arhiv where cInv_Nomer=@cInv_Nomer and ddat_period= dbo.FN_MAX_DATA_IN_ARHIV(@cInv_Nomer)
	end

end

if(@is=0 and @count=0)-- номера в архіві немає
Begin
	if(@data<@min_data)
	begin
		select top 1 @client = cKod_Goz_Buh from l_oza_ruh where cInv_Nomer=@cInv_Nomer and cKod_Goz_Buh is not null and 
			dbo.fn_nea_data(ddat_oper) =@min_data
		order by ddat_oper desc,nid_operruh desc
	end
	if(@data>=@min_data and @data<@max_data)
	Begin
		select top 1 @client = cKod_Goz_Buh from l_oza_ruh where cInv_Nomer=@cInv_Nomer and cKod_Goz_Buh is not null and
			dbo.fn_nea_data(ddat_oper) >@data and dbo.fn_nea_data(ddat_oper) <=@max_data
		order by ddat_oper desc,nid_operruh desc
	End
	if(@data>=@max_data)
	Begin
		select @client = cKod_Goz_Buh from l_oza_kart where cInv_Nomer=@cInv_Nomer

	End

End
END
return @client
end
go


Можете помочь?



Скажите, а зачем Вам так много функций фактически в одном SELECT запросе?
Vitaliy_Honcharuk
Дата: 12.02.2013 16:19:42
CREATE FUNCTION dbo.FN_NEA_DATA (@DATE datetime)
RETURNS datetime
AS
BEGIN
RETURN(DATEADD(day, DAY(@DATE) * - 1 + 1, @DATE))
END


Просто дату делаю правильной - какую нада
Glory
Дата: 12.02.2013 16:28:13
Vitaliy_Honcharuk
Просто дату делаю правильной - какую нада

Ну да.
Зачем приводить один входной параметр-дату к нужному значению ?
Гораздо лучше дату в каждой записи конвертить при выполнении запросов.

Наверное написали кучу базовых функций. Типо модульность, повторное использование кода и тп - это круто. Потом на базовых функциях написали еще функций. И тд.
А о том, как сервер будет на основе всего этого винегрета сможет построить нормальный план выполнения, никто не подумал.
_ч_
Дата: 12.02.2013 17:01:36
Vitaliy_Honcharuk,

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

dbo.fn_nea_data(ddat_oper) >@data and dbo.fn_nea_data(ddat_oper) <=@max_data


По-моему можно уже выделить какую-нибудь переменную, в которой бы хранился результат dbo.fn_nea_data(ddat_oper)

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

Мистер Хенки
переделать на inline table функции
aleks2
Дата: 12.02.2013 17:26:42
Glory
Гораздо лучше дату в каждой записи конвертить при выполнении запросов.


Гораздо лучше - ничо не конвертить.
Пример
CREATE FUNCTION dbo.FN_NEA_OZA_PEREOC (@data datetime, @nomer varchar(13))
RETURNS decimal(14,2)
AS
begin

set @data = DATEADD( month, 1, DATEADD(day, 1 - DAY(@data), @data) )

return (SELECT SUM(L_OZA_RUH.nPoc_Vart_Buh)
FROM         L_OZA_RUH LEFT OUTER JOIN
                      GD_TYPE_OPER ON L_OZA_RUH.nId_TypeOper = GD_TYPE_OPER.nId_TypeOper
WHERE     (L_OZA_RUH.cInv_Nomer = @nomer) AND (GD_TYPE_OPER.cPriznak like 'ц%') 

--and (dbo.fn_nea_data(dDat_Oper)<=dbo.fn_nea_data(@data)
--and DATEADD(day, 1 - DAY(dDat_Oper), dDat_Oper)<=DATEADD(day, 1 - DAY(@data), @data)
--and dDat_Oper < DATEADD( month, 1, DATEADD(day, 1 - DAY(@data), @data) )

and dDat_Oper < @data

-- и сервер радостно смогет задействовать индексы.
)

)
end

--CREATE FUNCTION dbo.FN_NEA_DATA (@DATE datetime)
--RETURNS datetime
--AS
--BEGIN
--RETURN(DATEADD(day, DAY(@DATE) * - 1 + 1, @DATE))
-- вроде первое число месяца?
--END
Glory
Дата: 12.02.2013 17:28:57
aleks2
Гораздо лучше - ничо не конвертить.

Это был ... сарказмю
Vitaliy_Honcharuk
Дата: 12.02.2013 18:05:25
я уже нашол причину - вся причина в том что у меня поле по которому я джойнаю char(13) - как можна ето переделать? или как чар оптимизировать при джойнах

Ето поле есть ключом. - cInv_Nomer