Является ли ГДЕ ID IN (1, 2, 3, 4, 5,...) наиболее эффективным?

Я знаю, что эта тема была избита до смерти, но кажется, что многие статьи в Интернете часто ищут самый элегантный способ, а не самый эффективный способ его решения. Вот проблема. Мы создаем приложение, в котором один из общих запросов к базе данных будет включать манипуляции (SELECTs и UPDATE) на основе списка пользователей, предоставленного пользователем. Ожидается, что в таблице, которая будет содержаться сотни тысяч строк, и предоставленные пользователем списки идентификаторов могут быть потенциально неограниченными, bust они будут наиболее вероятными с точки зрения десятков или сотен (мы можем ограничить его по соображениям производительности позже).

Если мое понимание того, как работают базы данных в целом, является правильным, наиболее эффективным является просто использовать конструкцию WHERE ID IN (1, 2, 3, 4, 5, ...) и строить запросы динамически. Ядро проблемы состоит в том, что входные списки идентификаторов будут действительно произвольными, и поэтому, независимо от того, насколько умны базы данных или насколько их мы умело используем, у нас всегда есть случайное подмножество целых чисел, и поэтому в конечном итоге каждый подход должен внутренне сводятся к чему-то вроде WHERE ID IN (1, 2, 3, 4, 5, ...) в любом случае.

Можно найти множество подходов по всему Интернету. Например, один включает объявление переменной таблицы, передачу списка идентификаторов в процедуру хранилища в виде строки с разделителями-запятыми, разделение ее в процедуре хранилища, вставку идентификаторов в переменную таблицы и присоединение к ней основной таблицы, то есть нечто вроде это:

-- 1. Temporary table for ID’s:
DECLARE @IDS TABLE (ID int);

-- 2. Split the given string of ID’s, and each ID to @IDS.
-- Omitted for brevity.

-- 3. Join the main table to @ID’s:
SELECT MyTable.ID, MyTable.SomeColumn
FROM MyTable INNER JOIN @IDS ON MyTable.ID = @IDS.ID;

Устраняя проблемы с манипуляциями со строками, я думаю, что в основном это происходит в том, что на третьем этапе SQL Server говорит: "Спасибо, это хорошо, но мне просто нужен список идентификаторов", и это сканирует переменную таблицы @IDS, а затем n ищет в MyTable, где n - количество идентификаторов. Я провела некоторые элементарные оценки производительности и проверила план запросов, и кажется, что это то, что происходит. Таким образом, переменная таблицы, конкатенация и разделение строк и все дополнительные INSERT - ни для чего.

Правильно ли я? Или я что-то упускаю? Действительно ли какой-то умный и эффективный способ? В основном, то, что я говорю, заключается в том, что SQL Server должен делать n указателей, независимо от того, что и как сформулировать запрос, поскольку WHERE ID IN (1, 2, 3, 4, 5, ...) является самым простым способом его запросить.

Ответы

Ответ 1

Ну, это зависит от того, что происходит на самом деле. Как пользователь выбирает эти идентификаторы?

Кроме того, это не просто эффективность; там также безопасность и правильность беспокоиться. Когда и как пользователь сообщает базе данных об их идентификационных номерах? Как вы включаете их в запрос?

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

Я дам вам, что вы вряд ли будете намного лучше, чем IN (1,2,3..n), для небольшого (сгенерированного пользователем) n. Но вам нужно подумать о том, как вы создаете этот запрос. Вы собираетесь использовать динамический SQL? Если да, то как вы будете защищать его от инъекций? Будет ли сервер кэшировать план выполнения?

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

Не забывайте старую пословицу (с извинениями Бенджамин Франклин):

Тот, кто будет торговать правильностью, не заслуживает ни

Ответ 2

Будьте осторожны; во многих базах данных IN (...) ограничено фиксированным числом вещей в предложении IN. Например, я думаю, что это 1000 в Oracle. Это большой, но, возможно, стоило бы знать.

Ответ 3

Предложение IN не гарантирует INDEX SEEK. Я столкнулся с этой проблемой, прежде чем использовать SQL Mobile edition в Pocket с очень небольшим количеством памяти. Замена IN (list) на список предложений OR увеличила мой запрос на 400% aprox.

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

Ответ 4

Для меня IN (...) не является предпочтительным вариантом из-за многих причин, включая ограничение количества параметров.

Следуя за запиской от Яна Зича относительно производительности, использующей различные реализации temp-table, вот некоторые цифры из плана выполнения SQL:

  • XML-решение: 99% времени - анализ XML-документов
  • процедура, разделенная запятыми, с использованием UDF из CodeProject: 50% -ное сканирование временной таблицы, поиск по индексу 50%. Можно сказать, что это наиболее оптимальная реализация синтаксического анализа строк, но я не хотел ее создавать (я с удовольствием проведу другой).
  • CLR UDF для разделения строки: 98% - поиск индекса.

Вот код для CLR UDF:

public class SplitString
{
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable InitMethod(String inputString)
    {
        return inputString.Split(',');
    }

    public static void FillRow(Object obj, out int ID)
    {
        string strID = (string)obj;
        ID = Int32.Parse(strID);
    }
}

Поэтому мне нужно будет согласиться с Jan, что XML-решение неэффективно. Поэтому, если список, разделенный запятыми, должен быть передан как фильтр, простой CLR UDF кажется оптимальным с точки зрения производительности.

Я проверил поиск записи 1K в таблице 200K.

Ответ 5

У таблицы var есть проблемы: использование таблицы temp с индексом имеет преимущества для статистики.

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

Разбор CSV прост: см. вопросы справа...

Ответ 6

По существу, я согласен с вашим наблюдением; Оптимизатор SQL Server в конечном итоге выберет наилучший план для анализа списка значений и, как правило, приравнивается к одному и тому же плану, независимо от того, используете ли вы

WHERE IN

или

WHERE EXISTS

или

JOIN someholdingtable ON ...

Очевидно, существуют и другие факторы, которые влияют на выбор плана (например, индексы покрытия и т.д.). Причина, по которой люди имеют различные методы для передачи в этом списке значений хранимой процедуры, заключается в том, что до SQL 2008 действительно не было простого способа передачи нескольких значений. Вы можете сделать список параметров (WHERE IN (@param1, @param2)...), или вы можете разобрать строку (метод, который вы показываете выше). Начиная с SQL 2008, вы также можете передавать табличные переменные, но общий результат тот же.

Так что да, неважно, как вы получите список переменных для запроса; однако есть и другие факторы, которые могут повлиять на производительность указанного запроса, как только вы получите список переменных.

Ответ 7

Когда-то давно я обнаружил, что в конкретной СУБД, с которой я работал, список IN был более эффективным до некоторого порога (который был, IIRC, что-то вроде 30-70), и после этого он было более эффективно использовать временную таблицу для хранения списка значений и соединения с временной таблицей. (СУБД очень быстро создавали временные таблицы, но даже с накладными расходами на создание и заполнение таблицы temp запросы выполнялись быстрее в целом). Это было с обновленной статистикой по основным таблицам данных (но это также помогло обновите статистику для таблицы temp).

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

Ответ 8

В SQL Server 2008 или более поздней версии вы должны искать табличные параметры.

2008 упрощает передачу списка разделенных запятыми SQL Server с помощью этого метода.

Вот отличный источник информации и тестов производительности по этому вопросу:

Arrays-in-sql-2008

Вот отличный учебник:

passing-table-valued-parameters-in-sql-server-2008

Ответ 9

В течение многих лет я использую 3 подхода, но когда я начинаю использовать OR/M, это кажется ненужным.

Даже загрузка каждой строки по id не такая неэффективная, как кажется.

Ответ 10

Если проблемы с манипуляцией строк отложены, я думаю, что:

WHERE ID = 1 ИЛИ ID = 2 ИЛИ ID = 3...

более эффективен, тем не менее я бы этого не сделал.

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

Ответ 11

Чтобы ответить на вопрос напрямую, нет способа передать (динамический) список аргументов в процедуру SQL Server 2005. Поэтому большинство людей в этих случаях передают список идентификаторов, разделенных запятыми, что я и сделал.

С sql 2005, хотя я предпочитаю передавать и XML-строку, которую также очень легко создать на стороне клиента (С#, python, другой SQL SP) и "native" для работы с 2005 года:

CREATE PROCEDURE myProc(@MyXmlAsSTR NVARCHAR(MAX)) AS BEGIN
    DECLARE @x XML
    SELECT @x = CONVERT(XML, @MyXmlAsSTR)

Затем вы можете присоединиться к базовому запросу непосредственно с выбором XML как (не тестировался):

SELECT      t.*
FROM        myTable t
INNER JOIN  @x.nodes('/ROOT/ROW') AS R(x)
        ON  t.ID = x.value('@ID', 'INTEGER')

при передаче <ROOT><ROW ID="1"/><ROW ID="2"/></ROOT>. Просто помните, что XML - это CaSe-SensiTiv.

Ответ 12

select t.*
from (
    select id = 35 union all
    select id = 87 union all
    select id = 445 union all
    ...
    select id = 33643
) ids
join my_table t on t.id = ids.id

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

Обратите внимание, что механизмы запросов обычно обрабатывают

select t.*
from my_table t
where t.id in (35, 87, 445, ..., 33643)

как эквивалент

select t.*
from my_table t
where t.id = 35 or t.id = 87 or t.id = 445 or ... or t.id = 33643

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