Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Foreign keys  [new]
Meriguan
Member

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

Может кто занет, может где-то видел статью.

Есть несколько таблиц в которых нужно периодически чистить данные. Таблицы связаные по FK. Удалять данные с таблиц которые связанные по FK, MS SQL тратит время на проверку целосности, как результат удаление данных происходит долго.
По этому есть два варианта очистки данных:
1. Изменить все внешние ключи на Cascade Delete, затем удалять данные
2. Удалять все внешние ключи перед удалением данных, удалять данные, затем вность создавать FK.
Какой вариант дешевле и быстрее?
7 авг 12, 13:03    [12974641]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
trew
Member

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

Влияние внешних ключей на delete

Может Вам вообще нужен TRUNCATE TABLE? Всё зависит от того, что хотите получить.
7 авг 12, 13:42    [12975034]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Meriguan
Member

Откуда:
Сообщений: 124
trew
Meriguan,

Влияние внешних ключей на delete

Может Вам вообще нужен TRUNCATE TABLE? Всё зависит от того, что хотите получить.


Спасибо, статья полезная, но все равно SQL делает проверку данных по ключу перед тем как удалить данные. Учитывая объемы данных которые будут удаляться SQL будет всегда у меня выбирать Scan.

Truncate table - это очень хоршо, но данные надо удалять которые старше 2-х недель.
7 авг 12, 13:53    [12975152]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Glory
Member

Откуда:
Сообщений: 104760
Meriguan
но все равно SQL делает проверку данных по ключу перед тем как удалить данные.

Ну и сколько займет проверка пустой дочерней таблицы ? Даже Scan-ом
7 авг 12, 13:57    [12975182]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
Meriguan
По этому есть два варианта очистки данных:
1. Изменить все внешние ключи на Cascade Delete, затем удалять данные
2. Удалять все внешние ключи перед удалением данных, удалять данные, затем вность создавать FK.
Какой вариант дешевле и быстрее?
Третий вариант - сделать индекс на поле, которое ФК
7 авг 12, 14:07    [12975256]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Meriguan
Member

Откуда:
Сообщений: 124
Glory
Meriguan
но все равно SQL делает проверку данных по ключу перед тем как удалить данные.

Ну и сколько займет проверка пустой дочерней таблицы ? Даже Scan-ом


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

К тому же, если кол-во строк удаляемых из дочерней таблицы относительно не большое, не будет обновлена статистика и при удалении данных из родительской таблицы SQL будет считать, что данные из дочерней таблице есть и будет делать поиск данных.
7 авг 12, 14:15    [12975319]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Meriguan
Member

Откуда:
Сообщений: 124
alexeyvg
Meriguan
По этому есть два варианта очистки данных:
1. Изменить все внешние ключи на Cascade Delete, затем удалять данные
2. Удалять все внешние ключи перед удалением данных, удалять данные, затем вность создавать FK.
Какой вариант дешевле и быстрее?
Третий вариант - сделать индекс на поле, которое ФК


Я думаю если сравнивать 2-й и 3-й вариант, то второй будет гораздо быстрее.
7 авг 12, 14:19    [12975342]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Meriguan
alexeyvg
пропущено...
Третий вариант - сделать индекс на поле, которое ФК

Я думаю если сравнивать 2-й и 3-й вариант, то второй будет гораздо быстрее.
Второй вариант - гарантированная недоступность всех затронутых таблиц на время операции. А создание FK - гарантированный скан подчиненных (а то и вместе с главными) таблиц на предмет выявления записей, ему не удовлетворяющих. Даже если таковых нет.
7 авг 12, 14:24    [12975382]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
Meriguan
Я думаю если сравнивать 2-й и 3-й вариант, то второй будет гораздо быстрее.
Конечно. Всё зависит от требований.
Для удалений небольшими порциями предпочтительнее мой вариант, для удалений помногу быстрее удалять ФК.
7 авг 12, 14:28    [12975423]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
alexeyvg
Для удалений небольшими порциями предпочтительнее мой вариант, для удалений помногу быстрее удалять ФК.
Как я понимаю граница проходит на половине данных (если не учитывать доступность).

Гавриленко Сергей Алексеевич
гарантированная недоступность всех затронутых таблиц на время операции.
А есть ли адекватная причина почему нет ONLINE для CONSTRAINT?
7 авг 12, 14:59    [12975712]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Mnior
А есть ли адекватная причина почему нет ONLINE для CONSTRAINT?
Адекватная причина, походу, одна - никто не продавил. Есть ли объективная причина, зачем при чеке констрейнтов держать Sch-M на всех объектах, докуда серверер может дотянуться, я не знаю.
7 авг 12, 15:38    [12976010]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Стойте, стойте, а как на счёт CHECK CONSTRAINT? Так тоже не катит, так?
Хотя он не затрагивает [NOT] NULL для полей.
7 авг 12, 16:36    [12976407]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Гавриленко Сергей Алексеевич
Адекватная причина, походу, одна - никто не продавил
Судя по Connection - даже не пытались.
Значит стоит попробовать. Стоит начать пока с CHECK CONSTRAINT?
USE tempdb
GO
CREATE TABLE [dbo].[Test] (
	ID	Int	NOT NULL IDENTITY
)
ALTER TABLE [dbo].[Test] WITH NOCHECK ADD CONSTRAINT [CK_ID] CHECK ([ID] > 0)
GO
ALTER TABLE [dbo].[Test] CHECK CONSTRAINT ALL WITH (ONLINE = ON)
GO
7 авг 12, 17:21    [12976681]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
UP
8 авг 12, 13:16    [12981181]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Чё за безучастие?
Мужики, вам чё, всё пофигу и ничего не надо?
9 авг 12, 10:23    [12985871]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mnior
Чё за безучастие?
Мужики, вам чё, всё пофигу и ничего не надо?

Надо. Но конкретно в этом пока необходимости не было.
9 авг 12, 10:53    [12986144]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Ну голосовать то будете?
9 авг 12, 11:52    [12986574]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Mnior
Ну голосовать то будете?
Да. Мы, в принципе, ни чеками ни ФК особо не пользуемся, а единственное, над чем мы мучались -- добавление not null полей к большим таблицам -- сделали нормальным в 2012.
9 авг 12, 11:56    [12986615]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3264
Гавриленко Сергей Алексеевич
Mnior
Ну голосовать то будете?
Да. Мы, в принципе, ни чеками ни ФК особо не пользуемся, а единственное, над чем мы мучались -- добавление not null полей к большим таблицам -- сделали нормальным в 2012.

А целостность данных триггерами поддерживаете ?

В некоторых случаях на триггерах оно быстрее выходит, кроме того многие ORM требуют наличие PK и FK для генерации , это так заметки на полях.
9 авг 12, 12:02    [12986666]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Ken@t
А целостность данных триггерами поддерживаете?
Нет, серверным кодом.
9 авг 12, 12:13    [12986752]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Гавриленко Сергей Алексеевич
единственное, над чем мы мучались -- добавление not null полей к большим таблицам -- сделали нормальным в 2012.
О, [NOT] NULL важнее, и кое что они сделали в 2012.
А случаем с чеками они может тоже что-то поменяли в 2012м?
9 авг 12, 13:44    [12987625]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Mnior
Гавриленко Сергей Алексеевич
единственное, над чем мы мучались -- добавление not null полей к большим таблицам -- сделали нормальным в 2012.
О, [NOT] NULL важнее, и кое что они сделали в 2012.
А случаем с чеками они может тоже что-то поменяли в 2012м?
Не. Только позавчера досконально читал what's new по Database Engine. Разве что сделали, но сказать об этом забыли, но это маловероятно.

З.Ы. Знаю, что not null полями сделали "быстрое" (без модификации данных) добавление поля при добавлении его с дефолтом. Работает ли такой фокус при переводе уже существующего поля null в not null - надо проверять.
9 авг 12, 13:53    [12987704]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Гавриленко Сергей Алексеевич
Работает ли такой фокус при переводе уже существующего поля null в not null - надо проверять.
Судя по логике - нет. Он просто считает NULL как Default.
Возможно даже в планет быть что-то типа:
Column = IsNull(Column,Default)
Хотя маловероятно.

Ок, коль нет в доках, значит есть смысл, если чё этим отмажемся.
9 авг 12, 18:29    [12989878]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Я тут ошибся:
ALTER TABLE [dbo].[Test] WITH CHECK CHECK CONSTRAINT ALL
WITH (ONLINE = ON)
9 авг 12, 21:07    [12990342]     Ответить | Цитировать Сообщить модератору
 Re: Foreign keys  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
Mnior
Я тут ошибся:
ALTER TABLE [dbo].[Test] WITH CHECK CHECK CONSTRAINT ALL
WITH (ONLINE = ON)


где фидбэк-то? я плюсану
10 авг 12, 07:46    [12991292]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить