Как избежать динамического 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 кэширует планы запросов, и это очень круто. Я также не знал об общих табличных выражениях. И ссылка, которую опубликовал Хенрик Опель, содержит множество хороших советов для этого типа задач.

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

ОБНОВИТЬ:

Итак, у меня есть рабочий пример этой поисковой системы в Интернете по адресу http://www.silverlightxap.com/controls Если кому интересно увидеть это в действии.

Ваш запрос идеально подходит для использования общего табличного выражения (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 кеширует план запроса. Честно говоря, мне кажется странным, что план запроса не кэшируется, если запрос проверяется на соответствие синтаксису / и т. Д. (С использованием exec или sp_executesql) - проверка будет происходить до плана запроса, почему последующий шаг будет пропущен?

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

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

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

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

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

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

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

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

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, поэтому лучше использовать первый метод. В Microsoft SQL Server последнее решение могло бы работать лучше.

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

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

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

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