Неожиданное увеличение скорости SQL

Использование вызовов функций в предложениях where оказывает огромное влияние, поскольку SQL Server должен вычислять результат для каждой строки. С другой стороны, like - это встроенная языковая функция, которая сильно оптимизирована.

Если вы используете функцию для столбца с индексом, тогда db больше не использует индекс (по крайней мере, с Oracle)
Итак, я предполагаю, что ваше поле примера 'some_string_field' имеет индекс, который не используется для запроса с 'LEFT'

Похоже, что выражение LEFT (some_string_field, 4) вычисляется для каждой строки полного сканирования таблицы, а выражение «like» будет использовать индекс.

Оптимизация «лайка» для использования индекса, если это шаблон с передней привязкой, является гораздо более простой оптимизацией, чем анализ произвольных выражений с использованием строковых функций.

Почему вы говорите, что они идентичны? Они могут решить одну и ту же проблему, но их подход отличается. По крайней мере, так кажется ...

Запрос с использованием LEFT оптимизирует тест, поскольку он уже знает длину префикса и т. д., Поэтому в программе C / C++ / ... или без индекса алгоритм, использующий LEFT для реализации определенного поведения LIKE, будет быстрейший. Но, в отличие от большинства недекларативных языков, в базе данных SQL за вас делается множество операционных оптимизаций. Например, LIKE, вероятно, реализуется путем первого поиска знака%, и если будет замечено, что% является последним символом в строке, запрос можно оптимизировать так же, как вы это делали с помощью LEFT, но напрямую с использованием индекса .

Итак, я действительно думаю, что вы, мы, правы, в конце концов, они, вероятно, идентичны в своем подходе. Единственное отличие состоит в том, что сервер db может использовать индекс в запросе с помощью LIKE, потому что в предложении WHERE нет функции, преобразующей значение столбца во что-то неизвестное.

В более общем смысле, вы никогда не должны использовать функцию в левой части предложения WHERE в запросе. Если вы это сделаете, SQL не будет использовать индекс - он должен оценивать функцию для каждой строки таблицы. Цель состоит в том, чтобы убедиться, что ваше предложение where - "Sargable"

Еще несколько примеров:

Bad: Select ... WHERE isNull(FullName,'') = 'Ed Jones'
Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate()) 

Bad: Select ... WHERE Year(OrderDate) = 2003
Fixed: Select ... WHERE OrderDate >= '2003-1-1' AND OrderDate < '2004-1-1'

Здесь произошло либо то, что СУБД не способна использовать индекс в предикате LEFT () и может использовать его в LIKE, либо она просто сделала неправильный вызов, который был бы более подходящим методом доступа.

Во-первых, для некоторых СУБД может быть верно то, что применение функции к столбцу предотвращает использование метода доступа на основе индекса, но это не универсальная истина, и нет никакой логической причины, почему это необходимо. Метод доступа на основе индекса (например, полное сканирование индекса Oracle или быстрое сканирование полного индекса) может быть полезным, но в некоторых случаях СУБД не может выполнять операцию в контексте предиката на основе функции.

Во-вторых, оптимизатор может просто ошибиться в арифметике при оценке преимуществ различных доступных методов доступа. Предполагая, что система может использовать метод доступа на основе индекса, она должна сначала оценить количество строк, которые будут соответствовать предикату, либо из статистики в таблице, либо из статистики в столбце, путем выборки данных во время анализа или использовать эвристическое правило (например, «предположим, что совпадут 5% строк»). Затем он должен оценить относительную стоимость полного сканирования таблицы или доступных методов на основе индексов. Иногда он ошибается в арифметике, иногда статистика вводит в заблуждение или неточна, а иногда эвристические правила не подходят для набора данных.

Ключевой момент - знать о ряде вопросов:

1. Какие операции может поддерживать ваша СУБД?
2. Какая операция была бы наиболее подходящей в случай, с которым вы работаете?
3. Выбор системы правильный?
4. Что можно сделать, чтобы позволить системе выполнять более эффективную операцию (например, добавить отсутствующее ограничение, не равное нулю, обновить статистику и т. д.)?

По моему опыту, это нетривиальная задача, и зачастую ее лучше доверить специалистам. Или, с другой стороны, просто опубликуйте проблему в Stackoverflow - некоторые из нас находят это увлекательным, собака нам помогает.

Как упоминалось в @BradC, вы не должны использовать функции в предложении WHERE, если у вас есть индексы и вы хотите ими воспользоваться.

Если вы читали раздел, озаглавленный «Используйте LIKE вместо LEFT () или SUBSTRING () в предложениях WHERE, когда присутствуют индексы» из этих советов по производительности SQL, есть еще примеры.

Он также намекает на вопросы, с которыми вы столкнетесь по Экзамены MCSE SQL Server 2012, если вы тоже хотите их взять. :-)