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

Откуда:
Сообщений: 818
Кака в чем, я пришел, когда проекту было 14 лет, он был написан через жопу с кучей курсоров и отсутствием внешних ключей, а тема оптимизации запросов вызывала ухмылку. Так вот, по немногу начал ломать систему и ныть по поводу курсоров, отсутствию ключей и тд. Но ща образовалась жопа, есть таблица (Clients), с относительно небольшим количеством записей (400к) при ,примерно,100-150 полях, там всего 8 внешних ключей. При этом на саму таблицу ссылаются еще 50 таблиц. Ерунда в том, что сейчас удаление вновь созданной записи занимает минуту и 50 секунд. И ща весь отдел по немногу начинает опять забивать на внешние ключи, ссылаясь на эту ситуацию. У меня есть только 1 вариант удаления при таком раскладе, это собирать все таблицы, которые зависят от Clients проверять есть ли там удаляемая запись через exists, и если нет, отключать ограничения, удалять и включать. Но это, как по мне, тупой костыль. Мы даже пробовали отключать триггер, но результат тот же.
Что можно сделать в этой ситуации?
ПС
ms sql 2016 enterprise
11 окт 19, 11:44    [21991979]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Обычно такое бывает, если на связанной таблице отсутствует индекс, поэтому, для поиска значений при удалении используется скан.

Я бы посмотрел на план удаления строки - кто именно дает медленное выполнение, и сделал соответствующий индекс.
11 окт 19, 11:53    [21991991]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
invm
Member

Откуда: Москва
Сообщений: 9273
ther
При этом на саму таблицу ссылаются еще 50 таблиц
А у этих таблиц индексы по ссылке есть?
11 окт 19, 11:59    [21992005]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
ther
Member

Откуда:
Сообщений: 818
У каждой таблицы кластерный индекс, но вряд ли кто то создавал еще индекс на внешний ключ. План во вложении

К сообщению приложен файл (DeletionPlan.rar - 129Kb) cкачать
11 окт 19, 12:20    [21992031]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
ther
Member

Откуда:
Сообщений: 818
Оказалось, что 147 таблиц ссылаются на Client
11 окт 19, 12:21    [21992034]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
invm
Member

Откуда: Москва
Сообщений: 9273
ther
но вряд ли кто то создавал еще индекс на внешний ключ
ther
Оказалось, что 147 таблиц ссылаются на Client
Вот и получили полные сканы этих таблиц при удалении строки из родительской.

Вдобавок еще удаляются строки из индексированных представлений.
11 окт 19, 12:53    [21992069]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
ther
Member

Откуда:
Сообщений: 818
Если с индексами понятно, хотя стремно ща делать 147 индексов, как бы место не закончилось на винте.
То как быть с удалением при наличии индексируемой вьюхи?
11 окт 19, 13:10    [21992084]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
архивариус
Member

Откуда:
Сообщений: 150
ther
Если с индексами понятно, хотя стремно ща делать 147 индексов, как бы место не закончилось на винте.
То как быть с удалением при наличии индексируемой вьюхи?

может для начала посмотреть на 4 которые рекомендует Query Processor, а не 147 :
/*
Missing Index Details 
The Query Processor estimates that implementing the following index could improve the query cost by 12,7321%.

WARNING: This is only an estimate, and the Query Processor is making this recommendation based solely upon analysis
of this specific query. It has not considered the resulting index size, or its workload-wide impact, including its
impact on INSERT, UPDATE, DELETE performance. These factors should be taken into account before creating this index.
*/
USE [Electro_Temp]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[OpDay] ([OD_SClient],)
GO

----------------

/*
Missing Index Details
The Query Processor estimates that implementing the following index could improve the query cost by 12,7354%.

WARNING: This is only an estimate, and the Query Processor is making this recommendation based solely upon analysis
of this specific query. It has not considered the resulting index size, or its workload-wide impact, including its
impact on INSERT, UPDATE, DELETE performance. These factors should be taken into account before creating this index.
*/
USE [Electro_Temp]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[OpDay] ([OD_Client],)
GO

----------------

/*
Missing Index Details
The Query Processor estimates that implementing the following index could improve the query cost by 10,7184%.

WARNING: This is only an estimate, and the Query Processor is making this recommendation based solely upon analysis
of this specific query. It has not considered the resulting index size, or its workload-wide impact, including its
impact on INSERT, UPDATE, DELETE performance. These factors should be taken into account before creating this index.
*/
USE [Electro_Temp]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Movement] ([Mov_Client],)
GO

----------------

/*
Missing Index Details
The Query Processor estimates that implementing the following index could improve the query cost by 57,5961%.

WARNING: This is only an estimate, and the Query Processor is making this recommendation based solely upon analysis
of this specific query. It has not considered the resulting index size, or its workload-wide impact, including its
impact on INSERT, UPDATE, DELETE performance. These factors should be taken into account before creating this index.
*/
USE [Electro_Temp]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[DocOut] ([DO_Client],)
GO
11 окт 19, 13:17    [21992095]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
ther
Если с индексами понятно, хотя стремно ща делать 147 индексов, как бы место не закончилось на винте.
То как быть с удалением при наличии индексируемой вьюхи?

Если она индексируемая, то логично, что и индекс на ней тоже можно сделать, не?
11 окт 19, 13:17    [21992096]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
StarikNavy
Member

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

добавить поле "удалено" , и при удаление не удалять а ставить признак в это поле.
в клиенте таких не отражать
удалять (с этим признаком) потихоньку ночами
11 окт 19, 13:29    [21992112]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
PaulWist
Member

Откуда:
Сообщений: 2227
ther
Если с индексами понятно, хотя стремно ща делать 147 индексов, как бы место не закончилось на винте.
То как быть с удалением при наличии индексируемой вьюхи?


1. Не всё так плохо, на паре таблиц есть индексы на внешний ключ, осталось только на 145 проиндексировать.

2. Есть ещё 3 таблицы-кучи.
11 окт 19, 13:37    [21992118]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
ther
Member

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

это понятно, я думал, что надо будет доп манипуляции с вьюхой, кроме индекса

архивариус
пасиб, пробую
11 окт 19, 14:06    [21992151]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33504
Блог
ther,

Вам эти 150 таблиц не нужны, сначала гляньте на те немногие из них, что обеспечиваю основную часть задержек. Ну и пользовательское удаление записей из справочника само по себе выглядит довольно дико.
11 окт 19, 19:03    [21992495]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
982183
Member

Откуда: VL
Сообщений: 3351
"удаление вновь созданной записи "
1.Я правильно понимаю, что подразумевается полное отсутствие указаний на данную запись в других таблицах.
или всё же есть вероятность появления связанных записей?
2. Зачем делать физическое удаление? Пометьте запись как "для удаления" и обработайте процедуру физического удаления в любое удобное время.
12 окт 19, 09:43    [21992664]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31328
982183
и обработайте процедуру физического удаления в любое удобное время.
Так всё равно будет расход ресурсов на скан этих 170 таблиц.
Разумнее сделать индексы на тех таблицах, где много записей.
Или, действительно, подумать о ненужности физического удаления (в т.ч. через вьюху, если нельзя поменять программу).
12 окт 19, 11:27    [21992691]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
982183
Member

Откуда: VL
Сообщений: 3351
Ну так в "пакетном" виде всё это можно запустить гораздо оптимальней.
Сформировать список ID для удаления, разделить его на те, на которые есть ссылки и те по которым нет.
первые на анализ,
вторые на физическое удаление.
12 окт 19, 12:06    [21992706]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31328
982183
Ну так в "пакетном" виде всё это можно запустить гораздо оптимальней.
Сформировать список ID для удаления, разделить его на те, на которые есть ссылки и те по которым нет.
первые на анализ,
вторые на физическое удаление.
Да, это само собой.
И вообще, можно же запускать физическое удаление редко.
Если создавать инедксы не хочется, ибо интенсивные вставки, и будет оверхед, то вполне себе решение.
12 окт 19, 12:21    [21992709]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
uaggster
Member

Откуда:
Сообщений: 809
А у вас еще и декларативная ссылочная целостность, небось, включена?
У каждой декларативной связи...
14 окт 19, 08:38    [21993376]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31328
uaggster
А у вас еще и декларативная ссылочная целостность, небось, включена?
У каждой декларативной связи...
Да, это и есть вопрос топика.
14 окт 19, 08:52    [21993384]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
uaggster
Member

Откуда:
Сообщений: 809
Имелось ввиду - поддержание декларативной ссылочной целостности - ака каскадное обновление/удаление /заNULLение дочерних записей.

Сама по себе бекларативная ссылочная целостность - не так страшна. А вот каскадные операции...
14 окт 19, 12:23    [21993586]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
uaggster
Имелось ввиду - поддержание декларативной ссылочной целостности - ака каскадное обновление/удаление /заNULLение дочерних записей.

Сама по себе бекларативная ссылочная целостность - не так страшна. А вот каскадные операции...

что с каскадом, что без, если "147 таблиц ссылаются на Client"
и индексов нет, 147 сканов будут все равно
14 окт 19, 13:14    [21993663]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
PaulWist
Member

Откуда:
Сообщений: 2227
Yasha123
uaggster
Имелось ввиду - поддержание декларативной ссылочной целостности - ака каскадное обновление/удаление /заNULLение дочерних записей.

Сама по себе бекларативная ссылочная целостность - не так страшна. А вот каскадные операции...

что с каскадом, что без, если "147 таблиц ссылаются на Client"
и индексов нет, 147 сканов будут все равно


+100500

Индексов нужно значительно меньше, из этих 147 таблиц почти все маленькие.

Критик дал рецепт:

Критик
Вам эти 150 таблиц не нужны, сначала гляньте на те немногие из них, что обеспечиваю основную часть задержек.
14 окт 19, 13:59    [21993714]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
982183
Member

Откуда: VL
Сообщений: 3351
alexeyvg
uaggster
А у вас еще и декларативная ссылочная целостность, небось, включена?
У каждой декларативной связи...
Да, это и есть вопрос топика.

А я так понял, что он сам проверяет наличие связанных записей в зависимых таблицах.
14 окт 19, 14:07    [21993724]     Ответить | Цитировать Сообщить модератору
 Re: Внимание боян. Долгое удаление записи из таблицы.  [new]
uaggster
Member

Откуда:
Сообщений: 809
Yasha123
uaggster
Имелось ввиду - поддержание декларативной ссылочной целостности - ака каскадное обновление/удаление /заNULLение дочерних записей.

Сама по себе бекларативная ссылочная целостность - не так страшна. А вот каскадные операции...

что с каскадом, что без, если "147 таблиц ссылаются на Client"
и индексов нет, 147 сканов будут все равно

Будут. Но с блокировками - попроще будет.
14 окт 19, 14:30    [21993752]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить