От новичка до гуру: Курсы программирования на CyberDuff

Использование условия ИЛИ + функция в соединении, по-видимому, сбивает с толку оптимизатор запросов SQL Server.

Я изо всех сил пытаюсь отладить производительность по конкретному запросу. Запрос таков:

select count(*)  
FROM dbo.user d
INNER JOIN dbo.distinct_first_name dfn ON (
        [dbo].jw(dfn.first_name, 'john') > 0.8
        AND
        (d.first_name = dfn.first_name
         OR d.nick_name = dfn.first_name
         OR d.middle_name = dfn.first_name)
        )

Запрос запускает фильтр Джаро Винклера для отдельной таблицы имен (содержащей примерно 15 тыс. строк), а затем выполняет внутреннее соединение с пользовательской таблицей для получения набора результатов. Как определено, это занимает около 1 минуты при примерно 500 тыс. строк в пользовательской таблице.

Вот что я знаю:

1) Фильтр Яро Винклера почти мгновенный (сам по себе 0,1 с)

2) Если я изменю предложение пользователя, чтобы включить только один из столбцов (т.е. удалить ИЛИ), это займет всего 0,4 с.

3) Если я изменю это на три запроса и запущу их подряд, это займет около 2 секунд.

4) Если я изменю фильтр Яро Винклера на 0,99 (чтобы был только один результат), это не будет иметь существенного значения во времени выполнения запроса.

5) Если я заменю фильтр Jaro Winkler операцией равенства (dfn.first_name = 'john'), общее время запроса сократится до 4 с.

(Все тайминги довольно медленные, производительность в реальной жизни будет лучше.)

Итак, по какой-то причине комбинация функции и OR сбивает с толку оптимизатор запросов. План выполнения не очень информативен; в нем говорится, что 90% запроса тратится на:

<RelOp NodeId="63" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1.69029" EstimateIO="0.003125" EstimateCPU="0.000158859" AvgRowSize="17" EstimatedTotalSubtreeCost="71.4311" TableCardinality="15958" Parallel="0" EstimateRebinds="448881" EstimateRewinds="0.504024" EstimatedExecutionMode="Row">
                              <OutputList>
                                <ColumnReference Database="[mydb]" Schema="[dbo]" Table="[distinct_first_name]" Alias="[dfn]" Column="first_name" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="857936" ActualEndOfScans="859454" ActualExecutions="859454" />
                              </RunTimeInformation>
                              <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

Разделение запроса на самом деле является вариантом, так как это находится в sproc, и я, вероятно, могу немного изменить схему, но я не понимаю, что увязает в этом. Есть идеи?

sql sql-server
22.04.2014

  • Это заставило бы меня поверить, что вам не хватает индексов в дополнительных столбцах. Вы пытались переписать его с другой стороны, получая значения и сохраняя их во временную таблицу или табличную переменную и соединяя этот набор результатов с другой таблицей. 22.04.2014
  • @VladimirOselsky есть индексы в дополнительных столбцах. Присоединение к любому из этих столбцов по отдельности является эффективным. 22.04.2014
  • Вы пробовали запускать функцию отдельно от JOIN? 22.04.2014
  • @Goat CO - да, функция, запускаемая сама по себе, занимает 0,1 с по тем же критериям. Я мог бы запустить его заранее во временную таблицу, а затем присоединиться к временной таблице, я полагаю, но это не совсем то, что здесь ломается. 22.04.2014
  • Если функция не вызывается 3 раза в строке из-за 3 полей... похоже, что этого не должно происходить, но что-то явно не так. Это соответствовало бы отсутствию разницы в производительности между 0,8 и 0,99. Дает ли передача функции CTE такие же результаты? 22.04.2014
  • @GoatCO да, это мое предположение о том, что он делает. Но да, не похоже, что это должно происходить. Сначала попытался создать временную таблицу, а затем внутреннее соединение, и это выполняется примерно за 8 секунд. Сочетание этого подхода с тремя отдельными запросами позволяет выполнить все за 0,5 с. Итак, я предполагаю, что функция на самом деле выполняется для каждой строки в пользовательской таблице по какой-то странной причине (хотя это не указано в плане выполнения), и OR, похоже, неправильно анализируются. 22.04.2014
  • Было бы неплохо, если бы вы могли загрузить весь план выполнения XML (onedrive, dropbox...). Я не понимаю, откуда EstimateRebinds="448881", но это очень подозрительно. Кстати, вы ищете способы улучшить время выполнения запроса (затем превратите OR в UNION ALL, это поможет) или объяснение того, почему оптимизатор, по-видимому, плохо с этим справился? 22.04.2014
  • @dean Спасибо - полный план запроса здесь: pastebin.com/r9zyj0WA Я действительно ищу оба - понять и улучшить его. Почему оптимизатору не удается превратить структуру or в union all? 22.04.2014

