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

Откуда: большая деревня
Сообщений: 265
Не знаю, захочет ли кто разбираться в куче кода, но попробую.

Была база на сервере А, в ней есть представление и табличная функция, которая требуется для построения отчетов. Пришлось перенести эту базу на другой сервер. Как результат, нельзя вызвать табличную. Ладно, переписываю функцию на представление. В результате время выполнения выборки из представления падает в десятки раз.

"Хорошая" табличная функция:
+
CREATE FUNCTION [dbo].[fnAgentActivityHour]
(	
	-- Add the parameters for the function here
	@DateStart datetime, 
	@DateEnd datetime
)
RETURNS TABLE 
--with schemabinding
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
with cte as (
	select StartTime
			,EndTime
			,StartDate
			,EndDate

			,operator
			,campaign

			,StateGroup
			,StateTotal

	FROM [dbo].[AgentActivity] A

	where EndTime >= @DateStart and StartTime < @DateEnd + 1
		and EndTime < @DateEnd + 2
)

select	C.operator
		,H.Interval
		,sum(case when StateTotal = 'Total' then 
				datediff(ss
					,case when StartTime > Interval and StartTime < dateadd(hour, 1, Interval) then StartTime
						else Interval
					end
					,case when EndTime < dateadd(hour, 1, Interval) then EndTime
						else dateadd(hour, 1, Interval)
					end
				) end) as Total

		---... тут много аналлогичных case
		

from cte C
outer apply (
	select Interval
	from [dbo].[CalendarHours]
	where Interval >= StartTime2
		and Interval <= EndTime2
) H

where H.Interval >= @DateStart and H.Interval < @DateEnd + 1

group by operator, Interval

)


"Плохое" переписанное представление

+
ALTER VIEW [dbo].[vAgentActivityHour] AS

select	H.Interval
		,C.operator
		,sum(case when StateTotal = 'Total' then 
				datediff(ss
					,case when StartTime > Interval and StartTime < dateadd(hour, 1, Interval) then StartTime
						else Interval
					end
					,case when EndTime < dateadd(hour, 1, Interval) then EndTime
						else dateadd(hour, 1, Interval)
					end
				) end) as Total



from [dbo].[CalendarHours] H
outer apply (
	select StartTime
			,EndTime
			,StartDate
			,EndDate
			,operator
			,campaign

			,StateGroup
			,StateTotal

			,ContactID

	FROM [dbo].[AgentActivity] A 

	where Interval >= dateadd(hour,datediff(hour, 0, StartTime),0)
		and Interval <= dateadd(hour,datediff(hour, 0, EndTime),0)

) C

--where H.Interval >= @Date_Start and H.Interval < @Date_End + 1
group by operator,
		Interval


select *
from [dbo].[vAgentActivityHour]
where Interval >= '20170701' and Interval < '20170702'




Представление AgentActivity, использующееся в запросах:
+
CREATE view [dbo].[AgentActivity] WITH SCHEMABINDING
AS

SELECT D.StartTime
		,D.EndTime

		,cast(D.StartTime as date) as StartDate
		,cast(D.EndTime as date) as EndDate
		
		,LA.operator
		,LC.campaign
		,LT.Call_Type
		,D.ContactID
		,S.State
		,S.DescCode_ForAgentView as OperatorState
		,case when S.State = 20 then 'Paused'
			when S.State = 30 then 'AdmWk'
			when S.State = 40 then 'Waiting'
			when S.State = 50 then 'WrapUp'
			when S.State in (70, 90) then 'Ringing'
			when S.State in (80, 100) then 'Talking'
		end as StateGroup

		,case when S.State >= 20 then 'Total' end as StateTotal

from [dbo].[TIMESTATS] D
inner join [dbo].[TIMESTATS_STATUSES] S
	on S.[State] = D.[State]
inner join (
	select	id
			,Names2 as operator
	from dbo.TIMESTATS_LOOKUP 
	where [Type] = 'A'
) LA
	on LA.id = D.agent
left join (
	select id
			,Names2 as campaign
	from dbo.TIMESTATS_LOOKUP 
	where [Type] = 'C'
) LC
	on LC.id = D.Campaign
left join (
	select id
			,Names2 as Call_Type
	from dbo.TIMESTATS_LOOKUP 
	where [Type] = 'T'
) LT
	on LT.id = D.ContactType


Планы во вложении. В случае view оценочный план, т.к. не дождался завершения выполнения.

К сообщению приложен файл (Plans.zip - 41Kb) cкачать
4 июл 17, 15:57    [20612743]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать план выполнения представления  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 265
Немного лишнего удалил, вот исправленная версия "хорошей" функции
+
CREATE FUNCTION [dbo].[fnAgentActivityHour]
(	
	-- Add the parameters for the function here
	@DateStart datetime, 
	@DateEnd datetime
)
RETURNS TABLE 
--with schemabinding
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
with cte as (
	select StartTime
			,EndTime
			,StartDate
			,EndDate

			dateadd(hour, datediff(hour, 0, StartTime), 0) as StartTime2
			,dateadd(hour, datediff(hour, 0, EndTime), 0) as EndTime2

			,operator
			,campaign

			,StateGroup
			,StateTotal

	FROM [dbo].[AgentActivity] A

	where EndTime >= @DateStart and StartTime < @DateEnd + 1
		and EndTime < @DateEnd + 2
)

select	C.operator
		,H.Interval
		,sum(case when StateTotal = 'Total' then 
				datediff(ss
					,case when StartTime > Interval and StartTime < dateadd(hour, 1, Interval) then StartTime
						else Interval
					end
					,case when EndTime < dateadd(hour, 1, Interval) then EndTime
						else dateadd(hour, 1, Interval)
					end
				) end) as Total

		---... тут много аналлогичных case

		

from cte C
outer apply (
	select Interval
	from [dbo].[CalendarHours]
	where Interval >= StartTime2
		and Interval <= EndTime2
) H

where H.Interval >= @DateStart and H.Interval < @DateEnd + 1

group by operator, Interval

)
4 июл 17, 16:03    [20612780]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать план выполнения представления  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
А так не лучше выглядит?
План-то, может быть, тот же самый. Хотя...
from [dbo].[TIMESTATS] D
join [dbo].[TIMESTATS_STATUSES] S on D.[State] = S.[State]
join dbo.TIMESTATS_LOOKUP LA on D.agent = LA.id and LA.[Type] = 'A'
left join dbo.TIMESTATS_LOOKUP LC on D.Campaign = LC.id and LC.[Type] = 'C'
left join dbo.TIMESTATS_LOOKUP LT on D.ContactType = LT.id and LT.[Type] = 'T'
4 июл 17, 16:11    [20612840]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать план выполнения представления  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
А почему везде OUTER APPLY, хотя возможен и LEFT JOIN?
В последней "исправленной версии" непонятно, зачем OUTER APPLY, а не CROSS APPLY?
4 июл 17, 16:21    [20612888]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать план выполнения представления  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 265
Через left join тоже долго, 9 минут. В этот раз дождался, реальный план во вложении.

Может быть как-то иначе запрос написать? Весь день сижу, пробую разные варианты, всё в пустую.

К сообщению приложен файл (view_bad2.zip - 22Kb) cкачать
4 июл 17, 16:44    [20612998]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать план выполнения представления  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Как-то так...
WITH
cte AS (
  SELECT
    hh.[interval],
    ss.[starttime],
    ss.[endtime],
    aa.[operator],
    aa.[campaign],
    aa.[stategroup],
    aa.[statetotal]
  FROM 
    [dbo].[CalendarHours] hh
    LEFT JOIN [dbo].[AgentActivity] aa ON (
             aa.[starttime]   < @dateend
      AND aa.[endtime]    >= @datestart
      AND aa.[starttime] <= hh.[interval]
      AND aa.[endtime]    > hh.[interval] )
    CROSS APPLY (
      SELECT
        [starttime] = CASE 
          WHEN aa.[starttime] < hh.[interval] THEN hh.[interval]
          ELSE aa.[starttime]
        END,
        [endtime] = CASE 
          WHEN aa.[endtime] > DATEADD( HOUR, 1, hh.[interval] ) THEN DATEADD( HOUR, 1, hh.[interval] )
          ELSE aa.[endtime]
        END
    ) ss
  WHERE 
           hh.[interval] >= @datestart
    AND hh.[interval] < @dateend
)
SELECT
  [operator],
  [interval]
  [time] = SUM( CASE WHEN [statetotal] = 'TOTAL' THEN DATEDIFF( SECOND, [starttime], [endtime] ) END )
FROM
  cte
GROUP BY
  [operator],
  [interval]
4 июл 17, 16:58    [20613065]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать план выполнения представления  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 265
Руслан Дамирович, откуда @datestart в представлении?
4 июл 17, 17:06    [20613096]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать план выполнения представления  [new]
aleks2
Guest
virtuOS
Не знаю, захочет ли кто разбираться в куче кода, но попробую.

Представление AgentActivity, использующееся в запросах:
CREATE view [dbo].[AgentActivity] WITH SCHEMABINDING
AS

SELECT D.StartTime
		,D.EndTime

		,cast(D.StartTime as date) as StartDate
		,cast(D.EndTime as date) as EndDate
		

from [dbo].[TIMESTATS] D



Так насрать в душу сервера и еще жаловаться "медленно".
4 июл 17, 17:52    [20613242]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать план выполнения представления  [new]
aleks2
Guest
Тредстартер не просто чудак на известную букву, он - ленивый и глупый чудак.

1. Нехрен пихать под группировку то, чего там абсолютно не нужно. Зачем эти join-ы, если все и без них можно сделать? А имена присобачить после группировки?
CREATE view [dbo].[AgentActivity] WITH SCHEMABINDING
AS

SELECT D.StartTime
		,D.EndTime

		,cast(D.StartTime as date) as StartDate
		,cast(D.EndTime as date) as EndDate
		
		,LA.operator
		,LC.campaign
		,LT.Call_Type
		,D.ContactID
		,S.State
		,S.DescCode_ForAgentView as OperatorState
		,case when S.State = 20 then 'Paused'
			when S.State = 30 then 'AdmWk'
			when S.State = 40 then 'Waiting'
			when S.State = 50 then 'WrapUp'
			when S.State in (70, 90) then 'Ringing'
			when S.State in (80, 100) then 'Talking'
		end as StateGroup

		,case when S.State >= 20 then 'Total' end as StateTotal

from [dbo].[TIMESTATS] D
inner join [dbo].[TIMESTATS_STATUSES] S
	on S.[State] = D.[State]
inner join (
	select	id
			,Names2 as operator
	from dbo.TIMESTATS_LOOKUP 
	where [Type] = 'A'
) LA
	on LA.id = D.agent
left join (
	select id
			,Names2 as campaign
	from dbo.TIMESTATS_LOOKUP 
	where [Type] = 'C'
) LC
	on LC.id = D.Campaign
left join (
	select id
			,Names2 as Call_Type
	from dbo.TIMESTATS_LOOKUP 
	where [Type] = 'T'
) LT
	on LT.id = D.ContactType
4 июл 17, 18:00    [20613278]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать план выполнения представления  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 265
Помогло дополнительное ограничение на дату:

+
ALTER VIEW [dbo].[vAgentActivityHour] AS

select	H.Interval
		,C.operator
		,sum(case when StateTotal = 'Total' then 
				datediff(ss
					,case when StartTime > Interval and StartTime < dateadd(hour, 1, Interval) then StartTime
						else Interval
					end
					,case when EndTime < dateadd(hour, 1, Interval) then EndTime
						else dateadd(hour, 1, Interval)
					end
				) end) as Total



from [dbo].[CalendarHours] H
outer apply (
	select StartTime
			,EndTime
			,StartDate
			,EndDate
			,operator
			,campaign

			,StateGroup
			,StateTotal

			,ContactID

	FROM [dbo].[AgentActivity] A 

	where Interval >= dateadd(hour,datediff(hour, 0, StartTime),0)
		and Interval <= dateadd(hour,datediff(hour, 0, EndTime),0)
		and Interval <= EndTime

) C

--where H.Interval >= @Date_Start and H.Interval < @Date_End + 1
group by operator,
		Interval


select *
from [dbo].[vAgentActivityHour]
where Interval >= '20170701' and Interval < '20170702'
5 июл 17, 10:33    [20614713]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизировать план выполнения представления  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
рукалицо
5 июл 17, 10:59    [20614814]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить