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

Откуда: Львов
Сообщений: 504
Приветствую,
Стоит задача создать темповую таблицу с кластерным индексом и 10 млн. записей. Всегда думал что самый быстрый вариант такой:
CREATE TABLE #t(id INT NOT NULL PRIMARY KEY CLUSTERED, a INT, b VARCHAR(100), c DATE)
INSERT INTO #t(id, a, b, c)
SELECT id, a, b, c FROM ...


Но! Был сильно обламан и удивлен когда в 2014 версии быстрее оказался вариант с двойной перегрузкой данных:
INSERT INTO #t(id, a, b, c)
SELECT id, a, b, c INTO #t FROM ...
ALTER TABLE #t ADD PRIMARY KEY CLUSTERED (id);


Версия:
Microsoft SQL Server 2014 - 12.0.2480.0 (X64) 
	Jan 28 2015 18:53:20 
	Copyright (c) Microsoft Corporation
	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )


Вопрос. Почему сортировка и перегрузка данных с кучи (heap) в таблицу с кластерным индексом оказывается быстрей чем просто загрузка в таблицу с кластерным индексом?
9 дек 16, 09:48    [19983285]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
Извините, поправил второй вариант:
SELECT id, a, b, c INTO #t FROM ...
ALTER TABLE #t ADD PRIMARY KEY CLUSTERED (id);
9 дек 16, 09:51    [19983295]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
aleks2
Guest
Богдан Гоцкий
Извините, поправил второй вариант:
SELECT id, a, b, c INTO #t FROM ...
ALTER TABLE #t ADD PRIMARY KEY CLUSTERED (id);


Это документировано аж с MS SQL 7.0, т.е. с мохнатого 1998г.
SELECT ... INTO #t FROM ...

минимально логгируемая операция.
9 дек 16, 10:14    [19983391]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
aleks2
Guest
Т.е. по факту, в журнал НИЧЕГО не пишется.
А запись в журнал - это дорого.
9 дек 16, 10:17    [19983402]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
да, но вот
ALTER TABLE #t ADD PRIMARY KEY CLUSTERED (id);

никак не минимально логируема, и в бекграунде это переливка 10 лямов записей с кучи в кластеред индекс. По сути по времени она уже должна быть еквивалентна с первым вариантом
9 дек 16, 10:21    [19983423]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
aleks2
Guest
Богдан Гоцкий
да, но вот
ALTER TABLE #t ADD PRIMARY KEY CLUSTERED (id);

никак не минимально логируема, и в бекграунде это переливка 10 лямов записей с кучи в кластеред индекс. По сути по времени она уже должна быть еквивалентна с первым вариантом


1. Кто те сказал, что "переливка"?
2. Типа при заполнении сразу шуршать страницами не надо?
9 дек 16, 12:51    [19984341]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
Богдан Гоцкий,

И select into #table, и insert into #table select минимально журналируются.

Описанная проблема возникает, например, при распараллеливании:
set nocount on;

if object_id('tempdb..#a', 'U') is not null
 drop table #a;

if object_id('tempdb..#s', 'U') is not null
 drop table #s;

create table #a (id bigint primary key, s char(200));

select top (1000000)
 row_number() over (order by (select 1)) as id, cast('a' as char(200)) as s
into
 #s
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

set statistics xml, time on;
insert into #a
select id, s from #s option (maxdop 1);
set statistics xml, time off;
go

truncate table #a;
go

set statistics xml, time on;
insert into #a
select id, s from #s;
set statistics xml, time off;
go
/*
 Время работы SQL Server:
   Время ЦП = 1295 мс, затраченное время = 5919 мс.

 Время работы SQL Server:
   Время ЦП = 2793 мс, затраченное время = 13000 мс.
*/
9 дек 16, 13:03    [19984453]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
+

set nocount on;

if object_id('tempdb..#a', 'U') is not null
 drop table #a;

if object_id('tempdb..#s', 'U') is not null
 drop table #s;

create table #a (id bigint primary key, s char(200));

select top (1000000)
 row_number() over (order by (select 1)) as id, cast('a' as char(200)) as s
into
 #s
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

set statistics xml, time on;
insert into #a
select id, s from #s
option (maxdop 1,recompile);
set statistics xml, time off;
go

truncate table #a;
go

set statistics xml, time on;
insert into #a
select id, s from #s
option (recompile);
set statistics xml, time off;
go

truncate table #a;
go

set statistics xml, time on;
insert into #a with ( tablockx )
select id, s from #s
option (maxdop 1,recompile);
set statistics xml, time off;
go

truncate table #a;
go

set statistics xml, time on;
insert into #a with ( tablockx )
select id, s from #s
option (recompile);
set statistics xml, time off;
go

truncate table #a;
go


НА второй прогон дало результат:
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 9 ms.

 SQL Server Execution Times:
   CPU time = 3760 ms,  elapsed time = 4192 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 6100 ms,  elapsed time = 4111 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 3666 ms,  elapsed time = 4040 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 3 ms.

 SQL Server Execution Times:
   CPU time = 5694 ms,  elapsed time = 2484 ms.

Что я делаю/понимаю не так?
9 дек 16, 13:12    [19984530]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
aleks2
Guest
invm
И select into #table, и insert into #table select минимально журналируются.



Наивняк.

insert into #table select

может быть из ДВУХ транзакций.
Хрен ты откатишь ОДНУ из без журналирования.
9 дек 16, 13:16    [19984554]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
aleks2
insert into #table select

может быть из ДВУХ транзакций.
И что за транзакции?
9 дек 16, 13:28    [19984651]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
invm
Вопрос: И что за транзакции?

Ответ: Remote Distributed Transaction? предположение.
9 дек 16, 13:30    [19984666]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
invm
Member

Откуда: Москва
Сообщений: 9401
Руслан Дамирович
Ответ: Remote Distributed Transaction? предположение.
Круто. А где же в этом случае две транзакции?
9 дек 16, 13:38    [19984722]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
o-o
Guest
invm
Руслан Дамирович
Ответ: Remote Distributed Transaction? предположение.
Круто. А где же в этом случае две транзакции?

лучше так: и кто же у нас remote?
ТС во временную таблицу вставляет,
неужто на другом сервере у него tempdb?
9 дек 16, 13:50    [19984770]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
aleks2
Богдан Гоцкий
да, но вот
ALTER TABLE #t ADD PRIMARY KEY CLUSTERED (id);

никак не минимально логируема, и в бекграунде это переливка 10 лямов записей с кучи в кластеред индекс. По сути по времени она уже должна быть еквивалентна с первым вариантом


1. Кто те сказал, что "переливка"?
2. Типа при заполнении сразу шуршать страницами не надо?


1. Актуальный план сказал что переливка (чтение, сортировка, вставка)

К сообщению приложен файл. Размер - 16Kb
9 дек 16, 14:01    [19984822]     Ответить | Цитировать Сообщить модератору
 Re: Как быстрее приготовить временную таблицу?  [new]
iljy
Member

Откуда:
Сообщений: 8711
Богдан Гоцкий
да, но вот
ALTER TABLE #t ADD PRIMARY KEY CLUSTERED (id);

никак не минимально логируема


MSDN
If the database is set to the simple or bulk-logged recovery model, some index DDL operations are minimally logged whether the operation is executed offline or online. The minimally logged index operations are as follows:
CREATE INDEX[/b] operations (including indexed views).
ALTER INDEX REBUILD or DBCC DBREINDEX operations.


Так что вполне может.

Вообще говоря, INSERT SELECT тоже может быть минимально логируема

MSDN
Table Requirements for Minimally Logging Bulk-Import Operations
Minimal logging requires that the target table meets the following conditions:
  • The table is not being replicated.
  • Table locking is specified (using TABLOCK). For table with clustered columnstore index, you don't need TABLOCK for minimal logging. Additionally, only the data load into compressed rowgroups are minimally logged requiring a batchsize of 102400 or higher.

    Whether minimal logging can occur for a table also depends on whether the table is indexed and, if so, whether the table is empty:
  • If the table has no indexes, data pages are minimally logged.
  • If the table has no clustered index but has one or more nonclustered indexes, data pages are always minimally logged. How index pages are logged, however, depends on whether the table is empty:
  • If the table is empty, index pages are minimally logged.
  • If table is non-empty, index pages are fully logged.


  • If you start with an empty table and bulk import the data in multiple batches, both index and data pages are minimally logged for the first batch, but beginning with the second batch, only data pages are minimally logged.
  • If the table has a clustered index and is empty, both data and index pages are minimally logged. In contrast, if a table has a btree based clustered index and is non-empty, data pages and index pages are both fully logged regardless of the recovery model. For tables with clustered columnstore index, the dataload into compressed rowgroup is always minimally logged independent of the table being empty or not when batchsize >= 102400.
  • 9 дек 16, 14:28    [19984958]     Ответить | Цитировать Сообщить модератору
     Re: Как быстрее приготовить временную таблицу?  [new]
    Богдан Гоцкий
    Member

    Откуда: Львов
    Сообщений: 504
    iljy,

    Да, скорее всего вы правы. И перестройка кучи в кластерный индекс:
    ALTER TABLE #t ADD PRIMARY KEY CLUSTERED (id)
    

    действительно минимально логируема операция. Следующий скрипт показывает это:
    use tempdb
    go
    
    if object_id('tempdb..#t') is not null
    	drop table #t
    
    select top (1000000)
    	isnull(row_number() over (order by (select 1)), 0) as id, cast('a' as char(200)) as s
    into #t
    from
    	 master.dbo.spt_values a cross join
    	 master.dbo.spt_values b;
    go
    
    checkpoint
    go
    
    alter table #t add primary key clustered (id)
    go
    
    select l.* from fn_dblog(null, null) l
    left join sys.system_internals_allocation_units au on au.allocation_unit_id = l.AllocUnitId
    left join sys.partitions p on p.partition_id = au.container_id
    where
    	p.object_id = object_id('tempdb..#t')
    


    Как видно, после выполнения
    alter table #t add primary key clustered (id)
    
    в лог логируются только операции модификации хедеров PFS страниц. И больше ничего! Никаких LOP_INSERT_ROWS !

    OperationContext
    LOP_SET_BITSLCX_IAM
    LOP_SET_BITSLCX_GAM
    LOP_MODIFY_ROWLCX_PFS


    В общем, разобрался. Спасибо всем.
    9 дек 16, 15:18    [19985226]     Ответить | Цитировать Сообщить модератору
     Re: Как быстрее приготовить временную таблицу?  [new]
    aleks2
    Guest
    Руслан Дамирович
    invm
    Вопрос: И что за транзакции?

    Ответ: Remote Distributed Transaction? предположение.


    Какие вы сложносочиненные...

    invm
    aleks2
    insert into #table select

    может быть из ДВУХ транзакций.
    И что за транзакции?


    Проще надо быть.
    begin transaction
    insert into #table select 
    commit transaction
    
    begin transaction
    insert into #table select 
    rollback transaction
    
    9 дек 16, 15:34    [19985309]     Ответить | Цитировать Сообщить модератору
     Re: Как быстрее приготовить временную таблицу?  [new]
    Гавриленко Сергей Алексеевич
    Member

    Откуда: Moscow
    Сообщений: 37061
    aleks2
    Проще надо быть.
    Если таблица на начало транзакции пуста, то достаточно залогировать выделение страниц. А если таблица является кучей без индексов, то и второй insert можно логировать таким же образом, если вставлять данные в новую страницу.
    9 дек 16, 15:47    [19985372]     Ответить | Цитировать Сообщить модератору
     Re: Как быстрее приготовить временную таблицу?  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9401
    aleks2
    Проще надо быть.
    Надо быть умнее и учить матчасть, а не шастать по астралу.
    +
    use tempdb;
    set nocount on;
    
    if object_id('tempdb..#a', 'U') is not null
     drop table #a;
    
    if object_id('tempdb..#s', 'U') is not null
     drop table #s;
    
    create table #a (id bigint primary key, s char(2000));
    
    select top (200000)
     row_number() over (order by (select 1)) as id, cast('a' as char(200)) as s
    into
     #s
    from
     master.dbo.spt_values a cross join
     master.dbo.spt_values b;
    go
    
    insert into #a values (0, 'a');
    go
    
    begin tran t1;
    insert into #a select top (100002) id, s from #s;
    
    declare @tid sysname;
    select top (1) @tid = [Transaction ID] from sys.fn_dblog(null, null) where [Transaction Name] = N't1' order by [Current LSN] desc;
    select Operation, Context, AllocUnitName, count(*), sum([Log Record Length]) from sys.fn_dblog(null, null) where [Transaction ID] = @tid group by Operation, Context, AllocUnitName;
    commit;
    go
    
    begin tran t1;
    insert into #a select id, s from #s where id > 100002;
    
    declare @tid sysname;
    select top (1) @tid = [Transaction ID] from sys.fn_dblog(null, null) where [Transaction Name] = N't1' order by [Current LSN] desc;
    select Operation, Context, AllocUnitName, count(*), sum([Log Record Length]) from sys.fn_dblog(null, null) where [Transaction ID] = @tid group by Operation, Context, AllocUnitName;
    commit;
    go
    
    9 дек 16, 16:33    [19985629]     Ответить | Цитировать Сообщить модератору
     Re: Как быстрее приготовить временную таблицу?  [new]
    aleks2
    Guest
    invm
    aleks2
    Проще надо быть.
    Надо быть умнее и учить матчасть, а не шастать по астралу.

    К чему этот бессмысленный пример?
    У тредстартера НЕ КУЧА.

    CREATE TABLE #t(id INT NOT NULL PRIMARY KEY CLUSTERED, a INT, b VARCHAR(100), c DATE)
    
    10 дек 16, 11:24    [19987535]     Ответить | Цитировать Сообщить модератору
     Re: Как быстрее приготовить временную таблицу?  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9401
    aleks2
    К чему этот бессмысленный пример?
    У тредстартера НЕ КУЧА.
    Примеры тоже нужно уметь читать. Впрочем, и документацию неплохо бы почитывать.
    Ну и глазки протирать перед чтением того и другого.
    10 дек 16, 12:11    [19987617]     Ответить | Цитировать Сообщить модератору
     Re: Как быстрее приготовить временную таблицу?  [new]
    uaggster
    Member

    Откуда:
    Сообщений: 864
    aleks2
    Богдан Гоцкий
    Извините, поправил второй вариант:
    SELECT id, a, b, c INTO #t FROM ...
    ALTER TABLE #t ADD PRIMARY KEY CLUSTERED (id);
    


    Это документировано аж с MS SQL 7.0, т.е. с мохнатого 1998г.
    SELECT ... INTO #t FROM ...

    минимально логгируемая операция.

    Угу. Минимально то она логгируема, только не заблокирует ли она tempdb на время вставки всего миллиона записей?
    12 дек 16, 15:24    [19993894]     Ответить | Цитировать Сообщить модератору
     Re: Как быстрее приготовить временную таблицу?  [new]
    aleks2
    Guest
    uaggster
    aleks2
    пропущено...


    Это документировано аж с MS SQL 7.0, т.е. с мохнатого 1998г.
    SELECT ... INTO #t FROM ...

    минимально логгируемая операция.

    Угу. Минимально то она логгируема, только не заблокирует ли она tempdb на время вставки всего миллиона записей?

    Изыди, неуч.
    12 дек 16, 15:27    [19993914]     Ответить | Цитировать Сообщить модератору
     Re: Как быстрее приготовить временную таблицу?  [new]
    uaggster
    Member

    Откуда:
    Сообщений: 864
    aleks2
    uaggster
    пропущено...

    Угу. Минимально то она логгируема, только не заблокирует ли она tempdb на время вставки всего миллиона записей?

    Изыди, неуч.

    Чо изыди то?
    Если Select into #tmp сделать внутри хранимой процедуры и обернуть в транзакцию - то вполне себе заблокирует.
    А Create + insert into select - нет.
    Разве не так?
    :-)
    12 дек 16, 15:36    [19993958]     Ответить | Цитировать Сообщить модератору
     Re: Как быстрее приготовить временную таблицу?  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6801
    uaggster
    aleks2
    пропущено...

    Изыди, неуч.

    Чо изыди то?
    Если Select into #tmp сделать внутри хранимой процедуры и обернуть в транзакцию - то вполне себе заблокирует.
    А Create + insert into select - нет.
    Разве не так?
    :-)

    заблокирует tempdb?

    автор
    сделать внутри хранимой процедуры и обернуть в транзакцию -
    и одеялом накрыть
    12 дек 16, 15:56    [19994104]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить