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

Откуда:
Сообщений: 144
Просьба сильно не ругать, если что не так. Хочу понять, правильно ли я подхожу к созданию процедуры загрузки таблицы.
Таблица 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

Вопрос
Правильно ли я обращаюсь с индексами?
Можно ли оптимизировать подобную процедуру ?
3 июн 11, 15:39    [10759789]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
SergeV
Member

Откуда:
Сообщений: 144
MSSQL 2008
3 июн 11, 15:40    [10759793]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Обратите внимание на MERGE (Transact-SQL)
3 июн 11, 16:02    [10760043]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
SergeV
Member

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

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

Пожалуйста не отсылайте к MSDN. Интересует живой опыт разработки.
6 июн 11, 12:30    [10768814]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
invm
Member

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

Почему вы решили, что полное перестроение таблицы выгоднее, чем insert/update/delete?
Если уж решили перестраивать таблицу, то:
1. Удалить индексы;
2. Очистить таблицу;
3. Залить данные в таблицу;
4. Создать индексы.
6 июн 11, 13:40    [10769546]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
SergeV
Member

Откуда:
Сообщений: 144
invm
Почему вы решили, что полное перестроение таблицы выгоднее, чем insert/update/delete?

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

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

То есть вообще не использовать DBCC DBREINDEX, а каждый раз делать CREATE INDEX ?
6 июн 11, 13:54    [10769676]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
invm
Member

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

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

Ну какой смысл вставлять в таблицу данные, обновляя этим индекс, а потом этот же индекс перестраивать? Кстати, DBCC DBREINDEX объявлена deprecated, взамен пользуйтесь alter index rebuild.
6 июн 11, 14:41    [10770125]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
invm, полностью согласен. Может ALTER INDEX DISABLE и дает какой-то выигрыш, но мне обычно проще убить индексы, залить, а потом создать. Работает весьма шустро, намного быстрее чем если индексы не трогать.
6 июн 11, 14:56    [10770285]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
invm
Member

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

Думаю, что можно и ALTER INDEX DISABLE. Конечно же, если индекс не кластерный :)
6 июн 11, 15:03    [10770354]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
SergeV
Member

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

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

И еще, поправьте меня если я не прав - оптимальнее все же удалять/пересоздавать (DROP/CREATE) индекс, чем выключать/включать (ALTER DISABLE/ALTER REBUILD), поскольку во втором случае статистика индекса сохраняется. И следовательно в варианте во втором случае придется время от времени делать UPDATE STATISTICS.
6 июн 11, 16:21    [10771210]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
SergeV, Про статистику и индексы.
6 июн 11, 16:34    [10771378]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
ну и тут тоже есть
6 июн 11, 16:36    [10771403]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
invm
Member

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

BOL
Отключение кластеризованного индекса блокирует доступ пользователя к данным базовой таблицы
6 июн 11, 17:01    [10771735]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
SergeV
Member

Откуда:
Сообщений: 144
invm
Ну какой смысл вставлять в таблицу данные, обновляя этим индекс, а потом этот же индекс перестраивать?


Извините, я вернулся после долгого отсутствия :)

Вот этого Вашего замечания а совсем не понял. Индекс и нужно перестраивать после вставки данных, т.к он нарушается в результате вставки. Иначе для чего же нужно перестроение ?

MSDN:

Инструкция DBCC DBREINDEX выполняет перестроение индекса для таблицы или всех индексов, определенных для таблицы. Потребность в этом может возникнуть после массового копирования данных в таблицу.

И еще, приверженцам идеологии DROP/CREATE.

Инструкция DBCC DBREINDEX позволяет перестроить все индексы таблицы с помощью одной инструкции. Это проще, чем кодирование множества инструкций DROP INDEX и CREATE INDEX. Так как работа выполняется одной инструкцией, инструкция DBCC DBREINDEX автоматически становится атомарной, в то время как отдельные инструкции DROP INDEX и CREATE INDEX необходимо включить в транзакцию, чтобы они стали атомарными. Кроме того, инструкция DBCC DBREINDEX обеспечивает более глубокую оптимизацию по сравнению с отдельными инструкциями DROP INDEX и CREATE INDEX.

Хотя, конечно в следующих версиях этой инструкции не будет и надо будет пользоваться ALTER INDEX.

Почему гуру молчат, помогите разобраться !!!
21 июн 11, 12:19    [10845776]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
SergeV
Вот этого Вашего замечания а совсем не понял. Индекс и нужно перестраивать после вставки данных, т.к он нарушается в результате вставки. Иначе для чего же нужно перестроение ?

Ответьте себе на вопрос: Если я собрался после вставки всех данных перестроить индекс, то зачем мне во время вставки этот индекс обновлять?
21 июн 11, 12:37    [10845890]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
SergeV
Вот этого Вашего замечания а совсем не понял. Индекс и нужно перестраивать после вставки данных, т.к он нарушается в результате вставки. Иначе для чего же нужно перестроение ?
Индекс не нужно перестраивать после вставки данных, он не нарушается в результате вставки.

Иначе вообще существование индексов было бы бессмысленно, еслиб они при любом изменении данных становились бы недействительными!

Индексы, бывает, удаляют перед вставкой и пересоздают после неё для того, чтобы облегчить сам процесс добавления данных, т.к. процедура разового пересоздания индекса дешевле, чем изменение индекса на лету во время вставки/изменения данных.
21 июн 11, 12:55    [10846020]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
SergeV
Member

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

Спасибо, теперь стало яснее.

Я правильно понял, что во время вставки/изменения данных обновление индекса происходит автоматически ?

Понятно, что DROP INDEX вызывают перед вставкой а CREATE INDEX после вставки.
Остается непонятным в какой момент нужно вызывать DBCC DBREINDEX - до или после вставки ? В своей процедуре я вызывал после вставки, но тут было сказано, что это не имеет смысла.
21 июн 11, 13:31    [10846415]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
SergeV
Остается непонятным в какой момент нужно вызывать DBCC DBREINDEX - до или после вставки ? В своей процедуре я вызывал после вставки, но тут было сказано, что это не имеет смысла.
Ога. Остается только потестировать вызов DBCC DBREINDEX до вставки.
21 июн 11, 13:37    [10846491]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
SergeV
Member

Откуда:
Сообщений: 144
tpg
SergeV
Остается непонятным в какой момент нужно вызывать DBCC DBREINDEX - до или после вставки ? В своей процедуре я вызывал после вставки, но тут было сказано, что это не имеет смысла.
Ога. Остается только потестировать вызов DBCC DBREINDEX до вставки.


invm
Ну какой смысл вставлять в таблицу данные, обновляя этим индекс, а потом этот же индекс перестраивать?


Эту фразу я понял таким образом, что вызов DBREINDEX после вставки не имеет смысла.

И все-таки, когда вызывать DBREINDEX ???
HELP !!!!!!
21 июн 11, 13:55    [10846679]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
SergeV,

вы какой смысл в пересоздание/перестроение индекса в вашем конкретном случае вкладываете?
21 июн 11, 14:07    [10846804]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
Sergey Sizov
Member

Откуда:
Сообщений: 1579
SergeV
И все-таки, когда вызывать DBREINDEX ???
А почему Вы решили, что ее обязательно надо вызывать?
21 июн 11, 14:11    [10846838]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
SergeV
Member

Откуда:
Сообщений: 144
Sergey Sizov
А почему Вы решили, что ее обязательно надо вызывать?

Хороший вопрос, пойду думать)

tpg
вы какой смысл в пересоздание/перестроение индекса в вашем конкретном случае вкладываете?

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

Откуда: мск
Сообщений: 1887
SergeV
И все-таки, когда вызывать DBREINDEX ???
HELP !!!!!!


прочитайте хотя бы первые абзацы здесь http://msdn.microsoft.com/ru-ru/library/ms188917.aspx
и вот тут пример Г внизу http://msdn.microsoft.com/ru-ru/library/ms188917.aspx

ситуация должна проясниться
21 июн 11, 14:19    [10846931]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
поправка, первая ссылка http://msdn.microsoft.com/ru-ru/library/ms189858.aspx
21 июн 11, 14:20    [10846946]     Ответить | Цитировать Сообщить модератору
 Re: Процедура загрузки таблицы  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
SergeV
tpg
вы какой смысл в пересоздание/перестроение индекса в вашем конкретном случае вкладываете?

Смысл следующий - иметь быстрые работоспособные индексы на таблице, в которой данные меняются ежедневно.
А с чего вы решили, что они у вас медленные и неработоспособные? Вы проводили какие-то измерения после тестовых загрузок, сравнивали их с рекомендуемыми?
21 июн 11, 14:22    [10846973]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить