Составил запрос. На удаление с использованием временной таблицы. Но как этой временной таблице добавить индекс?

SET DATEFORMAT ymd
DECLARE @datevar datetime = '2016-08-01'

DECLARE @docs table(UIDs binary(16))
insert into @docs

SELECT     _IDRRef
FROM         _Document9915
WHERE     (_Date_Time >= @datevar)

UNION

SELECT   _IDRRef
FROM     _Document106
WHERE    (_Date_Time >= @datevar)


DELETE FROM _InfoReg4206
WHERE     (_Fld4207_RRRef NOT IN (select UIDs as _Fld4207_RRRef from @docs))


забыли про смещение дат. забыли про tref (uid-ы могут совпадать ) .

а по теме

https://msdn.microsoft.com/ru-ru/library/ms188783.aspx

http://www.t-sql.ru/post/index_on_table_variable.aspx

(1)
>>uid-ы могут совпадать
Ну там не очень важная таблица, если пару лишних строк удалится ничего страшного нет.

>>забыли про смещение дат
В базе 0 смещение, так что ничего страшного я думаю.

(4) посмотрите на эти даты в табличке

(5) Посмотрел. Я изначально просто выбрал документы, допустим за сегодня, нормально. Только сегодняшние выбрались.

зачем тут временная таблица вообще?

(7) дык, модно, молодежно

(7) ХЗ. Так в 1С обычно принято. :) Еще индекс можно построить по временной. Вроде как быстрее должно быть.

для однократного применения индекс не нужен

DELETE FROM _InfoReg4206
WHERE     (_Fld4207_RRRef NOT IN (SELECT     _IDRRef
FROM         _Document9915
WHERE     (_Date_Time >= @datevar)

UNION ALL

SELECT   _IDRRef
FROM     _Document106
WHERE    (_Date_Time >= @datevar)
))

(11) UNION ALL - я так понимаю быстрее должен обрабатывать объединение?

(10) Спорно. Почему то во всех мануалах (по 1С) советуют использовать временные с индексами. Даже если они 1 раз используются.

(12) естественно

(14) SELECT происходит быстро, а вот DELETE - уже час шуршит.
Таблица исходная из которой нужно удалить очень большая (250 миллионов записей). Временная вышла около 3 миллионов.
Как ускорить DELETE?

(15) если ты почти всю ее удаляешь, то бишь оставляешь 3 из 250 миллионов, то не проще было создать другую таблицу. Туда перенести нужные данные INSERT. Старую грохнуть DROP.
Новую переименовать

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

(17) В sql менеджере - показать код создания

(18) Есть такое.
А как со статистикой быть? Она автоматом создается?

(0)
DECLARE @docs table(UIDs binary(16)) - это не временная таблица, а табличная переменная.

Непонятно, зачем она вообще:

DELETE reg
FROM _InfoReg4206 reg
JOIN _Document9915 doc ON reg._RecorderRRef = doc._IDRRef
WHERE doc._Date_Time >= @datevar
    --AND reg._RecorderTRef = 0x00000105 /*это условие луше указать, чтобы в индекс попасть, значение можно в профайлере узнать*/


И для второй таблицы доков - аналогично.

+ смещение дат, да.

+ - еще можно удалять порциями:
DELETE TOP (1000)

(17) Тем не менее правильно товарищ в (16) говорит... Времени разобраться плюс время выполнить все равно будет меньше, чем вот так, в лоб удалять.
Можно еще просто создать копию таблицы с нужными данными (можно мастером накликать код, если сложно), затем truncate table очистит полностью исходную, потом обратно скопировать данные. Будет быстрее гораздо. Только права на truncate должны быть

(23) А обратно скопировать через insert? Или какой то другой ход есть?

(24) можно через временную:
BEGIN TRAN
BEGIN TRY
1) SELECT INTO #temp SELECT ...
2) TRUNCATE TABLE Reg
3) INSERT INTO Reg SELECT * FROM #temp
END TRY

BEGIN CATCH
    SELECT ERROR_MESSAGE()
    ROLLBACK TRAN
END CATCH

IF @@TRANCOUNT > 0 COMMIT TRAN

SELECT @@TRANCOUNT -- должно быть 0 !!

(25) Спасибо. То что надо. Так и сделаю.

(26)
1) - SELECT reg.* INTO #temp FROM _InfoReg4206 reg ...

(27) Спасибо большое. Все получилось. Сократилось до 7 минут.
У меня еще вопрос. После инсерта нужно переиндексацию, (перестроение, реорганизацию) индексов этой таблицы делать или sql сам все автоматом сделает?

не нужно . автоматом

(29) Спасибо.

Оказалось все не так просто.
На тестовой базе все нормально, на рабочей ошибка:
Не удается вставить повторяющуюся строку ключа в объект "dbo._InfoReg4206" с уникальным индексом "_InfoRe4206_ByDims_RR". Повторяющееся значение ключа: (0x08, 0x00001211, 0x7f8400259098e7a911e687ce59a0abda, 0x939b003048578e5311de15178d2d668c)

Поиск по этим полям показал, что запись все же уникальна.
Я так полагаю, что просто во время выполнения запроса в эту таблицу вносятся изменения из 1С.

Как заблокировать на уровне SQL таблицу на время выполнения всего скрипта?

Пример скрипта тут: http://pastebin.com/5fKkFX4e

(31) выгнать юзеров

https://msdn.microsoft.com/ru-ru/library/ms174335.aspx

Тебе нужен TABLOCK

(32) Не пойдет. Там постоянно кроме юзеров еще и автообмены. Ну и выгонять юзеров долго.

(34) У меня сначала идет TRUNCATE, потом INSERT. Между этими двумя строками может пролезть INSERT от 1С?

Правильно так?
INSERT INTO _InfoReg4206 SELECT * FROM #tmp_InfoReg4206_20161017113542 WITH (TABLOCK)

INSERT INTO _InfoReg4206 WITH (TABLOCK) SELECT * FROM #tmp_InfoReg4206_20161017113542

(0) Вот лезут такие грязными ручками в SQL, а потом базы падают. Начерта это делать напрямую? Почему не через платформу?

(37) 250 милионов записей из регистра долго удаляются.
(36) Аа. Точно. Не в тот запрос воткнул.

А вообще, что удаляешь? Версии объектов?

(39) Не. Создали тут умельцы свой регистр. Только очистку тупую сделали. 100 т. записей в день удаляется, посредством 1С, а прирастает больше. Иногда намного больше, это когда перепроведения всякие. Сейчас накопилось 270 миллионов записей, из них актуальны только 13 миллионов.