T-SQL: необязательные объединения таблиц?

ProgramBox

T-SQL: необязательные объединения таблиц?

Post author:admin
Запись опубликована:15 апреля, 2023
Post category:Вопросы по программированию

#sql-server-2008 #tsql

Вопрос:

Допустим, у меня есть хранимая процедура, которая будет выполнять сложную логику при изменении параметра @ComplicatedSearch = 1 . Когда значение равно 1, я заполняю переменную table @ValidIds допустимыми строками, которые может вернуть эта процедура. Когда значение равно 0, логика обходится, и нам не нужно отфильтровывать возвращаемые строки.

Итак, следуя этой логике, я получаю инструкцию, подобную so:

 SELECT
    m.*
    ,a.*
FROM
    MyTable m
    INNER JOIN AdditionalInfoTable a
       ON m.Id = a.MyTableId
WHERE
   (@ComplicatedSearch = 0 OR EXISTS(SELECT * FROM @ValidIds WHERE Id = m.Id))

Это работает нормально; однако я считаю, что было бы эффективнее присоединить MyTable к @ValidIds, когда это применимо, в отличие от использования EXISTS() , особенно когда MyTable содержит большое количество строк.

Есть ли какой-либо способ сделать что-то вроде того, что я привел ниже, без написания нескольких запросов? (фактический запрос очень большой, поэтому наличие нескольких версий с объединениями и без них было бы не идеально)

 SELECT
    m.*
    ,a.*
FROM
    MyTable m
    ONLY DO THIS IF ComplicatedSearch = 1 PLEASE: INNER JOIN @ValidIds v
       ON m.Id = v.Id
    INNER JOIN AdditionalInfoTable a
       ON m.Id = a.MyTableId

1. Ваше убеждение, что JOIN было бы более производительным, чем EXISTS , неуместно. EXISTS очень эффективно реализовано в SQL Server как полусоединение и может использовать любой из доступных типов соединения.

2. @MartinSmith: Я думаю, чего я пытаюсь избежать, так это полного сканирования таблицы в MyTable; Я надеялся, что объединение позволит более эффективно захватывать строки

3. Не уверен точно, какой план вы получили бы с этим, не настроив тест на этом этапе. Вы могли бы попробовать добавить, OPTION (RECOMPILE) чтобы учитывалось как значение @ComplicatedSearch , так и мощность табличной переменной. Также убедитесь, что переменная table объявлена с Id primary key , чтобы получить индекс, созданный для этого столбца.

Ответ №1:

Другой вариант:

 SELECT
   m.*     
  ,a.*
FROM MyTable m    
 INNER JOIN @ValidIds v
  ON m.Id = case
              when @ComplicatedSearch = 1 then v.Id  --  Filter rows
              else m.Id  --  Select all rows
            end
 INNER JOIN AdditionalInfoTable a    
  ON m.Id = a.MyTableId

Вам нужно будет выполнить тестирование производительности, чтобы убедиться, что оно достаточно эффективно. Некоторые быстрые тесты показали, что (по моим данным) был сгенерирован один и тот же план запроса независимо от того, был ли первый вызов для complex или не complex.

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

1. Идеально! Единственное, на что следует обратить внимание, это то, что мне нужно будет вставить фиктивную строку в эту таблицу, когда @ComplicatedSearch равно 0, чтобы предотвратить уничтожение всего этим внутренним объединением

2. Ого! Я пропустил это! (Я просто жестко запрограммировал некоторые значения при тестировании сгенерированных планов запросов.)

3. Не уверен, почему это принято, поскольку, похоже, это не работает. В случае, когда вы присоединяетесь обратно к той же таблице, SQL server все равно должен включать строки для @validIds, поскольку эта таблица была присоединена. Результатом является значительно большее количество строк (и намного более дорогой и медленный запуск запроса), чем предполагалось.

Ответ №2:

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

Таким образом, я бы написал процедуру, больше похожую

 if @ComplicatedSearch = 1
   exec simple_search
else
   exec complex_search

Где simple_search включает ваш первый запрос, а complex_search также присоединяется к @ValidIds

Да, вы получаете запрос дважды, но чтобы справиться с этим, я бы создал представление

 create view helper as
begin
SELECT
    m.*
    ,a.*
FROM
    MyTable m
    INNER JOIN AdditionalInfoTable a
       ON m.Id = a.MyTableId
end

и затем простой выбор из этого представления и сложные соединения с @ValidIds