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

Откуда:
Сообщений: 10
Есть запрос. Работает очень медленно с большой БД. Надо любыми методами его изменить. Может кто то сталкивался с подобным?


select r.id as ReportingPolicyPeriodId, r.Name as ReportingPolicyPeriodName, td.ClaimId, td.msid, 
	td.TreatmentId, td.MemberId, td.PaidAmount, td.IncurredAmount, td.bReserved, td.iReserved, 
	td.bDiscretionaryPayment, td.iDiscretionary, td.ClaimCostThreshold, td.ProjectedCost,
	td.Over65, td.MembershipType, td.IdMainMember, td.BenefitId, td.IsNotSpecified, r.OrderBy
into #tmpDraft
from HhsReportingPolicyPeriods r
left join
(
select sy.ReportPolicyPeriodId, c.id as ClaimId, ms.id as msid, t.id as TreatmentId, m.Id as MemberId,
cast(0 as float) as PaidAmount, cast(0 as float) as IncurredAmount, b.Reserved as bReserved,
i.Reserved as iReserved, b.DiscretionaryPayment as bDiscretionaryPayment, i.Discretionary as iDiscretionary,
s.ClaimCostThreshold, (SELECT TOP 1 ProjectedCost FROM HhsClaimProjectedCost cpc WHERE cpc.ClaimId = c.id and cpc.MembershipId = ms.id ORDER BY cpc.id desc) as ProjectedCost,
CASE When DATEADD(year, -65, getdate()) < m.dob Then 0 Else 1 End as Over65, ft.Name as MembershipType, f.IdMainMember, b.id as BenefitId, 0 as IsNotSpecified
from HhsSchemeYears sy 
		left join HhsMemberships ms on ms.SchemeYearId = sy.id
		left join HhsMembers m on ms.MemberId = m.id
		left join HhsClaims c on c.MemberId = m.Id
		left join HhsClaimTypes ct on c.ClaimTypeId = ct.id
		left join HhsTreatmentPlan t on t.claimid = c.Id and t.membershipid = ms.Id
		left join HhsGuarantees g on g.TreatmentId = t.Id
		left join HhsProviderBillsPaid p on p.LofGID = g.Id
		left join HhsInvoiceDetails i on p.InvoiceDetailId = i.id
		left join HhsBenefits b on i.BenefitId = b.id 
		left join HhsInvoiceStatuses ist on p.status = ist.id
		left join HhsSchemes s on sy.SchemeId = s.Id
		left join HhsFamilies f on f.IdMainMember = m.Id
		left join HhsFamilyTypes ft on f.FamilyTypeId = ft.id
where sy.ReportPolicyPeriodId IN (SELECT number FROM parseIDList(@RepPolicyList)) and
		p.Status = 1 and 
		ct.Name != 'Declined' and ist.Name != 'Declined' and
		(@ExtGroupName = '' or m.ExtGroupName like '%' + @ExtGroupName + '%') and
		(@ExtGroupNo = '' or m.ExtGroupNo like '%' + @ExtGroupNo + '%')
) 
td on r.id = td.ReportPolicyPeriodId
where r.Id IN (SELECT number FROM parseIDList(@RepPolicyList))


Сообщение было отредактировано: 12 авг 12, 14:09
12 авг 12, 12:57    [13000025]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
TerraCotta,

Ну меня настораживают в плане производительности следующие вещи.
Что за функция parseIDList(@RepPolicyList)? если она не inline, то нужно попробовать переделать ее в таковую.
Вот такие штуки исключают возможности поиска по индексу "(@ExtGroupName = '' or m.ExtGroupName like '%' + @ExtGroupName + '%') and" Так что если таблица HhsMembers m большая, можно подумать как это оптимизировать (например, исключив "универсальность разнеся на разные ветки кода когда @ExtGroupName = '' и когда мы действительно что-то ищем, + возможно сделать такую конструкцию, для облегчения поиска вида like '%smth%'.
Кстати, непонятно зачем вам left join HhsClaimTypes ct on c.ClaimTypeId = ct.id, когда потом вы всерно сравниваете это в where без учета null-ов?
Также у вас джойнится действительно много таблиц, может быть, имеет смысл внутренный подзапрос, выделить его какую-то основную часть куда будут входить все фильтры из where и предварительно записать результат во временную таблицу, т.е. разбить запрос на части с меньшим числом таблиц.
Ну и важно чтобы была хорошая детальная статистика по всем таблицам, особенно если их много и оптимизатору нужно правильно выбрать порядок соединения. Так что в любом случае, хорошобы сделать update statistics mytale with fullscan. Попробуйте начать с этого и смотрите что меняется. Обращайте внимание в действительном плане запроса на расхождение оценочного и реального числа строк. Также обращате внимание на толщину стрелок - это свидетельствует что где-то идет большой поток данных, выбирается много строк, смотрите, нельзя ли с этим что-то сделать.
В целом пока наверное все что я могу сказать глядя просто на запрос, без подробностей.
12 авг 12, 14:14    [13000146]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
TerraCotta
Member

Откуда:
Сообщений: 10
SomewhereSomehow,
просто поступило задание ускорить процесс. На реальной базе занимает много минут. И надо найти хоть отправную точку. Далее вытянутые данные используются для построения отчетности.
12 авг 12, 19:26    [13000853]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
TerraCotta,

Разве я дал недостаточно отправных точек?
Функция, поиск по like, много таблиц, неактуальна/неточная статистика + анализ плана...
Если вы хотите волшебную команду: select veryfast * from ... то такой нет. Придется напрячься.
12 авг 12, 19:44    [13000893]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
TerraCotta
Member

Откуда:
Сообщений: 10
SomewhereSomehow,
Я прекрасно это понимаю. Благодарю за отклик) Просто вряд ли получится избавиться от join или сократить сильно количество таблиц. А что Вы имеете ввиду под статистикой?
12 авг 12, 19:47    [13000898]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
aleks2
Guest
Самое элементарное:
1. Применить фильтры запроса ОТДЕЛЬНО к ОДНОЙ соответствующей таблице запроса. OR заменить на UNION.
2. Засунуть результаты во временные таблицы С ИНДЕКСАМИ.
3. Выполнить JOIN на этих таблицах.

