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

Откуда: Москва
Сообщений: 4804
Здравствуйте Господа,

Есть такой запрос:

DECLARE @T TABLE 
(
	A INT NOT NULL, 
	B INT NOT NULL
) 

INSERT INTO @T (A,B)
SELECT 
	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
	ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM spt_values


SELECT * FROM @T
WHERE (A <> B) OR (A IS NULL AND B IS NOT NULL) OR (A IS NOT NULL AND B IS NULL)


Последнее условие содержит бессмысленные части

OR (A IS NULL AND B IS NOT NULL) OR (A IS NOT NULL AND B IS NULL) -- всегда FALSE ибо

A INT NOT NULL,
B INT NOT NULL

Внимание вопрос знатокам.
Если данных запрос откомпилирован (например, находится внутри процедуры) сумеет ли оптимизатор выкинуть из запроса эти паразитные условия и при каких условиях?

@T может быть вьюхой или физической таблицей, где гарантировано

A INT NOT NULL,
B INT NOT NULL

Скажу сразу, что написал бы ISNULL(A, -1) <> ISNULL(B, -1), но имею дело с ситуацией, когда тип поля может быть неизвестен.
20 апр 16, 20:52    [19085139]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
А в плане не видно, что и так выкидывает?

К сообщению приложен файл. Размер - 49Kb
20 апр 16, 21:29    [19085239]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
a_voronin,

но и A всегда равно B в данном случае. Неудачный пример?
21 апр 16, 10:55    [19086849]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
0-0
Guest
a_voronin
Скажу сразу, что написал бы ISNULL(A, -1) <> ISNULL(B, -1), но имею дело с ситуацией, когда тип поля может быть неизвестен.


Свой Фреймворк изобретаете?
21 апр 16, 11:22    [19087022]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
0-0
Guest
a_voronin,

Если не хочется писать условие с NULL-ами, то никто не отменял Булеву алгебру.

Надеюсь, слышали про такую?

declare @a int , @b int 

select ~(
select case when @a = @b then cast(1 as bit) else cast(0 as bit) end)

go

declare @a int = 3, @b int 

select ~(
select case when @a = @b then cast(1 as bit) else cast(0 as bit) end)

go

declare @a int = 3, @b int = 2

select ~(
select case when @a = @b then cast(1 as bit) else cast(0 as bit) end)

go

declare @a int = 3, @b int = 3

select ~(
select case when @a = @b then cast(1 as bit) else cast(0 as bit) end)

go
21 апр 16, 11:27    [19087055]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
a_voronin,

почему не годится
SELECT * FROM @T WHERE NOT EXISTS(SELECT A INTERSECT SELECT B);
???
21 апр 16, 11:27    [19087058]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
iljy
Member

Откуда:
Сообщений: 8711
iap,

при
INSERT INTO @T (A,B)
SELECT 
	ROW_NUMBER() OVER (ORDER BY newid()),
	ROW_NUMBER() OVER (ORDER BY newid())
FROM master..spt_values


план такой же, так что нормально.

У меня другой вопрос есть. Я частенько для проверки условий использую exists (select A intersect select B), чтобы не выписывать IS NULL и т.п. И в такой форме сервер все прекрасно обрабатывает, даже индексы использует. Но вот обратное условие (not exists) вызывает у него ступор и он разворачивает его в Left Anti Semi join. Причем даже ухищрения типа WHERE case when exists (select A intersect select B) then 1 else 0 end = 0 не помогают :)
21 апр 16, 11:34    [19087110]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
a_voronin,

на самом деле это выражение приводится к

(A <> B) and NOT (a is null and b is null).
21 апр 16, 11:37    [19087134]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Владислав Колосов,

а (A IS NOT NULL AND B IS NULL)
21 апр 16, 11:39    [19087147]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
TaPaK,

not (a=b or (a is null and b is null))
21 апр 16, 11:42    [19087170]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
А, в смысле unknown не попадет в итог. Логично.
21 апр 16, 11:43    [19087179]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
iljy,

Потому что стоит это дело в MERGE запросе и выглядит вот так
			WHEN MATCHED AND (
				target.DELETED <> source.DELETED
				OR (target.[shk_id] <> source.[shk_id]) OR (target.[shk_id] IS NULL AND source.[shk_id] IS NOT NULL) OR (target.[shk_id] IS NOT NULL AND source.[shk_id] IS NULL)
				OR (target.[SHK_DeletedData_SHK_rv] <> source.[SHK_DeletedData_SHK_rv]) OR (target.[SHK_DeletedData_SHK_rv] IS NULL AND source.[SHK_DeletedData_SHK_rv] IS NOT NULL) OR (target.[SHK_DeletedData_SHK_rv] IS NOT NULL AND source.[SHK_DeletedData_SHK_rv] IS NULL)
				OR (target.[chrt_id] <> source.[chrt_id]) OR (target.[chrt_id] IS NULL AND source.[chrt_id] IS NOT NULL) OR (target.[chrt_id] IS NOT NULL AND source.[chrt_id] IS NULL)
				OR (target.[SHK_PredOrder_Link_rv] <> source.[SHK_PredOrder_Link_rv]) OR (target.[SHK_PredOrder_Link_rv] IS NULL AND source.[SHK_PredOrder_Link_rv] IS NOT NULL) OR (target.[SHK_PredOrder_Link_rv] IS NOT NULL AND source.[SHK_PredOrder_Link_rv] IS NULL)
				OR (target.[gi_id] <> source.[gi_id]) OR (target.[gi_id] IS NULL AND source.[gi_id] IS NOT NULL) OR (target.[gi_id] IS NOT NULL AND source.[gi_id] IS NULL)
				OR (target.[predorder_id] <> source.[predorder_id]) OR (target.[predorder_id] IS NULL AND source.[predorder_id] IS NOT NULL) OR (target.[predorder_id] IS NOT NULL AND source.[predorder_id] IS NULL)
				OR (target.[predorder_collection_id] <> source.[predorder_collection_id]) OR (target.[predorder_collection_id] IS NULL AND source.[predorder_collection_id] IS NOT NULL) OR (target.[predorder_collection_id] IS NOT NULL AND source.[predorder_collection_id] IS NULL)
				OR (target.[predorderdetailsize_id] <> source.[predorderdetailsize_id]) OR (target.[predorderdetailsize_id] IS NULL AND source.[predorderdetailsize_id] IS NOT NULL) OR (target.[predorderdetailsize_id] IS NOT NULL AND source.[predorderdetailsize_id] IS NULL)
				OR (target.[predorderdetailsa_id] <> source.[predorderdetailsa_id]) OR (target.[predorderdetailsa_id] IS NULL AND source.[predorderdetailsa_id] IS NOT NULL) OR (target.[predorderdetailsa_id] IS NOT NULL AND source.[predorderdetailsa_id] IS NULL)
			)
21 апр 16, 13:52    [19088246]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Владислав Колосов
a_voronin,

на самом деле это выражение приводится к

(A <> B) and NOT (a is null and b is null).


К сожалению практика опровергает вашу теорему и ниже тоже

SELECT A, B FROM 
(
	VALUES 
		(1, 1),
		(1, 2),
		(1, NULL),
		(NULL, 1),
		(NULL, NULL)
) V (A, B)
WHERE (A <> B) OR (A IS NULL AND B IS NOT NULL) OR (A IS NOT NULL AND B IS NULL)

SELECT A, B FROM 
(
	VALUES 
		(1, 1),
		(1, 2),
		(1, NULL),
		(NULL, 1),
		(NULL, NULL)
) V (A, B)
WHERE (A <> B) and NOT (a is null and b is null)

SELECT A, B FROM 
(
	VALUES 
		(1, 1),
		(1, 2),
		(1, NULL),
		(NULL, 1),
		(NULL, NULL)
) V (A, B)
WHERE not (a=b or (a is null and b is null))
21 апр 16, 13:56    [19088285]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Гавриленко Сергей Алексеевич
А в плане не видно, что и так выкидывает?


да видно
21 апр 16, 13:57    [19088298]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
a_voronin
iljy,

Потому что стоит это дело в MERGE запросе и выглядит вот так
			WHEN MATCHED AND (
				target.DELETED <> source.DELETED
				OR (target.[shk_id] <> source.[shk_id]) OR (target.[shk_id] IS NULL AND source.[shk_id] IS NOT NULL) OR (target.[shk_id] IS NOT NULL AND source.[shk_id] IS NULL)
				OR (target.[SHK_DeletedData_SHK_rv] <> source.[SHK_DeletedData_SHK_rv]) OR (target.[SHK_DeletedData_SHK_rv] IS NULL AND source.[SHK_DeletedData_SHK_rv] IS NOT NULL) OR (target.[SHK_DeletedData_SHK_rv] IS NOT NULL AND source.[SHK_DeletedData_SHK_rv] IS NULL)
				OR (target.[chrt_id] <> source.[chrt_id]) OR (target.[chrt_id] IS NULL AND source.[chrt_id] IS NOT NULL) OR (target.[chrt_id] IS NOT NULL AND source.[chrt_id] IS NULL)
				OR (target.[SHK_PredOrder_Link_rv] <> source.[SHK_PredOrder_Link_rv]) OR (target.[SHK_PredOrder_Link_rv] IS NULL AND source.[SHK_PredOrder_Link_rv] IS NOT NULL) OR (target.[SHK_PredOrder_Link_rv] IS NOT NULL AND source.[SHK_PredOrder_Link_rv] IS NULL)
				OR (target.[gi_id] <> source.[gi_id]) OR (target.[gi_id] IS NULL AND source.[gi_id] IS NOT NULL) OR (target.[gi_id] IS NOT NULL AND source.[gi_id] IS NULL)
				OR (target.[predorder_id] <> source.[predorder_id]) OR (target.[predorder_id] IS NULL AND source.[predorder_id] IS NOT NULL) OR (target.[predorder_id] IS NOT NULL AND source.[predorder_id] IS NULL)
				OR (target.[predorder_collection_id] <> source.[predorder_collection_id]) OR (target.[predorder_collection_id] IS NULL AND source.[predorder_collection_id] IS NOT NULL) OR (target.[predorder_collection_id] IS NOT NULL AND source.[predorder_collection_id] IS NULL)
				OR (target.[predorderdetailsize_id] <> source.[predorderdetailsize_id]) OR (target.[predorderdetailsize_id] IS NULL AND source.[predorderdetailsize_id] IS NOT NULL) OR (target.[predorderdetailsize_id] IS NOT NULL AND source.[predorderdetailsize_id] IS NULL)
				OR (target.[predorderdetailsa_id] <> source.[predorderdetailsa_id]) OR (target.[predorderdetailsa_id] IS NULL AND source.[predorderdetailsa_id] IS NOT NULL) OR (target.[predorderdetailsa_id] IS NOT NULL AND source.[predorderdetailsa_id] IS NULL)
			)
WHEN MATCHED AND NOT EXISTS
(
 SELECT
  target.DELETED
 ,target.[shk_id]
 ,target.[SHK_DeletedData_SHK_rv]
 ,target.[chrt_id]
 ,target.[SHK_PredOrder_Link_rv]
 ,target.[gi_id]
 ,target.[predorder_id]
 ,target.[predorder_collection_id]
 ,target.[predorderdetailsize_id]
 ,target.[predorderdetailsa_id]
 INTERSECT
 SELECT
  source.DELETED
 ,source.[shk_id]
 ,source.[SHK_DeletedData_SHK_rv]
 ,source.[chrt_id]
 ,source.[SHK_PredOrder_Link_rv]
 ,source.[gi_id]
 ,source.[predorder_id]
 ,source.[predorder_collection_id]
 ,source.[predorderdetailsize_id]
 ,source.[predorderdetailsa_id]
)
21 апр 16, 14:04    [19088376]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
iap
WHEN MATCHED AND NOT EXISTS
(
 SELECT
  target.DELETED
 ,target.[shk_id]
 ,target.[SHK_DeletedData_SHK_rv]
 ,target.[chrt_id]
 ,target.[SHK_PredOrder_Link_rv]
 ,target.[gi_id]
 ,target.[predorder_id]
 ,target.[predorder_collection_id]
 ,target.[predorderdetailsize_id]
 ,target.[predorderdetailsa_id]
 INTERSECT
 SELECT
  source.DELETED
 ,source.[shk_id]
 ,source.[SHK_DeletedData_SHK_rv]
 ,source.[chrt_id]
 ,source.[SHK_PredOrder_Link_rv]
 ,source.[gi_id]
 ,source.[predorder_id]
 ,source.[predorder_collection_id]
 ,source.[predorderdetailsize_id]
 ,source.[predorderdetailsa_id]
)


Я обеими руками за компактность записи, но INTERSECT жрет процессора больше и работает дольше

CREATE TABLE #T 
(
	A INT NOT NULL, 
	B INT NOT NULL
) 

INSERT INTO #T (A,B)
SELECT TOP 50000000
	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
	ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM spt_values X, spt_values Y, spt_values Z


SELECT * FROM #T
WHERE (A <> B) OR (A IS NULL AND B IS NOT NULL) OR (A IS NOT NULL AND B IS NULL)

SELECT * FROM #T
WHERE NOT EXISTS (SELECT A INTERSECT SELECT B) 

DROP TABLE #T
21 апр 16, 17:24    [19089824]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
iljy
Member

Откуда:
Сообщений: 8711
a_voronin
Я обеими руками за компактность записи, но INTERSECT жрет процессора больше и работает дольше


вот я как раз об этом и говорил: если убрать not, то ничего от не жрет, а вот присутствие not почему-то сервер сбивает.
21 апр 16, 17:43    [19089930]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
iljy
a_voronin
Я обеими руками за компактность записи, но INTERSECT жрет процессора больше и работает дольше


вот я как раз об этом и говорил: если убрать not, то ничего от не жрет, а вот присутствие not почему-то сервер сбивает.


Ещё хуже


CREATE TABLE #T 
(
	A INT NOT NULL, 
	B INT NOT NULL
) 

INSERT INTO #T (A,B)
SELECT TOP 50000000
	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
	ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM spt_values X, spt_values Y, spt_values Z


SELECT * FROM #T
WHERE (A <> B) OR (A IS NULL AND B IS NOT NULL) OR (A IS NOT NULL AND B IS NULL)

SELECT * FROM #T
WHERE EXISTS (SELECT A EXCEPT SELECT B) 

DROP TABLE #T
21 апр 16, 17:47    [19089951]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация IS NULL  [new]
iljy
Member

Откуда:
Сообщений: 8711
a_voronin,

я всю дорогу говорил о форме

WHERE EXISTS (SELECT A INTERSECT SELECT B) .

я знаю, что она решает обратную задачу, я просто привел ее как пример удобной записи, которую не получается применить к проверке на неравенство из-за каких-то внутренних заморочек сервера.
21 апр 16, 18:37    [19090181]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить