Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
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] Ответить | Цитировать Сообщить модератору |
aleks222 Member Откуда: Сообщений: 1236 |
1. Завязать с группировками. 2. Освоить exists(). И пребудет с тобой щастье.... |
8 апр 19, 15:03 [21856025] Ответить | Цитировать Сообщить модератору |
aleks222 Member Откуда: Сообщений: 1236 |
Да и ваще - хреново пишете, товарищ майор.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] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9633 |
Добавить в таблицу вычисляемый столбец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] Ответить | Цитировать Сообщить модератору |
Oriaraniar Member Откуда: Сообщений: 9 |
invm, индекс не получилось создать по вычисляемому полю, но и без него запрос стал выполняться 9 секунд, что уже приемлимо. Благодарю. |
10 апр 19, 13:33 [21858264] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |