Что лучше, динамический SQL или где дело?

Question

Мне нужно создать хранимую процедуру, которая принимает 12 аргументов, и запрос фильтруется с другой комбинацией этих аргументов. Все 12 аргументов не являются обязательными, так как если я передам 3, 5 или 12 аргументов, это зависит от введенных пользователем результатов поиска.

Я могу создать 2 способа: либо с помощью динамического запроса SQL, либо с помощью операторов "Case where". Пример этих запросов приведен ниже:

1. Динамический запрос

   DECLARE @sql VARCHAR(MAX) 
   DECLARE @condition VARCHAR(MAX)=''
   Declare @var1 varchar(10)
   Declare @var2 varchar(10)
   Declare @var3 varchar(10) 
   SET  @sql='SELECT * FROM TableDemo1 TD1 WITH(NOLOCK)
    INNER JOIN TableDemo2 TD2 ON TD1.Column1=TD2.Column2'
   if(@var1 <>0 and @var1 is not null)
   begin
       if(@condition<>'')
       begin
           set @condition=@condition + ' and TD1.columnTest1='+@var1)
       end
       else
       begin
           set @condition=' where TD1.columnTest1='+@var1
       end
   end
   if(@var2 <>0 and @var2 is not null)
   begin
       if(@condition<>'')
       begin
           set @condition=@condition + ' and TD2.columnTest2='+@var2)
       end
       else
       begin
           set @condition=' where TD2.columnTest2='+@var2
       end
   end
   
   if(@var3 <>0 and @var3 is not null)
   begin
       if(@condition<>'')
       begin
           set @condition=@condition + ' and TD1.columnTest3='+@var3)
       end
       else
       begin
           set @condition=' where TD1.columnTest3='+@var3
       end
   end
   SET @sql=@sql+@condition
   EXEC(@sql)
   

2. Запрос с делом где

   Declare @var1 varchar(10)
   Declare @var2 varchar(10)
   Declare @var3 varchar(10)
   SELECT *
   FROM TableDemo1 TD1 WITH(NOLOCK)
       INNER JOIN TableDemo2 TD2 ON TD1.Column1=TD2.Column2
   WHERE
     (CASE WHEN   
       (@var1<>0 and @var1 is not null)   
       THEN  
       CASE WHEN TD1.columnTest1=@var1 THEN 1 ELSE 0 END  
       ELSE 1 END)=1  
     AND   
     (CASE WHEN  
       (@var2<>0 and @var2 is not null)   
       THEN  
       CASE WHEN TD2.columnTest2=@var2 THEN 1 ELSE 0 END  
       ELSE 1 END)=1  
     AND
     (CASE WHEN
       (@var3<>0 AND @var3 IS NOT NULL)
       THEN
       CASE WHEN TD1.columnTest3 = @var3
       THEN 1 ELSE 0 END
       ELSE 1 END) =1
   

Это только часть моей хранимой процедуры, есть 7-8 таблиц с объединениями и, как указано выше, в различных запросах.

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

Я знаю о недостатках динамического SQL, но какую технику я должен использовать?

Answer 1

Обычно это зависит, но чаще всего я использую динамические запросы в качестве последнего средства. Что касается вашего вопроса, я бы, скорее всего, выбрал решение CASE, но я думаю, что ваши выражения CASE неоправданно сложны. Я бы заменил предложение WHERE на что-то вроде этого:

...
WHERE
    TD1.columnTest1 = COALESCE(NULLIF(@var1, 0), TD1.columnTest1)
    AND   
    TD2.columnTest2 = COALESCE(NULLIF(@var2, 0), TD2.columnTest2)
    AND   
    TD1.columnTest3 = COALESCE(NULLIF(@var3, 0), TD1.columnTest3)

При правильной индексации это не должно быть слишком медленным.

Answer 2

Динамический запрос приведет к сканированию индекса.

Случай приведет к последующему сканированию (т. Е. К прочтению всей таблицы).

Поэтому определенно следуйте динамическому запросу.

Answer 3

По моему опыту динамичный where пункт обеспечивает лучшую производительность. Особенно над большими наборами данных.

И очень хорошее объяснение есть в Catch All Queries.

Answer 4

Я использовал опцию "Andriy M", опубликованную с использованием функций coalesce и nullif.

Но эта опция работает только с оператором '=', но чтобы найти, как использовать ее с другими условиями, в одном примере используется ключевое слово 'IN'.

TD1.columnTest1 = (
    CASE 
        WHEN (
            ( TD1.columnTest1 
                IN (
                    SELECT item FROM dbo.Splitfunction(@comaSepValues,',')
                )
            ) 
            OR 
            NULLIF(@PlaceTypeCode,'') IS NULL 
        ) THEN columnTest1
        ELSE NULL 
    END
)

Дайте мне знать, если это работает или нет.

Answer 5

Есть два способа выполнить динамический запрос 1. Exec 2. sp_executeSQL

если вы хотите повторно использовать план выполнения, перейдите к опции sp_executeSQL.

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

Динамические запросы не всегда плохо работают, особенно если вы используете их надлежащим образом