Порядок медленным запросом вниз

с использованием сервера sql 2014; ((SP1-CU3) (KB3094221) 10 октября 2015 г. x64

У меня есть следующий запрос

SELECT * FROM dbo.table1 t1

                    LEFT JOIN dbo.table2 t2 ON t2.trade_id = t1.tradeNo
                    LEFT JOIN dbo.table3 t3 ON t3.TradeReportID = t1.tradeNo                                                                                                
                    order by t1.tradeNo

существуют строки ~ 70k, 35k и 73k в t1, t2 и t3 соответственно.

Когда я опускаю order by, этот запрос выполняется за 3 секунды с 73k строк.

Как написано, на запрос потребовалось 8,5 минут, чтобы вернуть ~ 50 тыс. строк (я с тех пор его остановил)

Переключение порядка LEFT JOIN имеет значение:

SELECT * FROM dbo.table1 t1

                    LEFT JOIN dbo.table3 t3 ON t3.TradeReportID = t1.tradeNo                                                                                                
                    LEFT JOIN dbo.table2 t2 ON t2.trade_id = t1.tradeNo                     
                    order by t1.tradeNo

Теперь это выполняется через 3 секунды.

У меня нет индексов на таблицах. Добавление индексов t1.tradeNo и t2.trade_id и t3.TradeReportID не влияет. Выполнение запроса только с одним левым соединением (оба сценария) в сочетании с order by выполняется быстро.

Хорошо для меня, чтобы поменять порядок LEFT JOIN, но это далеко не объясняет, почему это происходит и по каким сценариям это может случиться снова.

Предполагаемый план exectuion: (медленный)

(информация о восклицательных знаках)

Переключение порядка левого соединения (быстрый):

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

UPDATE

Похоже, добавление результатов предложения order by в план выполнения с использованием таблицы Spool (lazy spool) против NOT, используя это в быстром запросе. Если я отключу настольную катушку через DBCC RULEOFF ('BuildSpool');, это "исправляет" скорость, но в соответствии с этим сообщением это не рекомендуется и не может делать это за каждый запрос

ОБНОВЛЕНИЕ 2

Один из возвращенных столбцов (table3.Text] имеет тип varchar(max)). Если это было изменено на nvarchar(512), то исходный (медленный) запрос теперь быстр - то есть план выполнения теперь решает не использовать таблицу Spool - также обратите внимание, что даже для типа varchar(max) значения полей равны NULL для каждой из строк. Теперь это исправление, но я не мудрее

ОБНОВЛЕНИЕ 3

Предупреждения в плане выполнения указаны

Преобразование типов в выражение (CONVERT_IMPLICIT (nvarchar (50), [t2]. [trade_id], 0)) может повлиять на "CardinalityEstimate" в выборе плана запроса,...

t1.tradeNo - nvarchar(21) - остальные два являются varchar(50) - после изменения последних двух до тех же, что и первая проблема исчезает! (оставляя varchar (max), как указано в UPDATE 2 без изменений)

Учитывая, что эта проблема уходит, когда исправлены либо UPDATE 2, либо UPDATE 3, я бы предположил, что комбинация оптимизатора запросов с использованием таблицы temp (table spool) для столбца с неограниченным размером - очень интересна, несмотря на nvarchar(max), не имеющий данных.

Ответ 1

Вероятно, лучшее решение - убедиться, что обе стороны ваших объединений имеют одинаковый тип данных. Там нет необходимости, чтобы один был varchar а другой nvarchar.

Это класс проблем, который довольно часто встречается в БД. База данных допускает неправильные данные о составе данных, с которыми она собирается иметь дело. Расходы, указанные в вашем предполагаемом плане выполнения, скорее всего, далеки от того, что вы получите в своем фактическом плане. Мы все совершаем ошибки, и было бы хорошо, если бы SQL Server учился сам, но в настоящее время это не так. Он оценит время возврата в 2 секунды, несмотря на то, что он снова и снова будет сразу доказан. Честно говоря, я не знаю ни одной СУБД, которая бы лучше обучалась машинному обучению.

Где ваш запрос быстрый

Самая сложная часть выполняется заранее путем сортировки таблицы3. Это означает, что он может выполнять эффективное объединение слиянием, а это, в свою очередь, означает, что у него нет причин лениться с буферизацией.

Где это медленно

Наличие идентификатора, который относится к одной и той же вещи, хранящейся как два разных типа и длины данных, никогда не должно быть необходимым и никогда не будет хорошей идеей для идентификатора. В этом случае nvarchar в одном месте varchar в другом. Когда это не позволяет получить оценку количества элементов, это ключевой недостаток и вот почему:

Оптимизатор надеется потребовать только несколько уникальных строк из таблицы3. Просто несколько вариантов, таких как "Мужской", "Женский", "Другой". Это было бы то, что известно как "низкая мощность". Так что представьте, что tradeNo на самом деле содержит идентификаторы для полов по какой-то странной причине. Помните, что это вы со своими человеческими навыками контекстуализации, кто знает, что это очень маловероятно. БД слепа к этому. Итак, вот что он ожидает: когда он выполняет запрос в первый раз, когда встречает идентификатор "Male", он лениво извлекает связанные данные (например, слово "Male") и помещает их в спул. Далее, поскольку он отсортирован, он ожидает намного больше мужчин и просто повторно использует то, что уже положил в катушку.

По сути, планируется получить данные из таблиц 1 и 2 несколькими большими порциями, останавливаясь один или два раза, чтобы получить новые данные из таблицы 3. На практике остановка не случайна. На самом деле, он может даже останавливаться для каждой строки, потому что здесь много разных идентификаторов. Ленивая шпулька - все равно что подниматься по лестнице, чтобы получить по одной маленькой вещи за раз. Хорошо, если вы думаете, что вам просто нужен ваш кошелек. Не очень хорошо, если вы переезжаете, в таком случае вам понадобится большая коробка (рыхлая катушка).

Вероятная причина того, что уменьшение размера поля в таблице 3 помогло, состоит в том, что это означало, что он оценил меньшую сравнительную выгоду в выполнении ленивой катушки по сравнению с полной сортировкой. С varchar он не знает, сколько там данных, сколько их потенциально может быть. Чем больше потенциальных кусков данных, которые нужно перетасовать, тем больше физической работы необходимо выполнить.

Что вы можете сделать, чтобы избежать в будущем

Сделайте так, чтобы схема вашей таблицы и индексы отражали реальную форму данных.

Если идентификатор может быть varchar в одной таблице, то вряд ли понадобятся дополнительные символы, доступные в nvarchar для другой таблицы. Избегайте необходимости преобразования идентификаторов, а также используйте целые числа вместо символов, где это возможно.
Спросите себя, нужно ли заполнять любой из этих таблиц tradeNo для всех строк. Если так, сделайте так, чтобы это не обнулялось на этом столе. Затем спросите, должен ли идентификатор быть уникальным для любой из этих таблиц, и задайте его соответствующим образом в соответствующем индексе. Уникальным является определение максимальной мощности, поэтому он больше не допустит этой ошибки.

Сдвиг в правильном направлении с порядком соединения.

Порядок ваших соединений в SQL является сигналом для базы данных о том, насколько мощным/сложным вы ожидаете от каждого объединения. (Иногда, как человек, вы знаете больше. Например, если вы обращаетесь к 50-летним астронавтам, вы знаете, что фильтрация для астронавтов была бы первым фильтром, который нужно применить, но, возможно, начинать с возраста при поиске 50-летних офисных работников.) Тяжелые вещи должны прийти первый. Он будет игнорировать вас, если он думает, что у него есть информация, которую нужно знать лучше, но в этом случае он полагается на ваши знания.

Если все остальное терпит неудачу

Возможное решение проблемы - INCLUDE все поля, которые вам понадобятся из таблицы 3 в индексе TradeReportId. Причина, по которой индексы уже не могли помочь, заключается в том, что они позволяют легко определить, как проводить повторную сортировку, но до сих пор это не было сделано физически. Это работа, которую он надеялся оптимизировать с помощью ленивой катушки, но если бы данные были включены, они были бы уже доступны, так что не было никакой работы по оптимизации.

Ответ 2

Наличие индексов в таблице является ключом к ускорению поиска данных. Начните с этого, а затем повторите свой запрос, чтобы узнать, улучшена ли скорость с помощью "ORDER BY"