Заметки Дмитрия Пилюгина о Microsoft SQL Server 

Twitter RSS
Home SQL Server (все заметки) Как отключение FK на момент загрузки, может повлиять на план запроса после загрузки
formats

Как отключение FK на момент загрузки, может повлиять на план запроса после загрузки

Если в таблице создано ограничение внешнего ключа (Foreign Key Constraint), то при добвлении данных в эту таблицу будет проверяться наличие/отсутствие соответствующих записей в «родительской» таблице.
Иногда, если загрузка данных идет из, условно говоря, доверенного источника (имеется ввиду нам известно, что все данные из источника отвечают требованиям ссылочной целостности), для ускорения загрузки ограничения внешних ключей отключают, а после загрузки — включают обратно.
Смоделируем такую ситуацию и посмотрим, что будет.

use tempdb
go
-- создадим тестовые таблички
create table dbo.ATable ( ATableID int not null primary key )
create table dbo.BTable (
	BTableID int not null primary key,
	ATableID int not null,
	constraint FK_ATable_ATableID foreign key(ATableID) references dbo.ATable(ATableID)
)
go
-- заполним данными
insert into dbo.ATable(ATableID) select 1
insert into dbo.BTable(BTableID, ATableID) select 1,1
go
-- выполним запрос с просмотром плана выполнения
set showplan_xml on
go
select
	count(*)
from
	dbo.BTable b
where
	exists (select * from dbo.ATable a where a.ATableID = b.ATableID)
go
set showplan_xml off
go

Видим, что за счет ограничения внешнего ключа и того, что колонка BTable.ATableID не может быть null, оптимизатор полностью исключил из плана обращение к таблице ATable.
Теперь отключаем ограничение, эмулируем загрузку и включаем ограничение.

-- отключаем
alter table dbo.BTable nocheck constraint FK_ATable_ATableID
go
-- загружаем
insert into dbo.BTable(BTableID, ATableID) select 2,1
go
-- включаем
alter table dbo.BTable check constraint FK_ATable_ATableID
go
--Выполним тот же самый запрос и посмотрим план
set showplan_xml on
go
select
	count(*)
from
	dbo.BTable b
where
	exists (select * from dbo.ATable a where a.ATableID = b.ATableID)
go
set showplan_xml off
go

Что мы видим, так это то, что план изменился, теперь 50% ресурсов занимает скан таблицы ATable.
Почему так произошло, ведь мы вроде вернули систему в исходное состояние до загрузки. На самом деле не совсем так. О том, что источник был «доверенный» знали только мы, ну а сервер нам не доверяет и правильно делает. =)
Как именно он нам не доверяет, можно посмотреть сделав такой запрос.

select is_not_trusted from sys.foreign_keys where name = 'FK_ATable_ATableID'

В момент первого выполнения запроса этот признак был равен 0.

Дело в том, что после загрузки данных, при включении ограничения у сервера нет уверенности в том, что в таблицу не попали записи, не имеющие соответствующих записей в родительской таблице.
«Убедить» сервер в обратном можно заставив его принудительно проверить данные в таблице. Для этого при включении ограничения нужно указать опцию with check.

alter table dbo.BTable with check check constraint FK_ATable_ATableID
go
set showplan_xml on
go
select
	count(*)
from
	dbo.BTable b
where
	exists (select * from dbo.ATable a where a.ATableID = b.ATableID)
go
set showplan_xml off
go

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

-- Удаляем тестовые таблички.
drop table BTable
drop table ATable
go

 

 

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Анти-спам: введите результат (цифрами) *