Блог

Я запускаю хранимую процедуру с несколькими объединениями с одной таблицей, содержащей более 600 000 записей. Проблема в том, что процедура выполняется очень медленно и может занять несколько минут. Мы проиндексировали соответствующие столбцы таблиц, но все равно безуспешно.

Что мы можем сделать, чтобы повысить производительность? Запрос размещен ниже.

Спасибо

 with CTE
as
(
select * from 
(
    select distinct c.ContactId, c.FirstName, c.LastName, 
    (select top 1 ce.Email from dbo.ContactEmails as ce where ce.ContactId =  c.ContactId and ce.IsPrimary = 1) as Email, 
    comp.CompanyName, j.JobName, c.MobileNumber, c.OfficeNumber, cse.DateSent, MAX(cse.DateSent) over(partition by ce.email) as maxdate 

    from dbo.ContactSentEmails as cse

    join dbo.ContactEmails as ce on cse.ContactId = ce.ContactId
    join dbo.Contacts as c on ce.ContactId = c.ContactId
    left join dbo.Jobs as j on c.JobId = j.JobId
    left join dbo.Companies as comp on c.CompanyId = comp.CompanyId

    join dbo.StaffProjects as sp on cse.StaffProjectId = sp.StaffProjectId
    join dbo.Staff as s on sp.StaffId = s.StaffId
    join dbo.Projects as p on sp.ProjectId = p.ProjectId

    where (@ContactSourceId = -1 or c.ContactSourceId = @ContactSourceId)
    and (@FirstName = '' OR c.FirstName LIKE '%'   @FirstName   '%')
    and (@LastName = '' OR c.LastName LIKE '%'   @LastName   '%')
    and (@EmailAddress = '' OR ce.Email LIKE '%'   @EmailAddress   '%')
    and (@StaffId = -1 or sp.StaffId = @StaffId)
    and (@ProjectId = -1 or sp.ProjectId = @ProjectId)
    and (@OfficeId = -1 or p.OfficeId = @OfficeId)
    and cse.DateSent between CONVERT(datetime, @startDate) and CONVERT(datetime, @endDate)

    group by c.ContactId, c.FirstName, c.LastName, Email,comp.CompanyName, j.JobName, c.MobileNumber, c.OfficeNumber, cse.DateSent
) as tbContacts
)

select ContactId, FirstName, LastName, Email, CompanyName, JobName, MobileNumber, OfficeNumber from CTE where cte.DateSent = CTE.maxdate order by CTE.Email
  

Много дефектов!

Дефект 1: ни один план не может быть выбран без просмотра предоставленных значений.

 (@ContactSourceId = -1 or c.ContactSourceId = @ContactSourceId) 
  

Если @ContactSourceId равно -1, у вас есть один план выполнения запроса. Если ContactSourceId не равен -1, у вас есть другой план выполнения запроса. Если вы хотите, чтобы был выбран правильный план выполнения, вам нужно предоставить запрос, который знает, что он должен фильтровать, не глядя на значения переменных.

Поскольку вы использовали эту конструкцию критериев 7 раз, существует 2 ^ 7 потенциальных планов, и ваши шансы получить правильный из них составляют 1/2 ^ 7 = 1/128 < 1%

Вам нужно разбить этот текст запроса на 128 различных запросов — оптимизатор запросов сделает это за вас плохо.

Дефект 2: неспособность использовать доступные критерии поиска

Один из этих 128 запросов работает следующим образом: предположим, что @FirstName был указан, а другие переменные — нет. В этом случае @FirstName является основным критерием для доступа к таблице контактов.

 c.FirstName LIKE '%'   @FirstName   '%'
  

Если бы вы написали запрос только с этим критерием в таблице контактов — не было бы индекса, который вы можете добавить в таблицу контактов, который будет использоваться. Вы обречены на сканирование таблицы. Узнайте больше о доступных критериях поиска.

Дефект 3: операции над каждой строкой дают одинаковый результат для каждой строки.

 cse.DateSent between CONVERT(datetime, @startDate) and CONVERT(datetime, @endDate)
  

Почему вы преобразуете переменную в DateTime в каждой строке? Выполните их преобразование перед выполнением запроса.

Дефект 4: 90% времени Distinct является опорой

 distinct
top 1
group by
  

Так много операторов «дайте мне только один» в запросе означает, что автор запроса просто пробовал что-то и видел, что прилипает. Упростите до фактического намерения. Я предполагаю, что distinct не нужен. Если вы добавляете distinct, когда вам это не нужно — вы все равно платите за это!

Как написал @Joe в комментарии к вашему вопросу, LIKE операторы стоят дорого, особенно потому, что вы добавляете подстановочный знак в начало каждой строки поиска. Многочисленные OR операторы также являются вероятной причиной низкой производительности запросов.

См. классическую статью Эрланда Соммарскога «Динамические условия поиска в T-SQL«, в которой содержится отличное введение в написание более эффективных «поисковых» запросов.

[Кроме того, что с SELECT TOP 1 и group by ... с почти каждым столбцом в SELECT предложении?]

Как говорят @Joe и @Kenny, LIKE операторы, вероятно, являются самой большой причиной ваших проблем. Исправление этого, вероятно, приведет к наибольшему повышению производительности. Исследуйте полнотекстовый поиск и посмотрите, подходит ли он для ваших нужд.

Тем не менее, я бы также изменил способ выбора электронной почты в качестве подвыбора в предложении select . Обычно это очень дорогой способ выполнения запроса в SQL Server. Может ли контакт иметь более одного адреса электронной почты, где IsPrimary = 1 ? Если нет, то просто присоединитесь к таблице в main FROM . Если у них может быть более одного, рассмотрите возможность создания представления (возможно, индексированного представления) для возврата верхнего адреса электронной почты по контакту. Тогда вы можете присоединиться к этому.