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

Откуда:
Сообщений: 112
Используется MSSQL2005, есть некая таблица Document, которая содержит много записей (около 2 млн), из нее нужно удалить часть записей, оставить только те записи которые принадлежат указанному клиенту.
Вначале создается временная таблица #tempDocument(ID) в которую заносятся ID из Document, которые нужно удалить.
Далее удаляем записи из Document

delete Document
FROM   DocumentINNER JOIN #tempDocumentON #tempDocument.ID = Document.ID

Запрос выполняется очень долго (более 30 мин), как можно ускорить выполенение удаления?
Для Document создано много индексов, имеет ли смысл их удалить перед выполнением, а потом заново создать??
Есть ли некие подсказки (hints) для оператора delete??
27 июл 11, 14:35    [11034325]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Dmitry_Minsk, часть записей это скока штук от 2млн? Создание индексов после их убийства может занять определенное время...
Может имеет смысл удалять частями? Либо наоборот - перенести полезные данные в отдельную таблицу, а потом переименовать ее в рабочую.
27 июл 11, 14:40    [11034376]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Dmitry_Minsk
Member

Откуда:
Сообщений: 112
kDnZP,
kDnZP
часть записей это скока штук от 2млн?

Это процентов 10 - 200000 штук

А перенесение - не займет много времени, тем более там тоже нужно будет создать индексы..
27 июл 11, 14:45    [11034422]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Dmitry_Minsk
Member

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

Извините, неправильно написал - остаться должно где-то 10 процентов
27 июл 11, 14:51    [11034486]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Dmitry_Minsk
Member

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

Провел тест - после удаления индексов - удаление прошло за 3 минуты
27 июл 11, 14:53    [11034496]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Leierkastenmann
Member

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

Как вариант удалять тем же запросом, но фиксированными порциями, пока все не будет удалено. Несколько раз приходилось так извращаться, когда из таблицы с несколькими десятками миллионов записей надо было удалить 5-7% записей. Размер порции устанавливался экспериментально.
27 июл 11, 15:15    [11034648]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Leierkastenmann
Member

Откуда: Москва
Сообщений: 113
А, проморгал. Если наоборот, остается 10%, то лучше как написали выше то что надо в отдельную таблицу такой же структуры, старую прибить, новую переименовать.
27 июл 11, 15:17    [11034666]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Leierkastenmann
А, проморгал. Если наоборот, остается 10%, то лучше как написали выше то что надо в отдельную таблицу такой же структуры, старую прибить, новую переименовать.
Тоько ещё возни с индексами и констрейнтами будет много.
Если они, конечно, есть.
27 июл 11, 15:45    [11034853]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Dmitry_Minsk
Member

Откуда:
Сообщений: 112
iap
Leierkastenmann
А, проморгал. Если наоборот, остается 10%, то лучше как написали выше то что надо в отдельную таблицу такой же структуры, старую прибить, новую переименовать.
Тоько ещё возни с индексами и констрейнтами будет много.
Если они, конечно, есть.

Есть конечно! Спасибо, за идею.
27 июл 11, 16:01    [11035008]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Dmitry_Minsk
iap
пропущено...
Только ещё возни с индексами и констрейнтами будет много.
Если они, конечно, есть.

Есть конечно! Спасибо, за идею.
Ккакую идею?
27 июл 11, 16:05    [11035042]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Dmitry_Minsk
Member

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

Про запись в другую таблицу, и потом переименование ее
27 июл 11, 16:09    [11035062]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Dmitry_Minsk
Member

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

идею подал - kDnZP
27 июл 11, 16:11    [11035079]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
iap
Leierkastenmann
Если наоборот, остается 10%, то лучше как написали выше то что надо в отдельную таблицу такой же структуры, старую прибить, новую переименовать.
Тоько ещё возни с индексами и констрейнтами будет много.
Если они, конечно, есть.


Ну, вообще-то, существует еще truncate table. Теоретически, лучше подходит для данного случая.
27 июл 11, 16:18    [11035145]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Shlippenbaranus
iap
пропущено...
Тоько ещё возни с индексами и констрейнтами будет много.
Если они, конечно, есть.


Ну, вообще-то, существует еще truncate table. Теоретически, лучше подходит для данного случая.
1. Нужно удалить не все записи, а часть.
2. TRUNCATE TABLE возможен не всегда. Например, на таблицу могут ссылаться FOREIGN KEYs
3. Есть ещё нюанс с триггерами. При TRUNCATE TABLE они не отработают.
Впрочем, в этом случае и простое копирование с дропом старой таблицы тоже не годится.
27 июл 11, 16:23    [11035200]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Shlippenbaranus, транкейт такая штукенция, которая не позваляет немножко подчистить таблицу. А вариант:

боевая таблица -> переливка данных из боевой таблицы в таблицу временного хранения-> транкейт боевой таблицы -> переливка данных из таблицу временного хранения в боевую

медленнее и хуже, чем

боевая таблица -> переливка данных из боевой таблицы в таблицу временного хранения-> пересоздание констрейнтов -> убить боевую таблицу -> переименование таблицы временного хранения в боевую -> пересоздание констрейнтов

Собственно это не я так придумал, так поступает SSMS, если при модификации схемы таблицы требуется ее пересоздание, ну допустим при изменении IDENTITY поля.

* Кстати, скрипт который сгенерит SSMS можно поиспользовать как заготовку в таком случае, чем я обычно и пользуюсь ;).
27 июл 11, 16:28    [11035251]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
kDnZP
Shlippenbaranus, транкейт такая штукенция, которая не позваляет немножко подчистить таблицу. А вариант:

боевая таблица -> переливка данных из боевой таблицы в таблицу временного хранения-> транкейт боевой таблицы -> переливка данных из таблицу временного хранения в боевую

медленнее и хуже, чем

боевая таблица -> переливка данных из боевой таблицы в таблицу временного хранения-> пересоздание констрейнтов -> убить боевую таблицу -> переименование таблицы временного хранения в боевую -> пересоздание констрейнтов

Собственно это не я так придумал, так поступает SSMS, если при модификации схемы таблицы требуется ее пересоздание, ну допустим при изменении IDENTITY поля.

* Кстати, скрипт который сгенерит SSMS можно поиспользовать как заготовку в таком случае, чем я обычно и пользуюсь ;).


Спасибо за информацию. Я, разумеется, не предлагал "немножко подчистить таблицу" truincate-ом, речь шла о варианте номер один. Логика элементарная - не нужно возиться с пересозданием ограничений целостности, индексов, а что касается ссылок и триггеров - ситуация будет не хуже, чем при "реальном" пересоздании таблицы. А из чего следует, что truncate будет "медленнее и хуже" пересоздания таблицы, кроме того, что так действительно поступает SSMS? Мало ли какие соображения у разработчиков SSMS. Может, им код переписывать неохота.
27 июл 11, 16:48    [11035392]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Shlippenbaranus, т.е. вы считаете, что гонять данные туда-сюда дешевле нежели тока в одну сторону? Вы проверьте, сразу все станет на свои места. Проверить весьма просто))).
27 июл 11, 16:56    [11035444]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
kDnZP
Shlippenbaranus, т.е. вы считаете, что гонять данные туда-сюда дешевле нежели тока в одну сторону? Вы проверьте, сразу все станет на свои места. Проверить весьма просто))).


Проверю, конечно, но логика? Я, все-таки, не понимаю MS SQL. Откуда здесь "гонять туда-сюда"? По естественной логике, и truncate, и drop|create гонят данные в одну и ту же, не будем называть ее вслух, сторону. Представьте себе паровоз и 10 вагонов с ткачами, от которых необходимо отделаться. Что проще - просто отцепить вагоны (truncate), или взорвать весь состав, включая и сам паровоз, а потом паровоз купить себе новый такой же? По вашим словам, MS SQL настолько не умеет отцеплять вагоны, что ему проще сделать второе :(.
27 июл 11, 18:07    [11035963]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Shlippenbaranus
Я, разумеется, не предлагал "немножко подчистить таблицу" truincate-ом, речь шла о варианте номер один.

Расшифруйте, что вы здесь имели в виду. У ТС стоит задача - удалить 90% данных из таблицы в 2млн строк. Как вы это сделаете транкейтом без переливок данных туда-сюда?
27 июл 11, 18:11    [11035986]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
kDnZP
Shlippenbaranus
Я, разумеется, не предлагал "немножко подчистить таблицу" truincate-ом, речь шла о варианте номер один.

Расшифруйте, что вы здесь имели в виду. У ТС стоит задача - удалить 90% данных из таблицы в 2млн строк. Как вы это сделаете транкейтом без переливок данных туда-сюда?


А, понятно :). "Туда-сюда" - Вы имели в виду полезную часть, ту которую необходимо сохранить. Я ее не посчитал, т.к., по логике, основную проблему составляет уничтожение ненужного балласта (который идет строго "туда" :)), и связанные с этим действия на уровне определения данных, типа обновления индексов.

(В моем примере - трудностями, связанными с тем, чтобы снять с отцепляемых вагонов одного человека и, позже, посадить его обратно, я позволил себе пренебречь)

С Вашей поправкой, могу согласиться: нужно экспериментировать. Но тогда в разных случаях результат ожидается разный (в частности, будет разница в зависимости от того, какими индексами обвешана "боевая" таблица, где расположена tempdb т.п.)
27 июл 11, 19:36    [11036308]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 726
по моему удалить 99% записей из двух миллионной таблицы даже при FULL SCAN гораздо быстрее чем читать етот топик.
Демагогия
27 июл 11, 19:59    [11036357]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Shlippenbaranus
kDnZP
пропущено...

Расшифруйте, что вы здесь имели в виду. У ТС стоит задача - удалить 90% данных из таблицы в 2млн строк. Как вы это сделаете транкейтом без переливок данных туда-сюда?


А, понятно :). "Туда-сюда" - Вы имели в виду полезную часть, ту которую необходимо сохранить. Я ее не посчитал, т.к., по логике, основную проблему составляет уничтожение ненужного балласта (который идет строго "туда" :)), и связанные с этим действия на уровне определения данных, типа обновления индексов.

(В моем примере - трудностями, связанными с тем, чтобы снять с отцепляемых вагонов одного человека и, позже, посадить его обратно, я позволил себе пренебречь)

С Вашей поправкой, могу согласиться: нужно экспериментировать. Но тогда в разных случаях результат ожидается разный (в частности, будет разница в зависимости от того, какими индексами обвешана "боевая" таблица, где расположена tempdb т.п.)

Вне зависимости от индексов и местоположения tempdb ваш вариант будет медленнее. Вот вам заготовка для тренировок, пробуйте:
USE tempdb

CREATE TABLE dbo.tstTable(id INT IDENTITY(0,1), DATA VARCHAR(10))
INSERT INTO dbo.tstTable(DATA)
SELECT TOP 100000 STR(v.number,10) FROM master..spt_values v, master..spt_values v1 WHERE v.type='p' AND v1.type='p'

SET STATISTICS TIME ON
/* Чтобы предотвратить возможность потери данных, необходимо внимательно просмотреть этот сценарий, прежде чем запускать его вне контекста конструктора баз данных.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_tstTable
	(
	id int NOT NULL IDENTITY (0, 1),
	DATA varchar(10) NULL
	)  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_tstTable ON
GO
IF EXISTS(SELECT * FROM dbo.tstTable)
	 EXEC('INSERT INTO dbo.Tmp_tstTable (id, DATA)
		SELECT id, DATA FROM dbo.tstTable WITH (HOLDLOCK TABLOCKX) WHERE id<10000')
GO
SET IDENTITY_INSERT dbo.Tmp_tstTable OFF
GO
DROP TABLE dbo.tstTable
GO
EXECUTE sp_rename N'dbo.Tmp_tstTable', N'tstTable', 'OBJECT' 
GO
COMMIT
SET STATISTICS TIME OFF

--SELECT * FROM dbo.tstTable
DROP TABLE dbo.tstTable
27 июл 11, 20:00    [11036360]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
kDnZP
Вне зависимости от индексов и местоположения tempdb ваш вариант будет медленнее. Вот вам заготовка для тренировок, пробуйте


Спасибо за заготовку, я ее поздно заметил. Проверял на своем собственном (реальном) материале, и вот результат:

1. Характеристика данных: "большая таблица" содержала около 5 млн записей, на таблицу были нацеплены кластерный и 2 некластерных индекса, 3 вычисляемых поля и триггер instead off. "В живых" оставлялось около 9.7 процентов записей, причем выборка осуществлялась по кластерному индексу (физически - удалялись все записи за несколько лет кроме записей за один месяц). Для упрощения кода и уменьшения количества ньюансов, на первом этапе эксперимента триггер был отключен, на втором - отключался и подключался при варианте с truncate и создавался вместе с индексами в варианте с drop|create.

2. Собственно, цифры:

2.1. При отсутствии триггера: в варианте с truncate удаление 90% процентов записей произошло за 254 секунды, в варианте с переименованием таблицы - за 323 секунды.

2.2. При отсутствии триггера: в варианте с truncate удаление 90% процентов записей произошло за 269 секунд, в варианте с переименованием таблицы - за 339 секунд.

Таким образом, если я не ошибся в запросах, вариант с truncate не только существенно ПРОЩЕ в кодировании, но и заметно быстрее - по крайней мере, в некоторых случаях :).

P.S. Реальный код не привожу т.к. нельзя. При наличии интереса могу привести подробную схему эксперимента - я допускаю, что мог что-то сделать неоптимально, отчего и такой результат.
29 июл 11, 16:01    [11046086]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
Shlippenbaranus
2.1. При отсутствии триггера:...

2.2. При отсутствии триггера:...


Мда. Copy/Past - страшное дело. Пункт "2.2." - при наличии триггера.
29 июл 11, 16:13    [11046170]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация удаления записей из таблицы  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1321
Leierkastenmann
А, проморгал. Если наоборот, остается 10%, то лучше как написали выше то что надо в отдельную таблицу такой же структуры, старую прибить, новую переименовать.


а скажите при таком способе - понятно что он более быстрый будет
никаких прав на таблицу потеряно не будет
или других побочных эффектов ?
29 июл 11, 20:25    [11047197]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить