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

Откуда:
Сообщений: 28
Доброе утро.

Вот такой запрос:
declare @var int = null

update Detail
set Designation = 'xxx'
where ID = @var


Можно ли заставить SQL Server ничего не делать (не сканировать таблицу или искать индексы), если переменная @var является NULL. Поле ID кластеризованный индекс не допускающий NULL

Знаю что можно так сделать, если поставить внешнее условие IF. Но можно ли в самом запросе такое провернуть? Спрашиваю из любопытства.

Т.е. условно говоря, сервер посмотрел на значение переменной, затем на тип столбца в искомом поле, узнал что поле не допускает NULL и пропускал запрос

К сообщению приложен файл. Размер - 14Kb
7 июл 18, 09:41    [21551436]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
aleks222
Member

Откуда:
Сообщений: 852
where ... and @var is not null


Впрочем, оптимизатор не настолько идиотичен - никакого scan и seek не будет.
7 июл 18, 10:36    [21551557]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
aleks222
Member

Откуда:
Сообщений: 852
План, который тебе нарисовали - это предварительный сферический план в вакууме.
7 июл 18, 10:38    [21551558]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
aleks222
where ... and @var is not null



Впрочем, оптимизатор не настолько идиотичен - никакого scan и seek не будет.
ID=@var
и так означает
@var IS NOT NULL
7 июл 18, 11:35    [21551678]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
VicN
Member

Откуда:
Сообщений: 28
aleks222,
автор
Впрочем, оптимизатор не настолько идиотичен - никакого scan и seek не будет.

разве? Я ведь включил действительный план запроса. Выполнил запрос и он показывает такой вот план.

Вообще смущает не только, что сервер делает поиск, но и какое то обновление кластерного индекса. Ведь я индекс совсем не трогаю. Зачем все это?
7 июл 18, 11:49    [21551707]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
VicN
Вообще смущает не только, что сервер делает поиск, но и какое то обновление кластерного индекса. Ведь я индекс совсем не трогаю. Зачем все это?
Почитайте что такое кластерный индекс, тогда поймете зачем "какое то обновление кластерного индекса".

По вашим данным можно только сказать, что на таблицу, помимо ПК, наложены еще ограничения.
Если хотите более подробного анализа, покажите полное определение Detail и актуальный план в формате sqlplan.
7 июл 18, 12:43    [21551814]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36694
Покажите план в нормальном виде. По фотографиям гадать долго будет.
7 июл 18, 12:45    [21551816]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
VicN
Member

Откуда:
Сообщений: 28
Таблица
+
USE [Production]
GO

/****** Object:  Table [dbo].[Detail]    Script Date: 07.07.2018 14:33:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Detail](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Designation] [varchar](50) NULL,
	[Name] [varchar](150) NULL,
	[SpecSectionID] [tinyint] NULL,
	[UnitID] [tinyint] NULL,
	[StatusEM] [varchar](50) NULL,
	[DateEM] [datetime] NULL,
	[StatusTD] [varchar](50) NULL,
	[DateTD] [datetime] NULL,
	[FIO] [int] NULL,
	[DateRecord] [datetime] NULL,
	[ClStandProdID] [tinyint] NULL,
	[DocFormatID] [tinyint] NULL,
 CONSTRAINT [pk_Detail] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [uq_DesignationName] UNIQUE NONCLUSTERED 
(
	[Designation] ASC,
	[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Detail] ADD  CONSTRAINT [df_DetailDateRecord]  DEFAULT (sysdatetime()) FOR [DateRecord]
GO

ALTER TABLE [dbo].[Detail]  WITH CHECK ADD  CONSTRAINT [fk_Detail_ClassStandartProduct] FOREIGN KEY([ClStandProdID])
REFERENCES [dbo].[ClassStandartProduct] ([ID])
GO

ALTER TABLE [dbo].[Detail] CHECK CONSTRAINT [fk_Detail_ClassStandartProduct]
GO

ALTER TABLE [dbo].[Detail]  WITH CHECK ADD  CONSTRAINT [fk_Detail_DocumentFormat] FOREIGN KEY([DocFormatID])
REFERENCES [dbo].[DocumentFormat] ([ID])
GO

ALTER TABLE [dbo].[Detail] CHECK CONSTRAINT [fk_Detail_DocumentFormat]
GO

ALTER TABLE [dbo].[Detail]  WITH CHECK ADD  CONSTRAINT [fk_Detail_SpecSection] FOREIGN KEY([SpecSectionID])
REFERENCES [dbo].[SpecificationSection] ([ID])
GO

ALTER TABLE [dbo].[Detail] CHECK CONSTRAINT [fk_Detail_SpecSection]
GO

ALTER TABLE [dbo].[Detail]  WITH CHECK ADD  CONSTRAINT [fk_Detail_Unit] FOREIGN KEY([UnitID])
REFERENCES [dbo].[Unit] ([ID])
GO

ALTER TABLE [dbo].[Detail] CHECK CONSTRAINT [fk_Detail_Unit]
GO


К сообщению приложен файл (ExecutionPlan1.sqlplan - 24Kb) cкачать
7 июл 18, 14:35    [21551980]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
invm
Member

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

Такая форма плана потому, что есть у таблицы фильтрованный индекс ix_Designation, который зависит от столбца Designation.
План отличается от первоначально показанного. Куда дели ограничение, зависящее от Designation?
7 июл 18, 15:15    [21552062]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
VicN
Member

Откуда:
Сообщений: 28
invm
VicN,

Такая форма плана потому, что есть у таблицы фильтрованный индекс ix_Designation, который зависит от столбца Designation.
План отличается от первоначально показанного. Куда дели ограничение, зависящее от Designation?

Там было проверочное ограничение check на поля Designation и Name. Удалил, т.к. это ограничение старое и не актуально на данный момент для разрабатываемой мной базы.

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

Видимо так не получится. Тему можно закрывать
7 июл 18, 15:56    [21552148]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
VicN
По моему обсуждение пошло в другую сторону.
Изначально я хотел выяснить, есть ли возможность не вынося во внешнее условие IF, сделать проверку переменной на значение NULL и не дергать таблицу и индексы как в плане, если переменная действительно является NULL.
По-моему, вы сами поинтересовались почему и зачем такой план.
Если желаете проверять значение переменной, то перепишите условие:
where @var is not null and ID = @var
Тогда в плане, перед доступом к таблице (Clustered Index Seek), появится соответствующий фильтр.
7 июл 18, 16:06    [21552171]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
VicN,

Добрый день.

В самом запросе можно «провернуть», как уже выше сказали, добавив дополнительно условие «and @var is not null». Это только для помощи оптимизатору, т.к. логически, в данном случае, это одно и то же. Но дополнительный предикат, дает серверу возможность использовать Startup Filter. Это такой вид фильтра, в котором проверяется некоторое выражение (Startup Expression Predicate) и если оно не выполняется, то вся нижележащая/дочерняя ветка плпнп не будет выполняться.

Добавьте в запросе в условие where «and @var is not null», и посмотрите в свойствах появившегося оператора фильтра Startup Expression Predicate: [@var] IS NOT NULL. Далее сравните в вашем плане и в получившемся свойство Number of Executions оператора Clustered Index Seek, в первом оно 1, т.е. оператор вызывался, во втором 0, т.е. не вызывался.

Хотя на уровне Storage Engine и то, и то не осуществляет доступ к строкам, включите «statistics io», и посмотрите число логических чтений, либо на более поздних версиях, можно посмотреть свойство самого оператора «Actual IO Statistics» и «Number of Rows Read» (которого не будет ни в том, ни в другом случае), можно также посмотреть sys.dm_db_index_operational_stats, чтобы убедиться, что никакого физического доступа нет.

В этом смысле сервер не дурак, так что не переживайте, что: «смущает не только, что сервер делает поиск» - он не делает в данном случае (если только вы не отключите ANSI_NULLS и не сделаете колонку nullable, исключив из РК, если так, то разница в физическом доступе будет, и вы это увидите по всем признакам, перечисленным выше).

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

Далее, по вопросу: «какое то обновление кластерного индекса. Ведь я индекс совсем не трогаю. Зачем все это?»

Операция обновления в SQL Server всегда состоит из двух частей - Read Cursor (не тот, не пользовательский курсор, а внутри сиквела, цикл по строкам), и Write Cursor. Один набирает строки для модификации (например, Clustered Index Seek), другой модифицирует (например, Clustered Index Update). В некоторых простых случаях, сервер, умеет это сворачивать в один оператор:
use tempdb;
go
create table t(a int primary key);
insert t values (1);
update t set a=1 where a=1;
delete t where a=1;
go
drop table t;

Планы имеют, кроме корневого элемента (INSERT/UPDATE/DELETE), всего по одному оператору плана, который совмещает Write + Read курсоры.
Картинка с другого сайта.
Это работает для самых простых случаев.

И даже для них, можно отключить эту оптимизацию недокументированным флагом 8758.
use tempdb;
go
create table t(a int primary key);
dbcc traceon (8758);
go
insert t values (1);
update t set a=1 where a=1;
delete t where a=1;
go
dbcc traceoff (8758);
go
drop table t;

Тогда планы такие, какие должны быть без дополнительной оптимизации.
Картинка с другого сайта.

Вывод из этого такой:
более-менее сложные запросы обновления должны так или иначе содержать в себе часть Read (которая не обязательно получает доступ к строкам, это может быть просто Constant Scan) и часть Write – такова особенность внутренней реализации сиквела.
Так что ваш план содержит Update не потому, что он реально что-то обновляет если NULL, а by design.

Это обусловлено тем, что несмотря на то, сколько строк затрагивает запрос, сервер должен еще корректно отрабатывать блокировки. Попробуйте в двух окнах, в первом:
begin tran;
update Detail with(tablockx) set Designation = 'xxx' where 1=0;
--rollback tran

Во втором:
update Detail set Designation = 'xxx' where 1=0

Несмотря на то, что оба запроса не затрагивают никаких строк по условию 1=0, все равно второй будет заблокирован и будет ждать, пока вы не закончите транзакцию первого. А т.к. итераторы выполнения (не только update, но и все остальные) должны корректно работать с блокировками, логика запроса не единственное, что обуславливает их физический дизайн.

Подытоживая,
- В данном примере лишнего физического доступа не будет, если он реально не нужен.
- Форма плана и наличие тех или иных оператор обусловлена особенностями физической реализации, которые должны учитывать много других аспектов работы, кроме логической составляющей запроса.
7 июл 18, 19:33    [21552427]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
VicN
Member

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

Спасибо за развернутый ответ, буду по немного разбираться и обдумывать.

Остальным также спасибо за участие в обсуждении
7 июл 18, 21:55    [21552644]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить SQL Server ничего не делать, если переменная NULL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
По-моему ни один такой трюк не пройдет, если включена принудительная параметризация. Это надо иметь в виду.
7 июл 18, 22:48    [21552738]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить