ТО, ГДЕ идентификатор В (1, 2, 3, 4, 5, …) самое эффективное?

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

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

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

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

В продолжение заметки из Ян Зич относительно производительности с использованием различных реализаций временных таблиц, вот некоторые цифры из плана выполнения 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);
    }
}

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

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

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

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

Анализировать CSV легко: см. вопросы справа ...

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

WHERE IN

,

WHERE EXISTS

или

JOIN someholdingtable ON ...

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

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

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

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

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

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

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

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

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

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 вообще не будет выполнять запросы с дизъюнктивными критериями поиска, потому что оно будет выполнять только те запросы, для которых он знает, как выполнять поиск по индексу.

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

WHERE ID = 1 OR ID = 2 OR ID = 3 ...

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

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

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

Хотя, начиная с sql 2005, я предпочитаю передавать строку XML, которую также очень легко создать на стороне клиента ( c #, python, другой SQL SP) и «родной» для работы с 2005 г .:

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

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

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

при передаче . Просто помните, что XML - это CaSe-SensiTiv.