Ответы:


1

Прежде всего, и first_name_alphaonly, и nick_name_alphaonly на самом деле являются несохраняемыми вычисляемыми столбцами, поэтому все оценочные значения кардинальности отключены, а затем умножаются.

Затем выполняется 857 936 отдельных запросов кластеризованного индекса по таблице distinct_first_name, и только после этого применяется фильтр, включающий функцию jw.

Создание индексов для вычисляемых столбцов может помочь. Фильтрация по distinct_first_name перед присоединением (в таблицу #temp), вероятно, также поможет. А затем совет по превращению OR в UNION ALL.

Оптимизатор, на самом деле, никогда не будет переставлять OR в UNION. Поверьте, это называется перестраховаться.

22.04.2014
  • На самом деле first_name_alphaonly и nick_name_alphaonly сохраняются в соответствии с определениями схемы. Где в плане запроса вы это видите? 22.04.2014
  • Я не понимаю, что индекс ищет по Different_first_name, хотя... его нужно нажать только один раз, чтобы вычислить первую сторону И, не так ли? В других запросах он работает правильно, так что что-то в этой структуре сбивает его с толку. 22.04.2014
  • Это внутренняя сторона соединения. План сначала работает с таблицей user, а затем присоединяется к таблице distinct_first_name. 22.04.2014
  • переключение сторон соединения не улучшает производительность, а наоборот, ухудшает ее. Если вы думаете об этом, это одна и та же операция, независимо от того, на какой стороне соединения находится функция - применить набор имен фильтров к большой таблице U. Так что я до сих пор не понимаю, почему он думает, что ему нужно сканировать different_first_name более одного раза. . (PS вы, возможно, пропустили мой первый комментарий... почему вы решили, что вычисляемые столбцы не сохраняются? Они существуют. На них уже есть индексы.) 22.04.2014
  • Проверить узел 6 в плане; это сканирование индекса по некоторому индексу dta с выводом столбцов first_name, middle_name и nick_name. Немного выше вы можете увидеть скалярный оператор cm_Fn_AlphaOnly_AtoZ, примененный к двум, в результате чего first_name_alphaonly и nick_name_alphaonly. 22.04.2014
  • это действительно странно. Почему он решил избегать сохраненной версии вычисляемого столбца? (Если я уменьшу это до одного из столбцов, cm_Fn_AlphaOnly_AtoZ больше не будет отображаться в плане запроса.) 23.04.2014
  • Я нашел это: заголовок stackoverflow.com/questions/5998217/ 23.04.2014

    • 2

    Несколько вещей, которые вы, возможно, захотите попробовать:

    • Что такое кластеризованный индекс таблицы dfn? Это просто таблица с именами, не более того? Если это так, удалите столбец автонумерации, если он у вас есть, и сделайте имя кластерным индексом.

    • Является ли «Джон» аргументом для вашего sproc? Я предполагаю, что это так. Вы можете сначала вычислить фильтр Яро Винклера по наименьшему из двух наборов данных имен и вставить его во временную таблицу. Затем присоединитесь к другой таблице во временной таблице. Помните, что индексы также могут быть полезны временным таблицам (если вы их добавите).

    • Возможно, вы сможете повысить производительность, создав индекс с несколькими столбцами: имя, псевдоним, отчество. Полезность отдельных индексов снижается из-за всех столбцов, на которые вы ссылаетесь в своем операторе where.

    • Я думаю, что всегда интересно запустить инструмент SQL Tuning Advisor и посмотреть, какие рекомендации он дает. Просто подключите монитор к экземпляру SQL-сервера и запишите выполнение запроса в файл рабочей нагрузки. Затем вы можете передать файл рабочей нагрузки инструменту советника, и он предложит индексы, статистику и даже изменения схемы, если вы включите эту опцию.

    • Предварительно рассчитайте все, что можете. Если я правильно помню, в фильтре Яро Винклера длина строки является важным фактором. Вы можете добавить столбец в свою таблицу dfn с длиной строки имени. Такие вещи, как функции и представления, хороши, но не обязательно оптимальны для производительности. Функция действует как черный ящик, который не может использовать какие-либо ранее существовавшие или предварительно рассчитанные данные в своих интересах.

    Самое главное: измеряйте свои результаты. Оптимизатор запросов SQL имеет собственное мнение. Следите за планом выполнения и пробуйте разные сценарии.

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

    22.04.2014
  • Спасибо. Кластеризованный индекс dfn — это столбец имени; там больше ничего нет. Я использую его как небольшой набор строк для запуска дорогостоящего фильтра. Подход с временной таблицей работает... но я не понимаю, почему. Разве оптимизатор не должен быть в состоянии выяснить, что первая часть соединения создает конечный набор имен, которые будут использоваться в объединении? Буду пробовать другие подходы. (К вашему сведению, функция Jaro Winkler работает очень быстро при работе с таблицей dfn. Она заменяет комбинацию Like + Soundex для работы с основной таблицей пользователей, которая была ужасно медленной, даже с предварительно вычисленными столбцами.) 22.04.2014
  • Я думаю, что пользовательская функция мешает оптимизатору полностью оптимизировать ваш запрос (обратите внимание на то, что я упоминаю в пункте 5, ваша функция в основном является черным ящиком для оптимизатора). Вы запускали Tuning Advisor? Вероятно, он не предложит вам идеального решения, но предложения, которые он дает, могут быть полезными. 24.04.2014
    • Новые материалы
    Основы Spring: Bean-компоненты, контейнер и внедрение зависимостей
    Как лего может помочь нашему пониманию Когда мы начинаем использовать Spring, нам бросают много терминов, и может быть трудно понять, что они все означают. Итак, мы разберем основы и будем..
    Отслеживание состояния с течением времени с дифференцированием снимков
    Время от времени что-то происходит и революционизирует часть моего рабочего процесса разработки. Что-то более забавное вместо типичного утомительного и утомительного процесса разработки. В..
    Я предполагаю, что вы имеете в виду методы обработки категориальных данных.
    Я предполагаю, что вы имеете в виду методы обработки категориальных данных. Пожалуйста, проверьте мой пост Инструментарий специалиста по данным для кодирования категориальных переменных в..
    Игра в прятки с данными
    Игра в прятки с данными Я хотел бы, чтобы вы сделали мне одолжение и ответили на следующие вопросы. Гуглить можно в любое время, здесь никто не забивается. Сколько регионов в Гане? А как..
    «Раскрытие математических рассуждений с помощью Microsoft MathPrompter и моделей больших языков»
    TL;DR: MathPrompter от Microsoft показывает, как использовать математические рассуждения с большими языковыми моделями; 4-этапный процесс для улучшения доверия и рассуждений в математических..
    Раскройте свой потенциал в области разработки мобильных приложений: Абсолютная бесплатная серия
    Глава 6: Работа в сети и выборка данных Глава 1: Введение в React Native Глава 2: Основы React Native Глава 3: Создание пользовательского интерфейса с помощью React Native Глава 4:..
    Все о кейсах: Camel, Snake, Kebab & Pascal
    В программировании вы сталкивались с ними при именовании переменной, класса или функции. Поддержание согласованности типов и стилей случаев делает ваш код более читабельным и облегчает совместную..