По мелочи:
Запихните результат (SELECT number FROM parseIDList(@RepPolicyList)) в табличную переменную с уникальным кластерным индексом. И замените in на join.
12 авг 12, 19:59    [13000920]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
TerraCotta,

Возможно вы неправильно меня поняли. Я не предлагал избавлять от join в ущерб какой-либо логике. Я предлагал изучить возможность разбить запрос на несколько, если это поможет (например, сначала выясняем все строки подпадающие под искомое условие, записываем их во временную таблицу, а затем джойним уже ее одну с оставшимися таблицами).
Про статистику. Перед тем как выбрать тот или иной способ как реально выполнять запрос оптимизатор оценивает число строк, которое должно получиться в итоге, и исходя из этого выбирает. Эта оценка возможна благодаря статистической информации о данных. Если эта информация неверна, оптимизатор может принять неверное решение. Подробнее тут.
Еще посмотрите на функцию все же. Желательно, чтобы она была inline тогда оптимизатор сможет дать более точную оценку. Ну и тут статейку почитайте про like. Также посмотрите планы. Посмотрите где выбирается много данных. Вот в помощь Анализ запроса
12 авг 12, 20:01    [13000927]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
Про функцию, LIKE, возможную замену OR на UNION и т.п. - всё бесспорно.
Это прямо-таки банальность, с этого надо начать.

Ну, можно ещё вручную попробовать написать JOINы в правильном порядке,
ибо в случае их большого количества оптимизатор не в состоянии
выбрать оптимальный вариант.
Сначала таблицы с наименьшим количеством строк, потом - побольше, в конце - самые большие.
Порядок JOINов фиксируется хинтом запроса FORCE ORDER.

И вот ещё что. Вам, кажется, уже говорили, что не имеет смысла использовать OUTER JOIN,
если в WHERE на внешнюю таблицу накладывается ограничение, отсекающее значения NULL.
Получается эквивалент INNER JOIN, но с лишними телодвижениями.
12 авг 12, 20:20    [13000984]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
TerraCotta
Member

Откуда:
Сообщений: 10
SomewhereSomehow,

эта функция принимает идентификаторы с разделителем в пробел и возвращает соотв. таблицу. А вот как разбить запрос я не понимаю... можно простенький пример на этом или на очень простом ином запросе?
12 авг 12, 21:12    [13001168]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
TerraCotta
Member

Откуда:
Сообщений: 10
aleks2,

самое элементарно и не понятно. Можно маленький пример написать на простеньком запросе?
12 авг 12, 21:12    [13001173]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
TerraCotta
Member

Откуда:
Сообщений: 10
Также есть вопрос:

where r.Id IN (SELECT number FROM parseIDList(@RepPolicyList)) усекает таблицу после всех join. Может это стоит сделать до всех join? Будет какая то разница?
12 авг 12, 21:14    [13001182]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
TerraCotta
Member

Откуда:
Сообщений: 10
iap,

И вот ещё что. Вам, кажется, уже говорили, что не имеет смысла использовать OUTER JOIN,
если в WHERE на внешнюю таблицу накладывается ограничение, отсекающее значения NULL.
Получается эквивалент INNER JOIN, но с лишними телодвижениями. - не понял.

Про функцию, LIKE, возможную замену OR на UNION и т.п. - всё бесспорно.
Это прямо-таки банальность, с этого надо начать. - можно простенький пример (тоже не очень ясно как сделать)
12 авг 12, 21:16    [13001188]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
TerraCotta
SomewhereSomehow,

эта функция принимает идентификаторы с разделителем в пробел и возвращает соотв. таблицу. А вот как разбить запрос я не понимаю... можно простенький пример на этом или на очень простом ином запросе?

Не охота анализировать и расписывать ваш запрос подробно, по крайней мере сейчас.
Идея простая. Нужно выделить "костяк" запроса, я бы начал с внутреннего подзапроса. Выделите из этого тот запрос который реально обеспечивать получение нужных строк, удалив соединения, которые нужны только для получения результатов в select.
Как правильно заметил aleks2, можно даже попробовать выделить такой запрос, просто предварительно записав результаты парсинга идентификаторов функции во временную таблицу или табличную переменную, практически не меняя основного запроса. Не забудьте сделать на этой таблице идшек primary key. Вот тут объясняют стратегию хорошо, не знаю как у вас с английским, но понятно даже в картинках Rewriting SQL queries for Performance in 9 minutes.
Опять же уберите лишние left join, заменив на inner join, оптимизатор догадается это сделать, но это лишние преобразвания, которые не только ведут к лишнему времени, но и увеличивают счетчик действий оптимизатора, после которых он перестает искать хороший план.
Ну а примеры, чего там, просто:
select *
from t1
join t2 on t1.id = t2.id
join t3 on t2.id = t3.id
join t4 on t3.id = t4.id
where
t2.name like 'abc' and
t3.state = 'a' and
t4.model = 13

--vs
create table #t(id int primary key);
insert #t(id)
select distinct t2.id
t2
join t3 on t2.id = t3.id
join t4 on t3.id = t4.id
where
t2.name like 'abc' and
t3.state = 'a' and
t4.model = 13

select 
* 
from
t1
join #t t on t1.id = t.id
join

Ну это просто в качестве иллюстрации. Что-то типа такого. При этом надо постараться, чтобы в #t было как можно меньше строк, а t1 была большой таблицей, которую t сможет сильно ограничить.
Экспериментируйте в этих направлениях. Ну и про like, статистику, и порядок join-ов не забывайте!
12 авг 12, 21:35    [13001258]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
TerraCotta
iap,

И вот ещё что. Вам, кажется, уже говорили, что не имеет смысла использовать OUTER JOIN,
если в WHERE на внешнюю таблицу накладывается ограничение, отсекающее значения NULL.
Получается эквивалент INNER JOIN, но с лишними телодвижениями. - не понял.

Про функцию, LIKE, возможную замену OR на UNION и т.п. - всё бесспорно.
Это прямо-таки банальность, с этого надо начать. - можно простенький пример (тоже не очень ясно как сделать)
Как логически выполняется
LeftTable LEFT JOIN RightTable ON ...
?
Сначала выполняется INNER JOIN, потом к результату добавляются строки LeftTable, которые не удовлетворяют условию ON,
а поля в этих доплнительных строках, соответствующие RightTable, заполняются значениями NULL.
После JOINов выполняется WHERE. А там Вы пишете
p.Status = 1
.
Стало быть, все записи, добавленные после INNER JOIN, отбрасываются. Так зачем же там написано LEFT JOIN?
Или почему в условии ON не учитываются p.Status IS NULL?

P.S. В чём трудность использовать тег QUOTE?
12 авг 12, 21:37    [13001266]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
TerraCotta
Member

Откуда:
Сообщений: 10
iap,

p и m - заменить на inner join получается только?
12 авг 12, 21:49    [13001313]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
TerraCotta
iap,

p и m - заменить на inner join получается только?

Еще раз. То что заметили я и iap, это плохо. Но вряд ли это станет решением вашей проблемы, т.к. оптимизатор в курсе такой ситуации и умеет сам заменять внешнее соединение на внутреннее. Конечно, может произойти чудо. Вдруг оптимизатор внезапно найдет правильный план, попробуйте, но! Не стоит строить иллюзий, не ищите решений в духе заменил одно слово на другое и все стало летать (хотя я этого не исключаю, но даю плохие шансы). У вас в запросе, много чего, на что стоит обратить внимание. Это перечисли уже все высказывающиеся по многу раз. Настал ваш звездный час экспериментов. Непонятно, какой помощи вы еще ждете. Слова сказаны, ссылки все приведены, дерзайте. После публикуйте действия, модифицированный запрос, результаты - если ничего не поможет - будем думать дальше. (Не забудьте про статистику и функцию).
12 авг 12, 22:03    [13001349]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
TerraCotta
Member

Откуда:
Сообщений: 10
SomewhereSomehow,

завтра с утреца приступлю... продвижения буду публиковать здесь. Спасибо за помощь ребята!
12 авг 12, 22:05    [13001357]     Ответить | Цитировать Сообщить модератору
 Re: Помогите ускорить SQL запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
Ну. вот, например, про функцию:

Функция, которая делит строку на слова

Правда, не факт, что сильно поможет.
12 авг 12, 22:08    [13001366]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить