Что лучше с точки зрения производительности SQL: предложение IF ... ELSE или предложение WHERE LIKE?
У меня есть хранимая процедура с необязательным параметром @UserID VARCHAR(50). Дело в том, что с ним можно работать двумя способами:
- Задайте для него значение по умолчанию
NULL, у него есть предложениеIF...ELSE, которое выполняет два разных запросаSELECT, один с'WHERE UserID = @UserID'и без указания where. - Задайте для него значение по умолчанию
'%', а затем просто укажите в предложении where использование'WHERE UserID LIKE @UserID'. В вызывающем коде символ «%» использоваться не будет, поэтому будут найдены только точные совпадения.
Возникает вопрос: какой вариант быстрее? Какой вариант обеспечивает лучшую производительность по мере роста таблицы? Имейте в виду, что столбец UserID является внешним ключом и не индексируется.
Обновлено: Что-то, что я хочу добавить, основываясь на некоторых ответах: параметр @UserID не (обязательно) единственный передаваемый параметр по желанию. В некоторых случаях имеется до 4 или 5 необязательных параметров.
Марк, я прочитал твой ответ, и ты меня убедил. Единственное, о чем уже упоминалось в другом комментарии: как вы обрабатываете несколько необязательных параметров? То, как это делается сейчас в нашей БД, - это объединить строку WHERE, добавить ее в строку SQL и вызвать для нее EXEC (). Есть способ попроще?
Другой вариант - написать код, который пишет код. Если вам нужно 5 различных ПЛАНОВ, напишите программу, которая генерирует эти 5 SQL и операторы case, которые определяют, какой sql выполняет какую группу переменных. Но не запускайте свой метакод, возьмите результат и зарегистрируйте его в CVS. метакод предназначен для вашего использования.
Думаю, это не так ясно. Когда люди пишут действительно крутой код, который изменяется на лету, поддерживать его практически невозможно. Вместо этого запустите этот классный метакод, чтобы создать код, который в конечном итоге будет зарегистрирован. Этот код можно зарегистрировать как отдельный проект, но не запускайте его в рабочей среде.
Ответы 9
Обычно я делаю что-то вроде
WHERE ( @UserID IS NULL OR UserID = @UserID )
И почему он не индексируется? Как правило, индексирование FK является хорошим тоном, поскольку вы часто присоединяетесь к ним ...
Если вас беспокоит хранение планов запросов, просто выполните: СОЗДАТЬ ПРОЦЕДУРУ ... С РЕКОМЕНДУЕМ
До SQL Server 2008 SP1 CU5 это ужасно. Если вы используете более позднюю версию, добавление OPTION (RECOMPILE) к запросу делает это жизнеспособным способом реализации условий динамического поиска. (Согласно исследованиям Эрланда Соммарскога)
Единственный способ сказать наверняка - это реализовать и то, и другое и измерить. Для справки есть третий способ реализовать это, что я обычно использую:
WHERE (@UserID IS NULL OR UserId = @UserId)
Почему бы не использовать:
where @UserID is null or UserID=@UserID
+ по ремонтопригодности и производительности
Вы каждый раз навязываете жесткий синтаксический анализ?
Никакой оптимизатор не должен это учитывать.
Я бы демонстративно выбрал первый, потому что, хотя он менее «умный», его легче понять, что происходит, и, следовательно, его легче поддерживать.
Использование специального значения по умолчанию может позже сбить вас с толку из-за некоторых непреднамеренных побочных эффектов (документация о том, почему вы используете это значение по умолчанию, и его использование, вероятно, будет пропущено любым сопровождающим)
Что касается эффективности - если вы не смотрите на 1000 или более пользователей, вряд ли это будет достаточной проблемой, чтобы переопределить ремонтопригодность.
Я мог бы тебя поцеловать. Но не поддавайтесь дурным привычкам, потому что данных может быть мало ... если он сделает это там, где это не имеет значения, он продолжит делать это, если это будет иметь значение, и его будут терять.
Да, я буду избегать поцелуев, но на самом деле я имел в виду обратное - всегда выбирайте ясный очевидный код, если только не возникает проблем с производительностью сила, иначе вы И всегда помните, что машинные циклы намного дешевле, чем циклы кодера.
Во-первых, вы должны создать индекс для UserID, если вы используете его в качестве критерия поиска таким образом.
Во-вторых, при сравнении UserID LIKE @UserID нельзя использовать индекс, потому что оптимизатор не знает, дадите ли вы значение параметра @UserID, которое начинается с подстановочного знака. Такое значение не может использовать индекс, поэтому оптимизатор должен предположить, что он не может создать план выполнения с использованием этого индекса.
Поэтому рекомендую:
- Создать индекс на
UserID - Используйте первый вариант,
WHERE UserID = @UserID, который следует оптимизировать для использования индекса.
редактировать: Марк Брэди напоминает мне, что я забыл заняться делом NULL. Я согласен с ответом Марка, делаю IF и выполняю один из двух запросов. Даю ответ Марка +1.
А когда нет User_ID, когда он хочет их всех? Вы не ответили полностью на вопрос. Вы справились с половиной очень просто.
Где UserId Like @UserId БУДЕТ использовать индекс, если он доступен, при условии, что значение @UserID не включает подстановочный знак в НАЧАЛЕ строкового значения ... как в Where lastname Like '% Higgins' Если вы имел Где Фамилия как «Хиггинс» или Где Фамилия как «Хиггинс%» Тогда все в порядке
@Charles: насколько я понимаю, это потребует перекомпиляции плана выполнения с учетом заданного значения @UserID. В противном случае оптимизатор не сможет предположить, что он может использовать индекс.
Не больше, чем при использовании Where UserId = @UserId для нового другого значения @UserID ... Оптимизатор должен посмотреть статистику, чтобы увидеть, сколько операций ввода-вывода страницы потребуется для переноса этих страниц с диска с помощью индекса, поскольку по сравнению с выполнением FTS ... Использование: Например, без начальных подстановочных знаков,
он может перемещаться по индексу точно так же, как если бы вы использовали Where UserId = ....
Очень полезно знать. Признаюсь, я не так часто использую MS SQL, как базы данных других производителей. Спасибо!
Я бы предпочел вариант 1, но на самом деле у меня есть две хранимые процедуры. Один получит всех пользователей, а другой - конкретного пользователя. Я думаю, это понятнее, чем передавать NULL. Это сценарий, в котором вам нужны два разных оператора SQL, потому что вы запрашиваете разные вещи (все строки против одной строки).
Я мог бы это сделать, хотя: 1. Предложение WHERE может по-прежнему возвращать более одной строки, 2. Существуют дополнительные необязательные параметры и 3. Логика кода проще иметь один SP и передавать только необходимые параметры.
Раньше я использовал подход NULL-означает-все, и меня сожгли, когда ошибочный код перешел в NULL для всего. Затем база данных отключается и извлекает миллион строк.
Проблема с наличием только одной хранимой процедуры, как уже упоминалось выше, довольно хорошо объясняется тем, что SQL хранит скомпилированный план для процедуры, план для нулевого значения сильно отличается от плана со значением.
Однако создание оператора if в хранимой процедуре приведет к перекомпиляции хранимой процедуры во время выполнения. Это также может усугубить проблемы с производительностью.
Как упоминалось в другом месте, это подходит для подхода test and see, учитывая оператор if, @UserID имеет значение null и две отдельные процедуры.
К сожалению, скорость этих подходов будет сильно различаться в зависимости от объема данных и частоты вызовов, в которых параметр равен нулю, и вызовов, в которых параметр не равен. Опять же, количество параметров также повлияет на эффективность подхода, который требует переписывания процедур.
Если вы используете SQL 2005, вы можете получить некоторую выгоду от запроса вариант подсказки плана.
Исправление: Sql 2005 и более поздние версии имеют «рекомпиляцию на уровне операторов», при которой в кеше хранятся отдельные планы для каждого оператора в процедуре ... Таким образом, старая политика до 2005 года, запрещавшая помещать несколько операторов логического перехода в одну хранимую процедуру, больше не верна. .. - Чарльз Бретана (я полагаю, это было достаточно важно, чтобы поднять его из комментария)
Sql 2005 и более поздние версии имеют «рекомпиляцию на уровне операторов», которая хранит отдельные планы в кеше для каждого оператора в процедуре ... Таким образом, старая политика до 2005 года, запрещавшая помещать несколько операторов логических ветвей в одну хранимую процедуру, больше не верна. ..
В SQL Server 2005 и последующих версиях есть так называемая «перекомпиляция на уровне операторов». Проверить http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
По сути, каждый отдельный оператор, выполняемый обработчиком запросов, получает свой собственный оптимизированный план, который затем сохраняется в «Кэш планов» (вот почему они изменили имя с «Кэш процедур»).
Так что лучше разделить ваш T-SQL на отдельные операторы ...
Замените одну хранимую процедуру двумя. У оптимизатора запросов есть много места, чтобы начать поражать вас непредвиденными последствиями. Измените код клиента, чтобы определить, какой из них вызывать.
Бьюсь об заклад, если бы вы сделали это таким образом, нам бы не понадобился этот диалог.
И поставить индекс по идентификатору пользователя. Индексы существуют не зря, и вот она.
Я умоляю вас ... не соглашайтесь с тем ответом, который вы приняли. Это соблазнительный путь, ведущий к боли и страданиям.