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

Откуда:
Сообщений: 51
Дилетантский вопрос.
Создаем временную таблицу на которую нужен кластерный индекс, записей будет пусть 50к
По производительности лучше кластерный индекс до вставки создать или после?

По моему мнению быстрее получится сначала создать, а потом вставить данные. Т.к. данные уже будут вставляться в нужном порядке, но нужна будет предварительная сортировка.
Если иначе, то будет создана куча, которую потом также нужно будет сортировать и перестраивать данные.
7 июн 21, 11:06    [22332141]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
0wl
Member

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

Вставка в кучу может быть быстрее, если соблюсти условия минимального логирования (см. https://docs.microsoft.com/en-US/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15#using-insert-intoselect-to-bulk-import-data-with-minimal-logging-and-parallelism и https://docs.microsoft.com/en-US/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import?view=sql-server-ver15 . Первая вставка в пустцю таблицу с кластерным индексом тоже будет минимально логироваться, но следующие итерации будут с полным логированием.

Ну и такой важный бонус: если создавать индекс по существующим данным, он вместе с собой соберёт актуальную статистику. А если сначала создать индекс, а потом наливать данные, придётся уповать на автообновление статистик.
7 июн 21, 11:22    [22332147]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
Владислав Колосов
Member

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

сортировка будет использована при вставке в пустую таблицу, даже если Вы ее не укажете явно, то есть этого не избежать. Дважды заполнять таблицу я не вижу смысла - при вставке и при создании кластерного индекса.
7 июн 21, 11:33    [22332155]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
Kolu4ka
Member

Откуда:
Сообщений: 51
0wl, спасибо,
читала еще топик
[url=]https://www.sql.ru/forum/1320821/insert-with-tablock[/url]

Конечный вывод делаю для себя такой, если в свою таблицу я заливаю все данные одним insert(одна интерация) и дальнейшие операции с таблицей будут только по кластерному полю, то имеет смысл создавать кластерный индекс сначала и использовать tablock при вставке.

Да, еще вопросик, если при работе с такой таблицей мне понадобится удалять записи, правильнее сделать поле для признака удаления и update его, что бы таблица не перестраивалась? Или можно сразу удалять? К примеру для удаления 10 из 50 тыс.
7 июн 21, 12:35    [22332207]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
aleks222
Member

Откуда:
Сообщений: 1414
Kolu4ka

Да, еще вопросик, если при работе с такой таблицей мне понадобится удалять записи, правильнее сделать поле для признака удаления и update его, что бы таблица не перестраивалась? Или можно сразу удалять? К примеру для удаления 10 из 50 тыс.


Правильнее - не вставлять ненужное.
Двойной кэшбэк.
7 июн 21, 12:50    [22332219]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
aleks222
Member

Откуда:
Сообщений: 1414
Kolu4ka

Конечный вывод делаю для себя такой, если в свою таблицу я заливаю все данные одним insert(одна интерация) и дальнейшие операции с таблицей будут только по кластерному полю, то имеет смысл создавать кластерный индекс сначала и использовать tablock при вставке.


Вот скока не мучился - так и не пришел к однозначному выводу "что быстрее"

select ... into #t from ...;
alter table #t add primary key (...) 


insert #t with(tablockx) (...)
select ... from ... order by ...


Но второй вариант сильно проигрывает по необходимости явно писать
create table #t( поля...)
7 июн 21, 12:56    [22332226]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
Кесарь
Member

Откуда:
Сообщений: 651
Kolu4ka
Дилетантский вопрос.
Создаем временную таблицу на которую нужен кластерный индекс, записей будет пусть 50к
По производительности лучше кластерный индекс до вставки создать или после?

По моему мнению быстрее получится сначала создать, а потом вставить данные. Т.к. данные уже будут вставляться в нужном порядке, но нужна будет предварительная сортировка.
Если иначе, то будет создана куча, которую потом также нужно будет сортировать и перестраивать данные.


На этот вопрос есть только один строгий ответ: смотрите время выполнения в том и другом случаях и сравнивайте планы выполнения.


Что же касается рекомендаций вообще, то имейте ввиду, что создание индекса на временной таблице - это DDL операция. Из-за которой меняется схема данных. И код процедуры будет перекомпилирован. Это не всегда плохо, но надо иметь это в виду.

Поэтому если вы хотите избежать перекомпиляций, делайте объявление индекса внутри объявления временной таблицы, а не отдельной командой. И само собой в самом начале процедуры. Если вы не уверены на счёт перекомпиляций, то делайте так, чтобы их избежать, всегда.
7 июн 21, 12:57    [22332228]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
Кесарь
Member

Откуда:
Сообщений: 651
aleks222
Kolu4ka

Конечный вывод делаю для себя такой, если в свою таблицу я заливаю все данные одним insert(одна интерация) и дальнейшие операции с таблицей будут только по кластерному полю, то имеет смысл создавать кластерный индекс сначала и использовать tablock при вставке.


Вот скока не мучился - так и не пришел к однозначному выводу "что быстрее"

select ... into #t from ...;
alter table #t add primary key (...) 


insert #t with(tablockx) (...)
select ... from ... order by ...


Но второй вариант сильно проигрывает по необходимости явно писать
create table #t( поля...)


В серьёзных компаниях такой вопрос даже не стоит. Все поля всегда должны быть описаны явно.
7 июн 21, 12:59    [22332229]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8690
Алекс прав, особой разницы в производительности нет, но пространства в базе, я предполагаю, будет затрачено больше в случае "перелива". Если добавление записей происходит не одной командой, то выгоднее по времени будет вариант с финальным созданием кластерного индекса.
7 июн 21, 13:04    [22332231]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
Кесарь
Member

Откуда:
Сообщений: 651
Kolu4ka
Да, еще вопросик, если при работе с такой таблицей мне понадобится удалять записи, правильнее сделать поле для признака удаления и update его, что бы таблица не перестраивалась? Или можно сразу удалять? К примеру для удаления 10 из 50 тыс.


Это зависит от многих обстоятельств. От размера таблицы и загрузки сервера например. Если у вас многие миллионы записей, то было бы неплохо такую таблицу уменьшить.

Если очистка памяти не является необходимой, то лучше не удалять, а делать дополнительное поле и по нему индекс.
7 июн 21, 13:06    [22332235]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
Кесарь
Member

Откуда:
Сообщений: 651
Владислав Колосов
Алекс прав, особой разницы в производительности нет, но пространства в базе, я предполагаю, будет затрачено больше в случае "перелива". Если добавление записей происходит не одной командой, то выгоднее по времени будет вариант с финальным созданием кластерного индекса.


Одно мне тут непонятно: а зачем тогда создавать именно кластерный индекс?
7 июн 21, 13:08    [22332236]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
aleks222
Member

Откуда:
Сообщений: 1414
Кесарь

В серьёзных компаниях такой вопрос даже не стоит. Все поля всегда должны быть описаны явно.


Удивительное дело.
А шо select ... into #t "неописывает поля"?
Вроде все однозначно описывается из исходных таблиц.
Более того, если базовые таблицы переопределены - не надо метаться по процедурам, правя определения времянок.
7 июн 21, 13:25    [22332256]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
invm
Member

Откуда: Москва
Сообщений: 9772
Кесарь
Что же касается рекомендаций вообще, то имейте ввиду, что создание индекса на временной таблице - это DDL операция. Из-за которой меняется схема данных. И код процедуры будет перекомпилирован. Это не всегда плохо, но надо иметь это в виду.

Поэтому если вы хотите избежать перекомпиляций, делайте объявление индекса внутри объявления временной таблицы, а не отдельной командой. И само собой в самом начале процедуры. Если вы не уверены на счёт перекомпиляций, то делайте так, чтобы их избежать, всегда.
Описанное сильно устарело, причем уже давно.
https://sqlperformance.com/2017/05/sql-performance/sql-server-temporary-object-caching
7 июн 21, 13:38    [22332267]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
Кесарь
Member

Откуда:
Сообщений: 651
invm
Кесарь
Что же касается рекомендаций вообще, то имейте ввиду, что создание индекса на временной таблице - это DDL операция. Из-за которой меняется схема данных. И код процедуры будет перекомпилирован. Это не всегда плохо, но надо иметь это в виду.

Поэтому если вы хотите избежать перекомпиляций, делайте объявление индекса внутри объявления временной таблицы, а не отдельной командой. И само собой в самом начале процедуры. Если вы не уверены на счёт перекомпиляций, то делайте так, чтобы их избежать, всегда.
Описанное сильно устарело, причем уже давно.
https://sqlperformance.com/2017/05/sql-performance/sql-server-temporary-object-caching


Немного не понял, что из написанного мной устарело, если по ссылке ровно оно самое?

"To be cached, a temporary object additionally must not:
Perform "DDL" after object creation"

"Be sure to meet the conditions for temporary table caching, which most often means not creating indexes or statistics after the initial table creation statement. This is made more convenient from SQL Server 2014 onward due to the introduction of the INDEX clause of the CREATE TABLE statement."
7 июн 21, 14:09    [22332297]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
Кесарь
Member

Откуда:
Сообщений: 651
aleks222
Кесарь

В серьёзных компаниях такой вопрос даже не стоит. Все поля всегда должны быть описаны явно.


Удивительное дело.
А шо select ... into #t "неописывает поля"?
Вроде все однозначно описывается из исходных таблиц.
Более того, если базовые таблицы переопределены - не надо метаться по процедурам, правя определения времянок.


Возможность переопределения базовых таблиц как бы ведёт к тому, что схема данных нестабильна. А значит будет перекомпиляция. Т.е. select into это DML и DDLв одном флаконе. Именно поэтому этого и нужно избегать.
7 июн 21, 14:12    [22332301]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
aleks222
Member

Откуда:
Сообщений: 1414
Кесарь
aleks222
пропущено...


Удивительное дело.
А шо select ... into #t "неописывает поля"?
Вроде все однозначно описывается из исходных таблиц.
Более того, если базовые таблицы переопределены - не надо метаться по процедурам, правя определения времянок.


Возможность переопределения базовых таблиц как бы ведёт к тому, что схема данных нестабильна. А значит будет перекомпиляция. Т.е. select into это DML и DDLв одном флаконе. Именно поэтому этого и нужно избегать.


Я ЗА!
Только вот эти скотины-пользователи...

ЗЫ. Может хватит вещать прописные лозунги?
7 июн 21, 14:19    [22332308]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
invm
Member

Откуда: Москва
Сообщений: 9772
Кесарь,

Процедуры уже давно не перекомпилируются. Перекомпилируются инструкции в процедуре.
Какие именно и как это зависит от кешируемости временных объектов можете посмотреть выполнив указанный ниже простой пример с отслеживанием SQL:StmtRecompile
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create procedure dbo.p1
as
begin
 declare @c int;
 select @c = count(*) from master.dbo.spt_values;

 create table #p1 (a int not null primary key clustered, b int);
 select @c = checksum_agg(checksum(*)) from #p1 where a = 10;
end;
go

create procedure dbo.p2
as
begin
 declare @c int;
 select @c = count(*) from master.dbo.spt_values;

 create table #p2 (a int not null, b int);
 alter table #p2 add primary key clustered (a);
 select @c = checksum_agg(checksum(*)) from #p2 where a = 10;
end;
go

exec dbo.p1;
exec dbo.p1;
go

exec dbo.p2;
exec dbo.p2;
go

drop procedure dbo.p1, dbo.p2;
go
7 июн 21, 14:52    [22332334]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный индекс до или после.  [new]
StarikNavy
Member

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

прощу уже было написать два скрипта и самому проверить по времени )
8 июн 21, 15:03    [22332928]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить