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

Откуда:
Сообщений: 9
Добрый день.
Существуют 2 таблицы. Таблица с контактами и таблица с проверками по контактам. Таблица контактов большая, таблица проверок еще больше, ибо на каждого контакта много проверок. Проверки могут быть как ручные, так и автоматические, а так-же проваленные и нет.
Была поставлена задача сделать вьюху по проверкам, которая на каждый контакт выводит проверку, по алгоритму. И вот с этой вьюхой проблемы.

Алгоритм там примерно следующий:
- если существует проваленная проверка, поставленная вручную, то выводить последнюю проваленную проверку, добавленную вручную
- во всех прочих случаях выводить просто последнюю проверку.

Первая версия (там второе условие было немного другое, выводить последнюю проверку только с момента появления функционала ручных проверок)

CREATE VIEW [dbo].[VwExtrimistRiskOperation]

as
	
	
with HeightenedContact (contactId) as (
	select ero.ContactId
	from ExtrimistRiskOperation ero
	where ero.RiskLevelHeightenedValue = 1 
				and ero.CreatedOn IS NOT NULL 
				and ero.ModifiedOn > cast('7/17/2018' as datetime)
	group by ero.ContactId
),
ContactWithRisk (keyColl, contactId, riskId) as (
	select 2, hc.contactId, ero1.Id
	from HeightenedContact hc
		outer apply (
			select TOP 1 tempEro.Id
			FROM ExtrimistRiskOperation tempEro
			WHERE tempEro.ContactId = hc.contactId and tempEro.RiskLevelHeightenedValue = 1
			ORDER BY tempEro.IsAddedManually desc, tempEro.ModifiedOn desc
		) ero1

	union

	select 1, c.Id, ero2.Id
	from Contact c
		join ExtrimistRiskOperation ero on c.Id = ero.ContactId
		outer apply (
			select TOP 1 tempEro.Id
			FROM ExtrimistRiskOperation tempEro 
			WHERE tempEro.ContactId = c.Id
			ORDER BY tempEro.ModifiedOn desc 
		) ero2
	where c.Id not in (select contactId from  HeightenedContact)
		and ero.RiskLevelStandardValue = 1 
					and ero.CreatedOn IS NOT NULL 
					and ero.ModifiedOn > cast('7/17/2018' as datetime)
) 

--дальше идёт селект сборки, к которому особо вопросов нет.


Данная вьюха работала хорошо, когда извне накладывалась фильтрация по контакту, но работала порядка 10-15 минут, когда данной фильтрации не было (режим работы был такой не часто, поэтому со скрипом, но работало так).

Впоследствии появилась потребность добавить последнюю проверку по всем контактам, и время формирования вьюхи без фильтрации ушло за 40 минут, что выходило за границы нормальной работы и приводило к вылетам по тайм-ауту страницы.

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

create function dbo.fn_getExtrimistRiskOperationAndContact() 
	returns @contact table (idContact uniqueidentifier, idOperation uniqueidentifier, isHigh bit)
	as
	begin

		insert into @contact
		select ero.ContactId, ero.Id, 0
		from (
			select tempEro.ContactId, MAX(tempEro.ModifiedOn) as ModifiedOn
			from ExtrimistRiskOperation tempEro
			group by tempEro.ContactId
		) eroReq
			join ExtrimistRiskOperation ero on eroReq.ContactId = ero.ContactId and eroReq.ModifiedOn = ero.ModifiedOn

		update @contact
		set isHigh = 1
		from ExtrimistRiskOperation ero 
			join @contact c on ero.ContactId = c.idContact
		where ero.RiskLevelHeightenedValue = 1
			and ero.ModifiedOn > cast('7/17/2018' as datetime) 
			and ero.CreatedOn IS NOT NULL 

		update @contact
		set idOperation = ero.Id
		from @contact c
			outer apply (
				select TOP 1 tempEro.Id
				FROM ExtrimistRiskOperation tempEro
				WHERE tempEro.ContactId = c.idContact and tempEro.RiskLevelHeightenedValue = 1
				ORDER BY tempEro.IsAddedManually desc, tempEro.ModifiedOn desc
		) ero
		where isHigh = 1

		return
	end	


С одной стороны помогло. Запрос стал формироваться полторы минуты. Но с другой стороны он формируется полторы минуты независимо от того, есть ли внешняя фильтрация или нет.
Перевести на хранимку с параметром не могу, система запрашивает данные именно со вьюхи

Есть идеи, как и что можно изменить, чтоб выйти на адекватное рабочее время работы вьюхи? Подозреваю, что outer apply по двум большим таблицам сильно грузит, но не понимаю, как можно сделать без него.
8 апр 19, 14:55    [21856012]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с производительностью запроса  [new]
aleks222
Member

Откуда:
Сообщений: 542
1. Завязать с группировками.
2. Освоить exists().

И пребудет с тобой щастье....
8 апр 19, 15:03    [21856025]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с производительностью запроса  [new]
aleks222
Member

Откуда:
Сообщений: 542
Да и ваще - хреново пишете, товарищ майор.

with c as ( select * from dbo.Contact ) 
   , e as ( select * from dbo.ExtrimistRiskOperation )
   , ContactWithRisk as (
	    select keyColl = iif( e.xRiskLevelHeightenedValue = 1, 2, 1)
             , c.*
             , e.*
	    from c
		    outer apply ( select top(1) *,  xRiskLevelHeightenedValue = iif( e.CreatedOn is not null and ero.ModifiedOn > '20180717', e.RiskLevelHeightenedValue, 0 )
                           FROM e WHERE e.ContactId = c.Id 
                            ORDER BY xRiskLevelHeightenedValue desc
                                   , e.ModifiedOn desc 
                        ) e
     )
   select * from ContactWithRisk
   -- ежели xRiskLevelHeightenedValue материализовать - будет быстрее
8 апр 19, 15:18    [21856060]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с производительностью запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 8492
Добавить в таблицу вычисляемый столбец
alter table ExtrimistRiskOperation add isHigh as cast(case when RiskLevelHeightenedValue = 1 and IsAddedManually = 1 and ModifiedOn > '20180717' then 1 else 0 end as bit);

Создать индекс
create index IX_ExtrimistRiskOperation__isHigh on ExtrimistRiskOperation (ContractId, IsHigh desc, ModifiedOn desc) include (Id) where CreatedOn is not null;

Переписать функцию
create function dbo.fn_getExtrimistRiskOperationAndContact() 
returns table
as
return (
 with s as
 (
  select
   ContractId, Id, IsHigh,
   row_number() over (partition by ContractId order by IsHigh desc, ModifiedOn desc) as rn
  from
   ExtrimistRiskOperation
  where
   CreatedOn is not null
 )
 select
  ContractId, Id, IsHigh
 from
  s
 where
  rn = 1
);
8 апр 19, 18:45    [21856395]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с производительностью запроса  [new]
Oriaraniar
Member

Откуда:
Сообщений: 9
invm,
индекс не получилось создать по вычисляемому полю, но и без него запрос стал выполняться 9 секунд, что уже приемлимо. Благодарю.
10 апр 19, 13:33    [21858264]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить