Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 @t is null ot @t = t  [new]
mos
Member

Откуда: Vilnius
Сообщений: 701
Добрый день

Как лутче реализовать запрос с многочисленным количеством переменных и их проверку на null
Красиво, но используються не оптимально индексы:

select *
from table t
where (@par1 is null or @par1 = t.col1)
and (@par2 is null or @par2 = t.col2)
and (@par3 is null or @par3 = t.col3)
and (@par4 is null or @par4 = t.col4)
and (@par5 is null or @par5 = t.col5)
and (@par6 is null or @par6 = t.col6)

Какие best practices?
5 сен 09, 11:25    [7621651]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
aleks2
Guest
mos,

Это и есть "зэ бест". Не парься.

Если индексы в данном случае не используются - значит нема подходящего или что-то с приведением типов.
5 сен 09, 12:43    [7621715]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
step_ks
Member

Откуда:
Сообщений: 936
mos,можете попробовать динамику, если допустимо.
5 сен 09, 13:47    [7621800]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
mos
Member

Откуда: Vilnius
Сообщений: 701
Динамика требует прав на все объекты
6 сен 09, 00:36    [7622546]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
Andret
Member

Откуда: Киев
Сообщений: 84
иногда может помочь
option (recompile)

или (и), как советовали коллеги - динамику.

Но тоже не всегда поможет. А может, из-за того, что в самом запросе можно изменить значение переменной:
declare	@i int
select	@i = id + 1
from	sysobjects
where	@i is null or id = @i
6 сен 09, 23:02    [7623775]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
Нектотам
Guest
mos
Какие best practices?

1. Лучше всё же динамический запрос в этом случае.
2. Если уж невмоготу, то можно попробовать заменить
(@par1 is null or @par1 = t.col1)
на
(ISNULL(@par1,t.col1) = t.col1)
Оптимизатор MS SQL в этом случае как-то адекватнее думает.

+ Небольшая проверка
Вот тестовый скрипт
CREATE TABLE #t (id INT IDENTITY (1,1) PRIMARY KEY NONCLUSTERED, data1 NVARCHAR(50) NOT NULL UNIQUE CLUSTERED, data2 NVARCHAR(50) NOT NULL);

DECLARE @n INT;
SET @n = 10000000;
INSERT INTO #t (data1, data2) SELECT CAST(NEWID() AS nvarchar(50)) data1, CAST(NEWID() AS nvarchar(50)) data2
FROM (SELECT number FROM master.dbo.spt_values WHERE type = 'P' AND number<@n) numbers
CROSS JOIN (SELECT number*2048 number FROM master.dbo.spt_values WHERE type = 'P' AND number<=@n/2048) numbers2048
CROSS JOIN (SELECT number*4194304 number FROM master.dbo.spt_values WHERE type = 'P' AND number<=@n/4194304) numbers4194304
WHERE (numbers.number + numbers2048.number + numbers4194304.number)<@n;

GO

DECLARE @id INT;
DECLARE @d DATETIME;
SET @id = 100;

SET @d = GETDATE();
SET STATISTICS PROFILE ON;
SELECT id, data1, data2 FROM #t t WHERE @id is null or @id = t.id;
SET STATISTICS PROFILE OFF;
PRINT DATEDIFF(ms, @d, GETDATE());

SET @d = GETDATE();
SET STATISTICS PROFILE ON;
SELECT id, data1, data2 FROM #t t WHERE ISNULL(@id,t.id)= t.id;
SET STATISTICS PROFILE OFF;
PRINT DATEDIFF(ms, @d, GETDATE());

DROP TABLE #t;
Вот результат:
(10000000 row(s) affected)
id          data1                                              data2
----------- -------------------------------------------------- --------------------------------------------------
100         AC6D5B33-EA4A-46BA-9476-EC61FE9AB92A               2ED9C134-B37E-4976-84E9-24A3ABCBCA62

(1 row(s) affected)

Rows                 Executes             StmtText                                                                                                                             StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                  DefinedValues                       EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                          Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------- ----------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
1                    1                    SELECT id, data1, data2 FROM #t t WHERE @id is null or @id = t.id;                                                                   1           1           0           NULL                           NULL                           NULL                                                                                                      NULL                                3000001       NULL          NULL          NULL        162,1744         NULL                                NULL     SELECT                                                           0        NULL
1                    1                      |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#t] AS [t]), WHERE:([@id] IS NULL OR [@id]=[tempdb].[dbo].[#t].[id] as [t].[id]))  1           2           1           Clustered Index Scan           Clustered Index Scan           OBJECT:([tempdb].[dbo].[#t] AS [t]), WHERE:([@id] IS NULL OR [@id]=[tempdb].[dbo].[#t].[id] as [t].[id])  [t].[id], [t].[data1], [t].[data2]  3000001       151,1742      11,00016      139         162,1744         [t].[id], [t].[data1], [t].[data2]  NULL     PLAN_ROW                                                         0        1

(2 row(s) affected)

1753
id          data1                                              data2
----------- -------------------------------------------------- --------------------------------------------------
100         AC6D5B33-EA4A-46BA-9476-EC61FE9AB92A               2ED9C134-B37E-4976-84E9-24A3ABCBCA62

(1 row(s) affected)

Rows                 Executes             StmtText                                                                                                                                                     StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                               DefinedValues          EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                          Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
1                    1                    SELECT id, data1, data2 FROM #t t WHERE ISNULL(@id,t.id)= t.id;                                                                                              2           1           0           NULL                           NULL                           NULL                                                                                                                                   NULL                   1             NULL          NULL          NULL        94,17101         NULL                                NULL     SELECT                                                           0        NULL
1                    1                      |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[data1]) OPTIMIZED)                                                                                      2           2           1           Nested Loops                   Inner Join                     OUTER REFERENCES:([t].[data1]) OPTIMIZED                                                                                               NULL                   1             0             4,18E-06      139         94,17101         [t].[id], [t].[data1], [t].[data2]  NULL     PLAN_ROW                                                         0        1
1                    1                           |--Index Scan(OBJECT:([tempdb].[dbo].[#t] AS [t]),  WHERE:(isnull([@id],[tempdb].[dbo].[#t].[id] as [t].[id])=[tempdb].[dbo].[#t].[id] as [t].[id]))  2           4           2           Index Scan                     Index Scan                     OBJECT:([tempdb].[dbo].[#t] AS [t]),  WHERE:(isnull([@id],[tempdb].[dbo].[#t].[id] as [t].[id])=[tempdb].[dbo].[#t].[id] as [t].[id])  [t].[id], [t].[data1]  1             76,36757      11,00016      87          87,36773         [t].[id], [t].[data1]               NULL     PLAN_ROW                                                         0        1
1                    1                           |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#t] AS [t]), SEEK:([t].[data1]=[tempdb].[dbo].[#t].[data1] as [t].[data1]) LOOKUP ORDERED FORWARD)    2           6           2           Clustered Index Seek           Clustered Index Seek           OBJECT:([tempdb].[dbo].[#t] AS [t]), SEEK:([t].[data1]=[tempdb].[dbo].[#t].[data1] as [t].[data1]) LOOKUP ORDERED FORWARD              [t].[data2]            1             0,003125      0,0001581     61          0,0032831        [t].[data2]                         NULL     PLAN_ROW                                                         0        1

(4 row(s) affected)

993
Обратите внимание:
1. Во втором запросе использован индекс по id
2. EstimateRows во втором запросе более правильное
3. Ну и время выполнения во втором случае меньше
4. Но всё равно скан никуда не уходит :(
7 сен 09, 06:58    [7624321]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
step_ks
Member

Откуда:
Сообщений: 936
Нектотам

2. Если уж невмоготу, то можно попробовать заменить
(@par1 is null or @par1 = t.col1)
на
(ISNULL(@par1,t.col1) = t.col1)
Оптимизатор MS SQL в этом случае как-то адекватнее думает.

Это не эквивалентые условия в общем случае.
7 сен 09, 08:24    [7624406]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
Нектотам
Guest
step_ks
Это не эквивалентые условия в общем случае.

Да, согласен, но для случая, когда t.col1 is null такие "фильтры" обычно не имеют смысла.
7 сен 09, 08:28    [7624416]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
step_ks
Member

Откуда:
Сообщений: 936
Нектотам

Да, согласен, но для случая, когда t.col1 is null такие "фильтры" обычно не имеют смысла.

Почему же? Либо юзер ищет по этому полю либо нет. Может, поле необязательное в документе.
7 сен 09, 08:33    [7624425]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
Нектотам
Guest
А вот такой вариант индексы использует. Но выглядеть уже на 5-6 фильтрах он будет мегаужасно :)

select * from table t where @par1 is null and @par2 is null union all
select * from table t where @par1 = t.col1 and @par2 is null union all
select * from table t where @par1 is null and @par2 = t.col2 union all
select * from table t where @par1 = t.col1 and @par2 = t.col2
7 сен 09, 08:44    [7624444]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
step_ks
Member

Откуда:
Сообщений: 936
тогда уж лучше if
7 сен 09, 08:46    [7624445]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
Нектотам
Guest
step_ks
Почему же? Либо юзер ищет по этому полю либо нет. Может, поле необязательное в документе.

Согласен, для нуллябельных полей фигню написал.
7 сен 09, 08:49    [7624449]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
Crimean
Member

Откуда:
Сообщений: 13148
mos
Динамика требует прав на все объекты


только до 2000 включительно. под 2005 уже элементарно обходится. и, часто, не худший выход
7 сен 09, 10:29    [7624905]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
mos
Member

Откуда: Vilnius
Сообщений: 701
и как обходится динамика? У меня 2008 уже :)
7 сен 09, 10:31    [7624913]     Ответить | Цитировать Сообщить модератору
 Re: @t is null ot @t = t  [new]
Crimean
Member

Откуда:
Сообщений: 13148
mos
и как обходится динамика? У меня 2008 уже :)


with execute as, в чем проблемы? в 2000 динамика всегда выполнялась под учеткой запустившего. начиная с 2005 это поменяли, дав разработчику средства управления этим
7 сен 09, 11:02    [7625137]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить