Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 truncate или drop  [new]
нуб987
Guest
подскажите, что правильней делать: truncate или drop?
задача такая: в таблицу нужно перезалить хитрым запросом около 100млн строк и построить индексы на некоторые поля.
Через некоторое время все опять нужно пересчитать, т.е. заново перезалить 100млн строк (не обязательно те же самые - они могут меняться, потому и обновляем).
И в таком случае что правильней делать: удалить всю таблицу с индексами, залить данные и построить новые индексы. Или же truncate с удалением индексов?
Таблица получается довольно большая. В каком случае будет минимальная фрагментация базы?
14 авг 13, 04:13    [14705594]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
aleks2
Guest
нуб987
подскажите, что правильней делать: truncate или drop?


А ничо, что оне не эквивалентны?

truncate = drop+create+create indexes

Так-то - пофег.
14 авг 13, 06:08    [14705626]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
думается, truncate будет вполне достаточно, а то прям какая-то зачистка территории, с выжиганием
14 авг 13, 09:25    [14705896]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
нуб987
Guest
aleks2
А ничо, что оне не эквивалентны?

truncate = drop+create+create indexes

Так-то - пофег.

у вас взаимоисключающие предложения
так они эквивалентны или нет?

вот, что пишет микрософт вот здесь:
микрософт
Инструкция TRUNCATE TABLE удаляет все строки таблицы, но структура таблицы и ее столбцы, ограничения, индексы и т. п. сохраняются. Чтобы удалить не только данные таблицы, но и ее определение, следует использовать инструкцию DROP TABLE.
14 авг 13, 09:56    [14706019]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
нуб987
aleks2
А ничо, что оне не эквивалентны?

truncate = drop+create+create indexes

Так-то - пофег.

у вас взаимоисключающие предложения
так они эквивалентны или нет?
Как же так?
truncate = drop+create+create indexes <> drop
Где противоречие?
14 авг 13, 10:02    [14706045]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
нуб987
Guest
StarikNavy
думается, truncate будет вполне достаточно, а то прям какая-то зачистка территории, с выжиганием

а в плане фрагментации базы?
если делать truncate, потом пересоздавать индексы (удалять, заливать данные, создавать снова) - это ж куча действий. И если параллельно работает еще какой-то процесс, то пока мы, например, удаляем индексы, освободившееся от truncate место может заняться какими-то объектами из параллельного процесса. И тогда вновь созданные индексы окажутся физически в другом месте файла БД.
... Хотя с drop вроде все то же самое может произойти......
Что-то запутался...
14 авг 13, 10:02    [14706046]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
нуб987
StarikNavy
думается, truncate будет вполне достаточно, а то прям какая-то зачистка территории, с выжиганием

а в плане фрагментации базы?
если делать truncate, потом пересоздавать индексы (удалять, заливать данные, создавать снова) - это ж куча действий. И если параллельно работает еще какой-то процесс, то пока мы, например, удаляем индексы, освободившееся от truncate место может заняться какими-то объектами из параллельного процесса. И тогда вновь созданные индексы окажутся физически в другом месте файла БД.


поясните, пожалуйста, что такое фрагментация базы....
Или вы имеете ввиду физическую фрагментацию страниц данных??
Или вы про внешнюю фрагментацию физических файлов БД на дисках системы ?

По сабжу:
Если вы всё равно планируете удалять индексы, заливать данные и создавать индексы заново, то может быть целесообразно будет рассмотреть вариант с if exists drop и далее select into? А потом уже после заливки данных мутить индексы.
14 авг 13, 10:10    [14706092]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
нуб987
Guest
Сергей Викт.
поясните, пожалуйста, что такое фрагментация базы....
Или вы имеете ввиду физическую фрагментацию страниц данных??
Или вы про внешнюю фрагментацию физических файлов БД на дисках системы ?

я имел в виду фрагментацию страниц данных. Хотя вроде получается, что фрагментация будет в любом случае: хоть truncate, хоть drop + create + create indexes
Сергей Викт.
По сабжу:
Если вы всё равно планируете удалять индексы, заливать данные и создавать индексы заново, то может быть целесообразно будет рассмотреть вариант с if exists drop и далее select into? А потом уже после заливки данных мутить индексы.

это в сравнении с truncate? А почему так лучше?
ПС. я не спорю, а просто понять пытаюсь
14 авг 13, 10:16    [14706123]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
Maxx
Member [скрыт]

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

да проведите уже просто екперимент на БД и все ..посмотрите что "останется" после drop и truncate
14 авг 13, 10:18    [14706129]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
нуб987
Guest
Maxx
нуб987,

да проведите уже просто екперимент на БД и все ..посмотрите что "останется" после drop и truncate

вот тут, извините, вообще не понял (нуб же). Я бы с радостью, но куда посмотреть?
14 авг 13, 10:31    [14706213]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
Glory
Member

Откуда:
Сообщений: 104760
Удаление такого объекта как таблица может породить очень много каскадных действий
Кроме простых индексов, которые вы уже упомянули, есть еще такие вещи как права доступа, например.
Они тоже удалятся. Или триггера
14 авг 13, 10:36    [14706244]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
Empirical
Member

Откуда:
Сообщений: 99
нуб987
все опять нужно пересчитать, т.е. заново перезалить 100млн строк (не обязательно те же самые - они могут меняться, потому и обновляем).

А merge вариант не рассматривали?
14 авг 13, 10:42    [14706272]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
нуб987
Guest
Glory
есть еще такие вещи как права доступа, например.
Они тоже удалятся. Или триггера

а если мы рассматриваем только таблицу + индексы. Без триггеров и прочего. То какой метод (truncate или drop) оптимальней? Хотя и тут не вижу особой разницы. Ну создадим мы помимо индексов еще и права доступа... Кроме обилия кода это вроде бы ни к чему не приведет.

Очевидно, что "трудозатратней" drop: нужно написать много кода для "восстановления" таблицы.
Но в плане будущих издержек (фрагментация базы, например. Или еще какие-то вещи, о которых по неопытности могу не знать - для этого и была создана тема), что лучше?
Или они равноценны в описываемой задаче и тогда проще выполнить truncate table?
14 авг 13, 10:45    [14706289]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
Glory
Member

Откуда:
Сообщений: 104760
нуб987
а если мы рассматриваем только таблицу + индексы. Без триггеров и прочего. То какой метод (truncate или drop) оптимальней?

Оптимальнее=быстрее ?
Разумеется удаление быстрее очистки.

нуб987
Хотя и тут не вижу особой разницы. Ну создадим мы помимо индексов еще и права доступа...

Для того, чтобы что-то создать, нужно знать что-создавать.
Вы каким способом намерены получить _полный скрипт_ создания таблицы ?

нуб987
Но в плане будущих издержек (фрагментация базы, например.

А какая критическая фрагментация может возникнуть при добавлении в пустую таблицу ? Особенно если добавлять в порядке кластерного индекса
14 авг 13, 10:55    [14706338]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
нуб987
Guest
Glory
Для того, чтобы что-то создать, нужно знать что-создавать.
Вы каким способом намерены получить _полный скрипт_ создания таблицы ?

например, Generate SQL Script'ом
ПС. я не говорю, что этот метод (drop + create...) самый правильный или самый лучший. Я пытаюсь понять, нужно ли оно вообще и будут ли плюсы. Например, перестройка индексов - часто полезна.
Glory
нуб987
Но в плане будущих издержек (фрагментация базы, например.

А какая критическая фрагментация может возникнуть при добавлении в пустую таблицу ? Особенно если добавлять в порядке кластерного индекса

критической, может, и никакой. Но если запускать это перезаполнение таблицы, например, раз в неделю или каждый день, то от таких объемов перезаполнения возможно базе будет не оч.хорошо.
Опять же, я не говорю, что ей точно будет не хорошо (я просто этого не знаю. Книжки вроде читаю, но пока не знаю). Я всего лишь уточняю - "будет ли? Или все будет ок и можно просто делать truncate?"
14 авг 13, 11:04    [14706406]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
Glory
Member

Откуда:
Сообщений: 104760
нуб987
например, Generate SQL Script'ом

- Т.е. перед заливкой данных вы будете каждый раз вручную создавать скрипт ?
- вы видели сколько там опций скриптования ?

нуб987
критической, может, и никакой. Но если запускать это перезаполнение таблицы, например, раз в неделю или каждый день, то от таких объемов перезаполнения возможно базе будет не оч.хорошо.

Что такое "не оч.хорошо" то ? Приведите критерии хорошо/нехорошо
14 авг 13, 11:10    [14706445]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
нуб987
Guest
Glory
нуб987
например, Generate SQL Script'ом

- Т.е. перед заливкой данных вы будете каждый раз вручную создавать скрипт ?
- вы видели сколько там опций скриптования ?

нет, конечно. Один раз сгенерировать и использовать этот скрипт - структура таблицы же не меняется, мы просто перезаливаем в нее данные.
Glory
Что такое "не оч.хорошо" то ? Приведите критерии хорошо/нехорошо

например, если НЕ перестраивать индексы, со временем они фрагментируются, запросы выполняются медленнее и т.д.
Вот с базой такого не будет ли в случае с truncate'ом?
14 авг 13, 11:18    [14706492]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31445
нуб987
Я пытаюсь понять, нужно ли оно вообще и будут ли плюсы. Например, перестройка индексов - часто полезна.
Так ведь индексы вы полностью собираетесь перестраивать в любом случае? Вы же пишите:
нуб987
Или же truncate с удалением индексов?


В этом случае без разницы.

Вариант с drop-create имеет только организационные преимущества - контроль за моделью данных будет в одном месте, в исходниках процесса импорта.
С другой стороны, придётся так же прописывать и права на эту таблицу.
В общем, смотрите в первую очередь на оргпроблемы, с точки зрния производительности/фрагментации разницы нет.

Ещё помнится была какая то бага с потерей страниц для image-text полей при удалении данных (но не помню, касается ли это так же и truncate) - тут drop-create выглядит надёжнее.
14 авг 13, 11:30    [14706555]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
Glory
Member

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

Кроме полей, в таблице есть еще много чего. Сказали же об этом.



нуб987
например, если НЕ перестраивать индексы, со временем они фрагментируются, запросы выполняются медленнее и т.д.

Индексы фрагментируются при изменении данных.
Наряду с обновлением статистики, дефрагментация есть стандартная операция регламентного обслуживания любой базы. Есть документированные способы и методы определения, когда и что нужно делать с индексом
14 авг 13, 11:32    [14706571]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
fewfef
Guest
нуб987,

", например, удаляем индексы, освободившееся от truncate место может заняться какими-то объектами из параллельного процесса. "

создай для своей маленькой таблицы и ее индексов отдельные file group

+ гавнакод

alter database db4 add filegroup big_tbl_fg
go
alter database db4 add filegroup big_tbl_idx_fg
go
alter database db4
add file 
(
    name = big_tbl_data_file1,
	filename = 'D:\\db\\mssql11\\Data\\big_tbl_data_file1.ndf',
    size = 5mb,
    maxsize = 100mb,
    filegrowth = 5mb
)
to filegroup big_tbl_fg
go
alter database db4
add file 
(
    name = big_tbl_idx_data_file1,
	filename = 'D:\\db\\mssql11\\Data\\big_tbl_idx_data_file1.ndf',
    size = 5mb,
    maxsize = 100mb,
    filegrowth = 5mb
)
to filegroup big_tbl_idx_fg
go
create table t1(
	id int not null,
	dummy_data nvarchar(50) not null,
 constraint pk_t1 primary key clustered (id asc)
) on big_tbl_fg
go
create nonclustered index ix_t1 on t1(id asc, dummy_data asc) on big_tbl_idx_fg
go

declare @sql nvarchar(max) = '';

select @sql += 'alter index ' + i.name + ' on ' + t.name + ' disable;'
from
	sys.tables t
		inner join
	sys.indexes i on t.object_id = i.object_id
where t.name = 't1' and i.is_primary_key = 0
order by case when t.type_desc = 'CLUSTERED' then 0 else 1 end

exec(@sql)
go

truncate table t1;
go

-- тут bcp льет данные
;with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
rt(n) as(select row_number() over(order by (select 0)) from l4 t1, l4 t2)
insert into t1(id, dummy_data)
	select top(1000000) n as id, cast(checksum(newid()) as nvarchar(50)) as dummy_data
	from rt


-- данные залиты
go

declare @sql nvarchar(max) = '';

select @sql = 'alter index ' + i.name + ' on ' + t.name + ' rebuild;'
from
	sys.tables t
		inner join
	sys.indexes i on t.object_id = i.object_id
where t.name = 't1' and i.is_primary_key = 0
order by case when t.type_desc = 'CLUSTERED' then 0 else 1 end

exec(@sql)
go

14 авг 13, 13:21    [14707493]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
fewfef
Guest
исичо я новичек, сильно не пинать за гавнакод. но идея выделить таблице свой тейблспейс файл груп, чтобы туда больше никто носа не совал кроме процессов работающих с таблицей, тогда фрагментации таблицы не будет.
14 авг 13, 13:24    [14707506]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
Гость333
Member

Откуда:
Сообщений: 3683
fewfef
тогда фрагментации таблицы не будет.

Почему не будет? Возможные вставки в середину индексов никто не отменял, с прилагающимися к ним page split и фрагментацией.
14 авг 13, 13:48    [14707711]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
fewfef
место может заняться какими-то объектами из параллельного процесса
В любой момент времени.
Даже при заливке данных.

Фрагментация это нормально.
Только жуткая фрагментация - плохо. Если периодически за всеми объектами файла следить, то и проблем не будет.
Не следить - то неважно что вы будете делать с одной таблой (create или truncate).
IMXO
Бардак заразителен.
14 авг 13, 15:11    [14708312]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3618
truncate СПЕЦИАЛЬНО ПРИДУМАН для того чтобы дроп не делать!
14 авг 13, 18:04    [14709455]     Ответить | Цитировать Сообщить модератору
 Re: truncate или drop  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Гость333
fewfef
тогда фрагментации таблицы не будет.

Почему не будет? Возможные вставки в середину индексов никто не отменял, с прилагающимися к ним page split и фрагментацией.
Я думаю что fewfef хотел сказать, что не будет фрагментации экстентов, а также если файлу сразу выделить место, то не будет также и физической фрагментации на диске. Но от сплитов, да, никуда не деться.
14 авг 13, 21:50    [14710049]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить