Как я избегаю динамического SQL при использовании неопределенного количества параметров?

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

В настоящее время моя база данных заполнена примерно 200 шейдерами и 500 тегами. Я провел, как мне кажется, несколько реалистичный тест, в котором я выполнил 35 различных поисковых запросов к моей сохраненной процедуре с различным количеством тегов, с поисковым запросом и без него. Я поместил все это в один оператор SQL, а затем протестировал результаты в ASP.NET. Он последовательно выполнял эти 35 поисковых запросов менее чем за 200 миллисекунд. Если я уменьшу его до 5 поисков, то время сократится до 10 мс. Такое представление потрясающее. Помогает то, что размер моей базы данных небольшой. Но я думаю, что также помогает то, что запрос хорошо использует индексы.

Одна вещь, которую я изменил в своем запросе, - это способ поиска тегов. Теперь я ищу теги по их идентификатору, а не по имени. Сделав это, я смогу сократить на 1 соединение и воспользоваться преимуществом использования индекса для поиска. А потом еще добавил "dbo". перед именами таблиц после того, как узнал, что SQL кэширует запросы для каждого пользователя.

Если кому-то интересно, вот моя законченная сохраненная процедура:

ALTER PROCEDURE [dbo].[search] 
    @search_term    varchar(100) = NULL,
    @tag1           int = NULL,
    @tag2           int = NULL,
    @tag3           int = NULL,
    @tag4           int = NULL,
    @tag5           int = NULL,
    @tag6           int = NULL,
    @tag7           int = NULL,
    @tag8           int = NULL,
    @tag9           int = NULL,
    @tag10          int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF LEN(@search_term) > 0
        BEGIN
            SELECT s.shader_id, s.page_name, s.name, s.description, s.download_count, s.rating, s.price FROM dbo.shader s 
            INNER JOIN FREETEXTTABLE(dbo.shader, *, @search_term) AS ft ON s.shader_id = ft.[KEY]
            WHERE (@tag1 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag1))
            AND   (@tag2 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag2))
            AND   (@tag3 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag3))
            AND   (@tag4 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag4))
            AND   (@tag5 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag5))
            AND   (@tag6 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag6))
            AND   (@tag7 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag7))
            AND   (@tag8 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag8))
            AND   (@tag9 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag9))
            AND   (@tag10 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag10))
            ORDER BY ft.[RANK] DESC
        END
    ELSE
        BEGIN
            SELECT s.shader_id, s.page_name, s.name, s.description, s.download_count, s.rating, s.price FROM dbo.shader s 
            WHERE (@tag1 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag1))
            AND   (@tag2 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag2))
            AND   (@tag3 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag3))
            AND   (@tag4 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag4))
            AND   (@tag5 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag5))
            AND   (@tag6 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag6))
            AND   (@tag7 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag7))
            AND   (@tag8 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag8))
            AND   (@tag9 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag9))
            AND   (@tag10 IS NULL OR EXISTS(SELECT 1 AS num FROM dbo.tag_shader_xref tsx WHERE tsx.shader_id = s.shader_id AND tsx.tag_id = @tag10))
        END
END

Хотя я не исчерпал все варианты, это было все еще хорошее упражнение, потому что я доказал себе, что мой дизайн базы данных очень хорошо подходит для этой задачи. И я также многому научился, разместив этот вопрос. Я знал, что exec () плохой, потому что он не кэширует план запроса. Но я не знал, что sp_executesql кэширует планы запросов, и это очень круто. Я также не знал об общих табличных выражениях. И ссылка, которую опубликовал Хенрик Опель, содержит множество хороших советов для такого рода задач.

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

ОБНОВЛЕНИЕ:

Ваш запрос идеально подходит для использования общего табличного выражения (CTE) из-за дублированного коррелированного подзапроса в предложениях EXISTS:

WITH attribute AS(
  SELECT tsx.shader_id,
         t.tag_name
    FROM TAG_SHADER_XREF tsx ON tsx.shader_id = s.shader_id
    JOIN TAG t ON t.tad_id = tsx.tag_id)
SELECT ft.[RANK], 
       s.shader_id, 
       s.page_name, 
       s.name, 
       s.description, 
       s.download_count, 
       s.rating, 
       s.price 
  FROM SHADER s 
  JOIN FREETEXTTABLE(SHADER, *, @search_term) AS ft ON s.shader_id = ft.[KEY]
  JOIN attribute a1 ON a1.shader_id = s.shader_id AND a1.tag_name = 'color'
  JOIN attribute a2 ON a2.shader_id = s.shader_id AND a2.tag_name = 'saturation'
 ORDER BY ft.[RANK] DESC

Используя CTE, я также преобразовал EXISTS в JOIN.

Говоря о вашем исходном вопросе относительно использования динамического SQL - единственная альтернатива - проверить входящий параметр на наличие критерия выхода перед его применением. IE:

WHERE (@param1 IS NULL OR a1.tag_name = @param1)

Если @ param1 содержит значение NULL, последняя часть SQL в скобках не выполняется. Я предпочитаю динамический SQL-подход, потому что в противном случае вы создаете JOINs / etc, которые могут не использоваться - это пустая трата ресурсов.

Какие проблемы с производительностью, по вашему мнению, существуют с динамическим SQL? При использовании sp_executesql план запроса кэшируется. Честно говоря, мне кажется странным, что план запроса не

Как избежать динамического SQL при использовании неопределенное количество параметров?

Вместо этого вы можете динамически сгенерировать соответствующие параметризованные (подготовленные) шаблоны SQL .

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

Это может быть сделано в приложении или достаточно сложной хранимой процедуре.

Я предпочитаю этот подход, скажем, процедуре, которая занимает не более 10

Ответ Билла Карвина GROUP BY на этот вопрос, вероятно, является самым простым шаблоном для создания шаблона - вы просто объединяете заполнители для предикат IN и обновление предложения COUNT .Другие решения, включающие соединения по тегам, потребуют увеличения псевдонимов таблиц (например, xref1 , xref2 и т. Д.) По мере продвижения.

Я видел два типа решений этой проблемы:

Первый - присоединить таблицу шейдера к тегам (через xref по мере необходимости) один раз для каждого тега, который вы ищете. Результат внутреннего соединения включает только те шейдеры, которые соответствуют всем тегам.

SELECT s.*
FROM shader s
JOIN tag_shader_xref x1 ON (s.shader_id = x1.shader_id)
JOIN tag t1 ON (t1.tag_id = x1.tag_id AND t1.tag_name = 'color')
JOIN tag_shader_xref x2 ON (s.shader_id = x2.shader_id)
JOIN tag t2 ON (t2.tag_id = x2.tag_id AND t2.tag_name = 'saturation')
JOIN tag_shader_xref x3 ON (s.shader_id = x3.shader_id)
JOIN tag t3 ON (t3.tag_id = x3.tag_id AND t3.tag_name = 'transparency');

Второе решение состоит в том, чтобы присоединиться к этим тегам один раз, ограничив теги тремя необходимыми вам, а затем GROUP BY shader_id , чтобы вы могли подсчитывать совпадения. Счетчик будет равен трем только в том случае, если были найдены все теги (при условии уникальности в таблице внешних ссылок).

SELECT s.shader_id
FROM shader s
JOIN tag_shader_xref x ON (s.shader_id = x.shader_id)
JOIN tag t ON (t.tag_id = x.tag_id 
  AND t.tag_name IN ('color', 'saturation', 'transparency'))
GROUP BY s.shader_id
HAVING COUNT(DISTINCT t.tag_name) = 3;

Что следует использовать? Зависит от того, насколько хорошо ваш бренд базы данных оптимизирует тот или иной метод. Обычно я использую MySQL, что не так хорошо с GROUP BY , поэтому лучше использовать первый метод.

Возможно, это не самый быстрый метод, но не могли бы вы просто сгенерировать строку запроса для каждого тега, а затем соединить их с помощью «INTERSECT»?

Изменить: не видел тег sproc, поэтому Не знаю, возможно ли это.

Я поддержал ответ Хенрика, но я могу придумать еще одну альтернативу - поместить поисковые теги во временную таблицу или табличную переменную, а затем выполнить JOIN или использовать предложение IN с под- ВЫБРАТЬ. Поскольку вам нужны результаты с всеми поисковыми тегами, вам нужно сначала подсчитать количество тегов запроса, а затем найти результаты, в которых количество сопоставленных тегов равно этому числу.

Как ввести значения в стол? Если теги передаются в вашу хранимую процедуру, и если вы используете SQL Server 2008, то вы можете использовать новую функцию возвращающих табличное значение параметров и передать табличную переменную непосредственно в вашу хранимую процедуру.

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

... WHERE @SearchTagCount = (SELECT COUNT(tsx.shader_id) FROM tag_shader_xref tsx
INNER JOIN tag t ON tsx.tag_id = t.tag_id
WHERE tsx.shader_id = s.shader_id AND t.tag_name IN (SELECT * FROM dbo.SplitString(@SearchTags,',')))

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

Конечно, если у вас есть значения (ключ) из таблицы поиска для этих тегов, я бы использовал их.

РЕДАКТИРОВАТЬ:

Поскольку вам нужны все теги в результате ....

К сожалению, Я думаю, что бы вы ни делали, SP будет под угрозой из-за регенерируемого плана.

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

Я все еще думаю, что это означает, что ваш SP имеет потерял большую часть своего кэшированного качества, но я считаю, что это лучше, чем прямая строка exec.