Процедура загрузки таблицы

SergeV
Дата: 03.06.2011 15:39:31
Просьба сильно не ругать, если что не так. Хочу понять, правильно ли я подхожу к созданию процедуры загрузки таблицы.
Таблица FaceAdres обновляется каждый день, интенсивность изменения данных в ней может быть разной (от 0 до 10% записей).
На таблицу создаются индексы, в последующих процедурах она используется в соединениях с другими.
Таблица является частью источника данных для куба, таких таблиц там много, эта приведена для примера. Соответственно и таких процедур тоже много. Все они однотипны и различаются только количеством индексируемых полей.

Данная процедура должна ежедневно обновлять таблицу FaceAdres.
Я решил не использовать операцию UPDATE, а каждый раз заливать все данные заново.

CREATE PROCEDURE [dbo].[load_FaceAdres]
AS
BEGIN

DECLARE @newTable bit

-- create table FaceAdres ... (создание постоянной таблицы, если ее еще нет)
...

-- insert into #tmp_FaceAdres select ... (загрузка временной таблицы)
...

-- update #tmp_FaceAdres ... (промежуточная обработка временной таблицы)
...

-- сравнение таблиц - временной (#tmp_FaceAdres) и постоянной (FaceAdres)
if  @newTable=1  OR
EXISTS 
(SELECT #tmp_FaceAdres.*, FaceAdres.*
FROM #tmp_FaceAdres FULL JOIN FaceAdres 
	ON (#tmp_FaceAdres.fkUPK = FaceAdres.fkUPK OR (#tmp_FaceAdres.fkUPK IS NULL AND FaceAdres.fkUPK IS NULL)) 
	AND (#tmp_FaceAdres.fkADRVID = FaceAdres.fkADRVID OR (#tmp_FaceAdres.fkADRVID IS NULL AND FaceAdres.fkADRVID IS NULL))
	AND (#tmp_FaceAdres.ADRES = FaceAdres.ADRES OR (#tmp_FaceAdres.ADRES IS NULL AND FaceAdres.ADRES IS NULL))
WHERE
	(FaceAdres.fkUPK IS NULL 
     AND FaceAdres.fkADRVID IS NULL
     AND FaceAdres.ADRES IS NULL
	)
OR
	(#tmp_FaceAdres.fkUPK IS NULL 
	AND #tmp_FaceAdres.fkADRVID IS NULL
	AND #tmp_FaceAdres.ADRES IS NULL
	)
)
begin-- есть различия

        -- копирование данных из временной в постоянную
	TRUNCATE TABLE FaceAdres
	INSERT INTO FaceAdres SELECT * from #tmp_FaceAdres

	if @newTable = 1
	begin
		-- добавление индексов по одному или нескольким полям
		CREATE /*NONCLUSTERED*/ INDEX Index_fkUPK_2
		ON FaceAdres(fkUPK)
		--WITH DROP_EXISTING
		
		...
	end
	else begin
		-- перестройка индексов
		DBCC DBREINDEX (FaceAdres) 
		
		-- ??? UPDATE STATISTICS ???
	end
end
else-- нет различий
	print 'Таблица FaceAdres не изменилась'

END

Вопрос
Правильно ли я обращаюсь с индексами?
Можно ли оптимизировать подобную процедуру ?
SergeV
Дата: 03.06.2011 15:40:17
MSSQL 2008
invm
Дата: 03.06.2011 16:02:21
Обратите внимание на MERGE (Transact-SQL)
SergeV
Дата: 06.06.2011 12:30:21
invm,

Спасибо за идею. Но а если это 2005 ? Там merge еще не было.
Меня интересует сама структура моей процедуры, сам принцип обновления таблицы, который в ней используется.
Он правильный или нет?
Правильно ли, создав индекс CREATE INDEX, при каждом последующем ежедневном запуске (когда таблица полностью обновляется) перестраивать индекс с помощью DBCC DBREINDEX ?
Не приведет ли это к излишней фрагментации и в итоге к замедлению выполнения ?
Может быть здесь нужно использовать и UPDATE STATISTICS ?

Пожалуйста не отсылайте к MSDN. Интересует живой опыт разработки.
invm
Дата: 06.06.2011 13:40:33
SergeV,

Почему вы решили, что полное перестроение таблицы выгоднее, чем insert/update/delete?
Если уж решили перестраивать таблицу, то:
1. Удалить индексы;
2. Очистить таблицу;
3. Залить данные в таблицу;
4. Создать индексы.
SergeV
Дата: 06.06.2011 13:54:07
invm
Почему вы решили, что полное перестроение таблицы выгоднее, чем insert/update/delete?

Потому что не требуется дополнительных сравнений "чего нет в первой и есть во второй" + "чего нет во второй и есть в первой". Имеющуюся здесь проверку через FULL JOIN я могу совсем убрать, и пересоздавать таблицу каждый раз безусловно.
А так же TRUNCATE в отличие от update/delete не пишет в лог.

invm
1. Удалить индексы;
2. Очистить таблицу;
3. Залить данные в таблицу;
4. Создать индексы.

То есть вообще не использовать DBCC DBREINDEX, а каждый раз делать CREATE INDEX ?
invm
Дата: 06.06.2011 14:41:34
SergeV
Потому что не требуется дополнительных сравнений "чего нет в первой и есть во второй" + "чего нет во второй и есть в первой". Имеющуюся здесь проверку через FULL JOIN я могу совсем убрать, и пересоздавать таблицу каждый раз безусловно.
А так же TRUNCATE в отличие от update/delete не пишет в лог.

Выбор того или иного способа ависит от ресурсоемкости оного. Вы проверяли? К тому же truncate все же минимально журналируется и требует разрешения alter table.
SergeV
То есть вообще не использовать DBCC DBREINDEX, а каждый раз делать CREATE INDEX ?

Ну какой смысл вставлять в таблицу данные, обновляя этим индекс, а потом этот же индекс перестраивать? Кстати, DBCC DBREINDEX объявлена deprecated, взамен пользуйтесь alter index rebuild.
kDnZP
Дата: 06.06.2011 14:56:55
invm, полностью согласен. Может ALTER INDEX DISABLE и дает какой-то выигрыш, но мне обычно проще убить индексы, залить, а потом создать. Работает весьма шустро, намного быстрее чем если индексы не трогать.
invm
Дата: 06.06.2011 15:03:45
kDnZP,

Думаю, что можно и ALTER INDEX DISABLE. Конечно же, если индекс не кластерный :)
SergeV
Дата: 06.06.2011 16:21:49
invm,

А чем опасно делать ALTER INDEX DISABLE кластерного индекса, если я снова включу его в той же процедуре, в которой отключил ?
Ведь блокировка таблицы в таком случае будет накладываться только на время выполнения этой процедуры.

И еще, поправьте меня если я не прав - оптимальнее все же удалять/пересоздавать (DROP/CREATE) индекс, чем выключать/включать (ALTER DISABLE/ALTER REBUILD), поскольку во втором случае статистика индекса сохраняется. И следовательно в варианте во втором случае придется время от времени делать UPDATE STATISTICS.