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

Откуда:
Сообщений: 234
Добрый день.
Подскажи, пжл, следующий вопрос.
Необходимо перезалить данные из таблицы в новую таблицу (структуры одинаковы). Записей очень много (миллиарды).
В таблице есть поле Id int identity(1,1) primary key clustered.

Пытался выполнять операцию 2-мя путями.
1. с использованим опции set identity_insert on, т.е. с сохранением значений Id
2. без использования опции set identity_insert on, т.е. с новой инкрементальной генерацией.

Оказалось, что при использовании set identity_insert on производительность значительно хуже, т.к. записи сортируются перед вставкой. Т.е перезаливка по варианту №1 по производительности значительно проигрывает №2.

Вопрос. Можно ли как-то избежать сортировок при варианте №1, чтобы сравнять производительность двух подходов. Как-нибудь намекнуть, что зачитанные данные уже отсортированы по clusterd field id.

Или же подход №2 всего будет более производительней.

Заранее благодарен за Ваши подсказки.
28 фев 14, 01:50    [15643782]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
Павел-П,

а поточнее покажите что и как вы делаете
мне кажется что сортировка обязана происходить по причине кластерного индекса в целевой таблице, а не потому что там identity
может его можно создать позже?
или может можно после инсерта по 2.
попробовать проапдейтить id на целевой таблице?

чисто теоретически, просто как идея, при действительно громадных таблицах
напрашивается что-то типа backup/restore той области где таблица лежит
типа скопипастить файл данных вместо возни с записями
28 фев 14, 03:11    [15643885]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
leov
а поточнее покажите что и как вы делаете
мне кажется что сортировка обязана происходить по причине кластерного индекса в целевой таблице, а не потому что там identity
+1
Покажите план запроса, а то как-то странно что он читая упорядоченные данные дополнительно "упорядочивает". ИМХО баг, если нет обратного.
leov
может его можно создать позже?
+1
Навесить свойство Identity можно и позже. Но тоже может быть косяк с планом, надеюсь что нет.

leov
что-то типа backup/restore той области где таблица лежит
типа скопипастить файл данных вместо возни с записями
1. Такого нет.
2. BULK insert (+ кажись Simple Log) есть итак и разниц не сильно большая в итоге. Ибо целостность и согласованность базы на первом месте палюбэ.

Просто перезалить таблицу? Мне кажется это жжж не спроста ...
Сменить файловую группу можно и без перезаливки, или у вас другие причины?
28 фев 14, 04:32    [15643922]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Собственно говоря скрипты очень просты.
Поле MonthEndFactMKey - primary key clustered int identity(1,1)
Надо тупо перезалить данные из одной таблицы в новую таблицу.

1. Вариант №1 с использованием опции set identity_insert и сохранением значения суррогатного ключа MonthEndFactMKey

set identity_insert [dbo].[MonthEndFact_tmp] on

insert into [dbo].[MonthEndFact_tmp]
(MonthEndFactMKey, DWAcctID, FiscalMthBeginDateMkey, FiscalMthEndDateMkey, CreditAccountMkey)
select MonthEndFactMKey, DWAcctID, FiscalMthBeginDateMkey, FiscalMthEndDateMkey, CreditAccountMkey
from [dbo].[MonthEndFact]
where MonthEndFactMKey between 1 and 10000000
order by 1

set identity_insert [dbo].[MonthEndFact_tmp] off

2. Вариант №2 с новой генерацией MonthEndFactMKey (сурогатный ключ)

insert into [dbo].[MonthEndFact_tmp]
(DWAcctID, FiscalMthBeginDateMkey, FiscalMthEndDateMkey, CreditAccountMkey)
select top 10000000 DWAcctID, FiscalMthBeginDateMkey, FiscalMthEndDateMkey, CreditAccountMkey
from [dbo].[MonthEndFact]

Производительность вариантов разная, как я умопянул изначально из-за сортировки в первом варианте.

План запроса для варианта №1 присоединен к сообщению в виде картинки. Там четка видна сортировка, которая и приводит к существенному падению производительности по сравнению с вариантом №2.

К сообщению приложен файл. Размер - 73Kb
28 фев 14, 22:07    [15649776]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31431
Павел-П
Надо тупо перезалить данные из одной таблицы в новую таблицу.
Как уже говорили, сождайте таблицу без ПК и без идентити, залейте данные запросом без сортировки, потом создайте ПК и установите свойство идентити. Производительность будет как во втором случае.
28 фев 14, 22:12    [15649797]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Mnior
Навесить свойство Identity можно и позже.


Вот тут очень интересно стало как вы это свойство навесите позже. Его менять можно только пересозданием таблицы.
Да и как это поможет производительности всей операции в целом тоже непонятно.
28 фев 14, 22:13    [15649800]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Mnior
Просто перезалить таблицу? Мне кажется это жжж не спроста ...
Сменить файловую группу можно и без перезаливки, или у вас другие причины?


Мне действительно просто по требованиям надо перезалить таблицу. Из одной сделать 2-е.
Просто стало очень интересно, что 2 подхода очень похожим между собой (с сохранением старых identity значений и без сохранения) по производительности ведут себя очень поразному. В моем случае падение произовдительности в 2 раза.

Просто может кто-нибудь сможет подсказать как перезалить данные еще более эффективнее.
28 фев 14, 22:17    [15649808]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
alexeyvg
Павел-П
Надо тупо перезалить данные из одной таблицы в новую таблицу.
Как уже говорили, сождайте таблицу без ПК и без идентити, залейте данные запросом без сортировки, потом создайте ПК и установите свойство идентити. Производительность будет как во втором случае.


Ну мне еще PK кластерным иметь надо. Как понимаете, создать clustered PK на большой таблице будет проблема.
Ну а как навесить identity без очередной перезаливки вообще не представляю.
http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/

А если еще раз перезаливать - какой тут выйгрышь в производительности
28 фев 14, 22:22    [15649824]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37052
Seek в первом запросе поди не упорядоченный, потому и Sort в плане, ибо order by + необходимость отсортировать в порядке ключа в таблице-приемнике. Во втором запросе стоит top без order by (что уже нонсенс), потому и нет sort.

Короче, проблема, похоже, не в identity_insert, а в структуре исходной таблицы.

Сообщение было отредактировано: 28 фев 14, 22:42
28 фев 14, 22:40    [15649900]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Гавриленко Сергей Алексеевич
Seek в первом запросе поди не упорядоченный, потому и Sort в плане, ибо order by + необходимость отсортировать в порядке ключа в таблице-приемнике.


А можно предложить как его сделать упорядоченным? Если конечно вообще можно.


Гавриленко Сергей Алексеевич
Во втором запросе стоит top без order by (что уже нонсенс), потому и нет sort.


Это просто следы оставшихся экспериментов. Ну напишем where MonthEndFactMKey between 1 and 10000000. Сортировки тоже нет :-)

insert into [dbo].[MonthEndFact_tmp]
(DWAcctID, FiscalMthBeginDateMkey, FiscalMthEndDateMkey, CreditAccountMkey)
select DWAcctID, FiscalMthBeginDateMkey, FiscalMthEndDateMkey, CreditAccountMkey
from [dbo].[MonthEndFact]
where MonthEndFactMKey between 1 and 10000000

Гавриленко Сергей Алексеевич
Короче, проблема, похоже, не в identity_insert, а в структуре исходной таблицы.


Да нет никакой проблемы в структуре таблице. Я думаю, вы и у себя такую же таблицу можете создать без всякиз проблем.
Поле Id int identity(1,1) primary key clustered. Ну и пару полей добавьте для красоты.
Потом залейте туда немного данных и попробуйте перелить как показано в примерах выше.
Получите тот же самый результат
28 фев 14, 23:13    [15650070]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Гавриленко Сергей Алексеевич
Seek в первом запросе поди не упорядоченный


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

Вообщем как я понимаю, identity_insert свойство тут не причем.
Просто любая попытка вставки прямых значений в clustered index будет приводить к необходимости начальной сортировки.
Вот и получается, что перезаливка с пересчетом (генарацией) первичного суррогатного ключа (в моем случае idenity колонки) будет работать много быстрее чем, чем с явным указанием identity_insert.

К сообщению приложен файл. Размер - 78Kb
1 мар 14, 00:02    [15650342]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37052
Павел-П
Нет ну он упорядоченный, только вот по чем и это ничему не помогает.
Тогда и правда, засада. Скуль раньше любил "забывать" между операциями, что все уже отсортировано. Тут та же болезнь?
1 мар 14, 00:18    [15650431]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Гавриленко Сергей Алексеевич
Павел-П
Нет ну он упорядоченный, только вот по чем и это ничему не помогает.
Тогда и правда, засада. Скуль раньше любил "забывать" между операциями, что все уже отсортировано. Тут та же болезнь?


Судя по всему да. Вот я и решил тут поспрашивать, это можно победить или нет. Может магия какая-то есть.
Просто операция перезаливки больших объемов у людей должна часто встречаться, ну и ее производительностью думаю люди тоже озадачивались.
В любом случае спасибо Вам за обсуждение и участие.
1 мар 14, 00:27    [15650463]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Да, кстатьи, немного об окружении.

Microsoft SQL Server 2008 (SP2) - 10.0.4371.0 (X64)
Sep 5 2012 21:21:32
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)
1 мар 14, 00:37    [15650495]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37052
Павел-П
Гавриленко Сергей Алексеевич
пропущено...
Тогда и правда, засада. Скуль раньше любил "забывать" между операциями, что все уже отсортировано. Тут та же болезнь?


Судя по всему да. Вот я и решил тут поспрашивать, это можно победить или нет. Может магия какая-то есть.
Просто операция перезаливки больших объемов у людей должна часто встречаться, ну и ее производительностью думаю люди тоже озадачивались.
В любом случае спасибо Вам за обсуждение и участие.

На самом деле не да, ибо на коленке не воспроизводится.

set nocount on

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

if object_id('tempdb..#d') is not null
    drop table #d

create table #s (   
    id      int             not null    primary key
    , name  varchar (50)    not null    
)

insert #s ( id, name )
            select 1, '1'
union all   select 2, '2'
union all   select 3, '3'

create table #d (   
    id      int             not null    identity primary key
    , name  varchar (50)    not null    
)

set statistics profile on 

insert #d (name) select  name from #s a where a.id > 1  order by a.id

truncate table #d

set identity_insert #d on

insert #d (id, name) select id, name from #s a where a.id > 1  order by a.id

set identity_insert #d off

set statistics profile off 
--------------------------------------------------------------------

Rows                 Executes             StmtText                                                                                                                                                                      StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                         DefinedValues                                 EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList              Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------- -------- ---------------------------------------------------------------- -------- ------------------
2                    1                    insert #d (name) select  name from #s a where a.id > 1  order by a.id                                                                                                         2           1           0           NULL                           NULL                           NULL                                                                                                                                             NULL                                          2             NULL          NULL          NULL        0,0132866        NULL                    NULL     INSERT                                                           0        NULL
2                    1                      |--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#d]), SET:([tempdb].[dbo].[#d].[name] = [tempdb].[dbo].[#s].[name] as [a].[name],[tempdb].[dbo].[#d].[id] = [Expr1005]))  2           2           1           Clustered Index Insert         Insert                         OBJECT:([tempdb].[dbo].[#d]), SET:([tempdb].[dbo].[#d].[name] = [tempdb].[dbo].[#s].[name] as [a].[name],[tempdb].[dbo].[#d].[id] = [Expr1005])  NULL                                          2             0,01          2E-06         9           0,0132866        NULL                    NULL     PLAN_ROW                                                         0        1
2                    1                           |--Compute Scalar(DEFINE:([Expr1005]=getidentity((819300079),(2),NULL)))                                                                                               2           3           2           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1005]=getidentity((819300079),(2),NULL))                                                                                            [Expr1005]=getidentity((819300079),(2),NULL)  2             0             2E-07         40          0,0032846        [a].[name], [Expr1005]  NULL     PLAN_ROW                                                         0        1
2                    1                                |--Top(ROWCOUNT est 0)                                                                                                                                            2           4           3           Top                            Top                            TOP EXPRESSION:((0))                                                                                                                             NULL                                          2             0             2E-07         40          0,0032844        [a].[id], [a].[name]    NULL     PLAN_ROW                                                         0        1
2                    1                                     |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#s] AS [a]), SEEK:([a].[id] > (1)) ORDERED FORWARD)                                                          2           5           4           Clustered Index Seek           Clustered Index Seek           OBJECT:([tempdb].[dbo].[#s] AS [a]), SEEK:([a].[id] > (1)) ORDERED FORWARD                                                                       [a].[id], [a].[name]                          2             0,003125      0,0001592     40          0,0032842        [a].[id], [a].[name]    NULL     PLAN_ROW                                                         0        1


Rows                 Executes             StmtText                                                                                                                                                                      StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                         DefinedValues                                                                      EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList              Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------- -------- ---------------------------------------------------------------- -------- ------------------
2                    1                    insert #d (id, name) select id, name from #s a where a.id > 1  order by a.id                                                                                                  4           1           0           NULL                           NULL                           NULL                                                                                                                                             NULL                                                                               2             NULL          NULL          NULL        0,0132866        NULL                    NULL     INSERT                                                           0        NULL
2                    1                      |--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#d]), SET:([tempdb].[dbo].[#d].[id] = [Expr1005],[tempdb].[dbo].[#d].[name] = [tempdb].[dbo].[#s].[name] as [a].[name]))  4           2           1           Clustered Index Insert         Insert                         OBJECT:([tempdb].[dbo].[#d]), SET:([tempdb].[dbo].[#d].[id] = [Expr1005],[tempdb].[dbo].[#d].[name] = [tempdb].[dbo].[#s].[name] as [a].[name])  NULL                                                                               2             0,01          2E-06         9           0,0132866        NULL                    NULL     PLAN_ROW                                                         0        1
0                    0                           |--Compute Scalar(DEFINE:([Expr1005]=setidentity([tempdb].[dbo].[#s].[id] as [a].[id],(819300079),(2),NULL)))                                                          4           3           2           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1005]=setidentity([tempdb].[dbo].[#s].[id] as [a].[id],(819300079),(2),NULL))                                                       [Expr1005]=setidentity([tempdb].[dbo].[#s].[id] as [a].[id],(819300079),(2),NULL)  2             0             2E-07         40          0,0032846        [a].[name], [Expr1005]  NULL     PLAN_ROW                                                         0        1
2                    1                                |--Top(ROWCOUNT est 0)                                                                                                                                            4           4           3           Top                            Top                            TOP EXPRESSION:((0))                                                                                                                             NULL                                                                               2             0             2E-07         40          0,0032844        [a].[id], [a].[name]    NULL     PLAN_ROW                                                         0        1
2                    1                                     |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#s] AS [a]), SEEK:([a].[id] > (1)) ORDERED FORWARD) 


Параллелизм виноват?
1 мар 14, 01:03    [15650587]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Гавриленко Сергей Алексеевич,

Похоже.
Попробуйте вставить в табличку чуть побольше записей в вашем примере. ну 100000 хотя бы. И сортинг и паралеллилизм появится.

set nocount on

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

if object_id('tempdb..#d') is not null
    drop table #d

create table #s (   
    id      int             not null    primary key
    , name  varchar (50)    not null    
)

--insert #s ( id, name )
--            select 1, '1'
--union all   select 2, '2'
--union all   select 3, '3'

;With NumberSequence( Number ) as
(
    Select 1 as Number
        union all
    Select Number + 1
        from NumberSequence
        where Number < 100000
)
insert into #s
Select Number, cast(Number as varchar(100))
From NumberSequence Option (MaxRecursion 0)


create table #d (   
    id      int             not null    identity primary key
    , name  varchar (50)    not null    
)

set statistics profile on 

insert #d (name) select  name from #s a where a.id > 1  order by a.id

truncate table #d

set identity_insert #d on

insert #d (id, name) select id, name from #s a where a.id > 1  order by a.id

set identity_insert #d off

set statistics profile off


Сообщение было отредактировано: 1 мар 14, 01:49
1 мар 14, 01:38    [15650716]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Rows                 Executes             StmtText                                                                                                                                                                      StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                         DefinedValues                                                                       EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                        Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- --------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
99999                1                    insert #d (id, name) select id, name from #s a where a.id > 1  order by a.id                                                                                                  2           1           0           NULL                           NULL                           NULL                                                                                                                                             NULL                                                                                99999         NULL          NULL          NULL        8,763853         NULL                              NULL     INSERT                                                           0        NULL
99999                1                      |--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#d]), SET:([tempdb].[dbo].[#d].[id] = [Expr1005],[tempdb].[dbo].[#d].[name] = [tempdb].[dbo].[#s].[name] as [a].[name]))  2           2           1           Clustered Index Insert         Insert                         OBJECT:([tempdb].[dbo].[#d]), SET:([tempdb].[dbo].[#d].[id] = [Expr1005],[tempdb].[dbo].[#d].[name] = [tempdb].[dbo].[#s].[name] as [a].[name])  NULL                                                                                99999         3,302408      0,099999      9           8,763853         NULL                              NULL     PLAN_ROW                                                         0        1
99999                1                           |--Parallelism(Gather Streams, ORDER BY:([Expr1005] ASC))                                                                                                              2           3           2           Parallelism                    Gather Streams                 ORDER BY:([Expr1005] ASC)                                                                                                                        NULL                                                                                99999         0             0,7463678     40          5,361446         [a].[name], [Expr1005]            NULL     PLAN_ROW                                                         1        1
99999                4                                |--Sort(ORDER BY:([Expr1005] ASC))                                                                                                                                2           4           3           Sort                           Sort                           ORDER BY:([Expr1005] ASC)                                                                                                                        NULL                                                                                99999         0,005630631   3,811921      40          4,615078         [a].[name], [Expr1005]            NULL     PLAN_ROW                                                         1        1
99999                4                                     |--Parallelism(Distribute Streams, RoundRobin Partitioning)                                                                                                  2           5           4           Parallelism                    Distribute Streams             NULL                                                                                                                                             NULL                                                                                99999         0             0,4642456     40          0,7975264        [a].[name], [Expr1005]            NULL     PLAN_ROW                                                         1        1
99999                1                                          |--Top(ROWCOUNT est 0)                                                                                                                                  2           6           5           Top                            Top                            TOP EXPRESSION:((0))                                                                                                                             NULL                                                                                99999         0             0,0099999     40          0,3332807        [a].[name], [Expr1005]            NULL     PLAN_ROW                                                         0        1
0                    0                                               |--Compute Scalar(DEFINE:([Expr1005]=setidentity([tempdb].[dbo].[#s].[id] as [a].[id],(1189579276),(2),NULL)))                                     2           7           6           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1005]=setidentity([tempdb].[dbo].[#s].[id] as [a].[id],(1189579276),(2),NULL))                                                      [Expr1005]=setidentity([tempdb].[dbo].[#s].[id] as [a].[id],(1189579276),(2),NULL)  99999         0             0,0099999     44          0,3232808        [a].[id], [a].[name], [Expr1005]  NULL     PLAN_ROW                                                         0        1
99999                1                                                    |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#s] AS [a]), SEEK:([a].[id] > (1)) ORDERED FORWARD)                                           2           8           7           Clustered Index Seek           Clustered Index Seek           OBJECT:([tempdb].[dbo].[#s] AS [a]), SEEK:([a].[id] > (1)) ORDERED FORWARD                                                                       [a].[id], [a].[name]                                                                99999         0,203125      0,1101559     40          0,3132809        [a].[id], [a].[name]              NULL     PLAN_ROW                                                         0        1


Сообщение было отредактировано: 1 мар 14, 01:49
1 мар 14, 01:38    [15650718]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Ой, немножко ошибся с форматированием. Вот вам и сортировка.
Rows                 Executes             StmtText                                                                                                                                                                      StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                         DefinedValues                                                                       EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                        Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- --------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
99999                1                    insert #d (id, name) select id, name from #s a where a.id > 1  order by a.id                                                                                                  2           1           0           NULL                           NULL                           NULL                                                                                                                                             NULL                                                                                99999         NULL          NULL          NULL        8,763853         NULL                              NULL     INSERT                                                           0        NULL
99999                1                      |--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#d]), SET:([tempdb].[dbo].[#d].[id] = [Expr1005],[tempdb].[dbo].[#d].[name] = [tempdb].[dbo].[#s].[name] as [a].[name]))  2           2           1           Clustered Index Insert         Insert                         OBJECT:([tempdb].[dbo].[#d]), SET:([tempdb].[dbo].[#d].[id] = [Expr1005],[tempdb].[dbo].[#d].[name] = [tempdb].[dbo].[#s].[name] as [a].[name])  NULL                                                                                99999         3,302408      0,099999      9           8,763853         NULL                              NULL     PLAN_ROW                                                         0        1
99999                1                           |--Parallelism(Gather Streams, ORDER BY:([Expr1005] ASC))                                                                                                              2           3           2           Parallelism                    Gather Streams                 ORDER BY:([Expr1005] ASC)                                                                                                                        NULL                                                                                99999         0             0,7463678     40          5,361446         [a].[name], [Expr1005]            NULL     PLAN_ROW                                                         1        1
99999                4                                |--Sort(ORDER BY:([Expr1005] ASC))                                                                                                                                2           4           3           Sort                           Sort                           ORDER BY:([Expr1005] ASC)                                                                                                                        NULL                                                                                99999         0,005630631   3,811921      40          4,615078         [a].[name], [Expr1005]            NULL     PLAN_ROW                                                         1        1
99999                4                                     |--Parallelism(Distribute Streams, RoundRobin Partitioning)                                                                                                  2           5           4           Parallelism                    Distribute Streams             NULL                                                                                                                                             NULL                                                                                99999         0             0,4642456     40          0,7975264        [a].[name], [Expr1005]            NULL     PLAN_ROW                                                         1        1
99999                1                                          |--Top(ROWCOUNT est 0)                                                                                                                                  2           6           5           Top                            Top                            TOP EXPRESSION:((0))                                                                                                                             NULL                                                                                99999         0             0,0099999     40          0,3332807        [a].[name], [Expr1005]            NULL     PLAN_ROW                                                         0        1
0                    0                                               |--Compute Scalar(DEFINE:([Expr1005]=setidentity([tempdb].[dbo].[#s].[id] as [a].[id],(1189579276),(2),NULL)))                                     2           7           6           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1005]=setidentity([tempdb].[dbo].[#s].[id] as [a].[id],(1189579276),(2),NULL))                                                      [Expr1005]=setidentity([tempdb].[dbo].[#s].[id] as [a].[id],(1189579276),(2),NULL)  99999         0             0,0099999     44          0,3232808        [a].[id], [a].[name], [Expr1005]  NULL     PLAN_ROW                                                         0        1
99999                1                                                    |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#s] AS [a]), SEEK:([a].[id] > (1)) ORDERED FORWARD)                                           2           8           7           Clustered Index Seek           Clustered Index Seek           OBJECT:([tempdb].[dbo].[#s] AS [a]), SEEK:([a].[id] > (1)) ORDERED FORWARD                                                                       [a].[id], [a].[name]                                                                99999         0,203125      0,1101559     40          0,3132809        [a].[id], [a].[name]              NULL     PLAN_ROW                                                         0        1
1 мар 14, 01:41    [15650728]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Гавриленко Сергей Алексеевич,

А option (maxdop 1) не помогает :-(. Может у Вас поможет

Может еще есть что-то хитрое?!
1 мар 14, 01:45    [15650745]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
Mnior
leov
что-то типа backup/restore той области где таблица лежит
типа скопипастить файл данных вместо возни с записями
1. Такого нет.
2. BULK insert (+ кажись Simple Log) есть итак и разниц не сильно большая в итоге. Ибо целостность и согласованность базы на первом месте палюбэ.
все-таки какой-то хитрый ход должен тут быть
типа сделать снапшот и потом поднять его, потом переименовать табличку потом поднять еще раз...
или вывести табличку в отдельный файл, бэкапить его, потом переместить, переименовать,
на старом месте создать заново пустую, потом отресторить еще раз...
или например посмотреть профайлером что DTS делает когда копируют табличку как объект и как просто данные...
1 мар 14, 01:46    [15650748]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

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

Ага, только о нем пока никто не знает.

Пока что очень хочется проконсультироваться с Александром касательно параллелилизма.
1 мар 14, 01:51    [15650762]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Гавриленко Сергей Алексеевич,

Вот сижу вашу статью https://msmvps.com/blogs/gladchenko/archive/2010/03/09/1761298.aspx
----------------------------------------------------------------------------------------------------
Вставка в кластеризованный индекс отсортированных входных данных приводит к снижению фрагментации. Однако сортировка данных может потребовать большой объём памяти. Если нужно снизить фрагментацию и одновременно снизить утилизацию памяти сортировкой, можно воспользоваться следующей уловкой:

Создайте временную таблицу с такой же схемой как у таблицы, получающей данные.
Убедитесь, что у временной таблицы создан такой же кластеризованный индекс, как у получающей таблицы.
Выполните массовую загрузку данных в промежуточную таблицу. Если это окажется полезно для производительности, включите флаг трассировки 610.
Данные массовой загрузки окажутся в промежуточной таблице, они уже будут отсортированы, но могут оказаться фрагментированными.
Используйте метод массовой загрузки INSERT…SELECT для перемещения данных из промежуточной таблицы в таблицу, куда изначально нужно было загрузить данные.
Поскольку промежуточная таблица уже отсортирована, данные будут вставляться в таблицу назначения в отсортированном порядке.
Этот подход обеспечит минимальную фрагментацию вставляемых данных в таблице назначения.
-----------------------------------------------------------------------------------------------------------------------------

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

Кому верить?
1 мар 14, 02:19    [15650842]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Павел-П
Гавриленко Сергей Алексеевич,

Вот сижу вашу статью https://msmvps.com/blogs/gladchenko/archive/2010/03/09/1761298.aspx


Вы правильно нашли статью (только автора чуть-чуть перепутали, автор той статьи Александр Гладченко), но вас запутало identity.

В данном случае сортировка это действительно оптимизация применяемая сервером для уменьшения фрагментации в кластерном индексе.

Рассмотрим на примерах. Но перед этим уберем все сбивающие с толку и могущие увести в сторону детали, как то - top, order by, where. (Кроме того, я использую SQL Server 2012, в поведении в данном случае отличий не будет, только из плана исчезнет оператор Top, который в 2008 добавляется в запросы на модификацию автоматически для обеспечения set rowcount).
Выполним приведенный ниже скрипт два раза. Один раз когда вставляем 364 строки, второй раз когда 365. Посмотрим на планы.

set nocount on;
if object_id('tempdb..#s') is not null drop table #s;
if object_id('tempdb..#d') is not null drop table #d;

create table #s (id int not null primary key,name  varchar (50) not null);
;With NumberSequence( Number ) as
(
    Select 1 as Number union all Select Number + 1 from NumberSequence where Number < 365 --365
)
insert into #s select Number, cast(Number as varchar(100)) from NumberSequence option (MaxRecursion 0);

create table #d (id int not null primary key identity, name varchar (50) not null);
go
-- 1.
set statistics xml on;
insert #d (name) select name from #s a;
set statistics xml off;
go
truncate table #d;
go
-- 2.
set statistics xml on;
set identity_insert #d on;
insert #d (id, name) select id, name from #s a;
set identity_insert #d off;
set statistics xml off;
go
drop table #d, #s;


Картинка с другого сайта.

Вопросы.

Итак, вопросы.

1. Почему отличаются планы вставки identity insert для 364 строк и 365 (1.2 и 2.2). В последнем появляется сортировка?

Дело в стоимости оператора Clustered Index Insert. Начиная с определенного порога, сервер решает затребовать строки в отсортированном порядке. Обратите внимание на свойство DML Request Sort, в случае 2.2 оно равно True (как и в случае 2.1 кстати). Если вы поменяете определение таблицы #d, например, сделаете name varchar(50) -> name char(50), стоимость поменяется (станет дороже) и для 364 строк тоже будет сортировка. Т.е. этопросто вопрос стоимости, когда с некоторого порога оптимизатор запрашивает строки в отсортированном порядке, чтобы избежать фрагментации.

2. Почему в случае когда не выполняется вставка в колонку с identity, даже для 365 строк нет сортировки, т.е. почему планы 1.1 и 2.1 одинаковые?

Как уже было сказано в предыдущем пункте DML Request Sort = True и для плана 2.1. Но в случае 2.2 вы меняете ключ кластерного индекса (ведь колонка не только identity, но и primary key, который по умолчанию реализуется кластерным индексом). Т.е. в случае 2.2 вы меняете ключ и строки действительно должны быть отсортированы по ключу, в случае 2.1, когда вставляется просто name, ключ не меняется - сортировать не нужно, оптимизатор в курсе, поэтому сортировки нет, несмотря на то что она запрашивается вставкой.

3. Почему бы не реализовать сортировку упорядоченным сканированием (ну или поиском + частичным сканированием), избежав явной сортировки?

Это то место, где в бой вступает identity insert. Посмотрим подробнее на оператор Compute Scalar в плане 2.2. Внутри мы увидим нечто подобное: Expr1005 = Scalar Operator(setidentity([tempdb].[dbo].[#s].[id] as [a].[id],(-1480674683),(2),NULL)). И далее в операторе сортировки: OrderBy: Expr1005 Ascending.

Именно потому, что над полем [#s].[id] используется внутренняя функция setidentity, мы не можем принимать в расчет сортировку по этому полю, т.к. нам нужно сортировать по выражению Expr1005 а не по [#s].[id]. Другое дело, что сервер мог бы быть поумнее и для этой внутренней функции использовать свою логику, не как для обычных выражений в запросе, но увы.

Уберем identity и выполним два запроса для 365 строк (т.е. с запрашиваемой сортировкой).

+
set nocount on;
if object_id('tempdb..#s') is not null drop table #s;
if object_id('tempdb..#d') is not null drop table #d;

create table #s (id int not null primary key,name  varchar (50) not null);
;With NumberSequence( Number ) as
(
    Select 1 as Number union all Select Number + 1 from NumberSequence where Number < 365
)
insert into #s select Number, cast(Number as varchar(100)) from NumberSequence option (MaxRecursion 0);

create table #d (id int not null primary key /*identity*/, name varchar (50) not null);
go
-- 3.1
set statistics xml on;
insert #d (id, name) select id, name from #s a;
set statistics xml off;
go
go
truncate table #d;
go
-- 3.2
set statistics xml on;
insert #d (id, name) select abs(id), name from #s a;
set statistics xml off;
go
drop table #d, #s;

Картинка с другого сайта.

В первом случае 3.1 - видно, что сортировки теперь нет, т.к. нет функции над полем #s.id и оптимизатор может не просто запросить но и использовать упорядоченный просмотр по кластерному индексу таблицы #s, тем самым избежав сортировки1.

Во втором случае 3.2 - мы наблюдаем такую же картину как с identity, в данном случае, вместо setidentity используется функция abs, которая определяет выражение и заставляет сервер выполнить явную сортировку по этому выражению.

Решение

В статье описано все правильно, но единственное что, если вы будете все равно выполнять вставку в таблицу с identity, там где этот столбец входит в индекс - будет сортировка. Так что никакие промежуточные таблицы не спасут. Но, к слову, немного странная задача. Я привык, что set identity insert используется больше в каких-то исключительных случаях, чем как запрос на постоянной основе, который нужно часто выполнять и по этому важна производительность. Если так, то может быть не делать колонку identity, либо не вставлять туда значения явно. Короче, имхо, легальное решение тут будет - сменить либо архитектуру, либо бизнес логику, чтобы избавиться от явной вставки в identity.

Нелегальное решение

Есть магический флажок трассировки, который снимает требование DML Sort Request - TF 8795, но он недокументирован и не поддерживается официально. Так что я вам этого не советовал, на свой страх и риск =).
+
set nocount on;
if object_id('tempdb..#s') is not null drop table #s;
if object_id('tempdb..#d') is not null drop table #d;

create table #s (id int not null primary key,name  varchar (50) not null);
;With NumberSequence( Number ) as
(
    Select 1 as Number union all Select Number + 1 from NumberSequence where Number < 364 --365
)
insert into #s select Number, cast(Number as varchar(100)) from NumberSequence option (MaxRecursion 0);

create table #d (id int not null primary key identity, name varchar (50) not null);
go
-- 4.
set statistics xml on;
set identity_insert #d on;
insert #d (id, name) select id, name from #s a option(querytraceon 8795);
set identity_insert #d off;
set statistics xml off;
go
drop table #d, #s;

Картинка с другого сайта.
1 мар 14, 09:42    [15651284]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow,

Опечатался, последний скрипт естесвенно для 365 строк (для 364 и так нет запроса на сортировку).
+
set nocount on;
if object_id('tempdb..#s') is not null drop table #s;
if object_id('tempdb..#d') is not null drop table #d;

create table #s (id int not null primary key,name  varchar (50) not null);
;With NumberSequence( Number ) as
(
    Select 1 as Number union all Select Number + 1 from NumberSequence where Number < 365 --365
)
insert into #s select Number, cast(Number as varchar(100)) from NumberSequence option (MaxRecursion 0);

create table #d (id int not null primary key identity, name varchar (50) not null);
go
-- 4.
set statistics xml on;
set identity_insert #d on;
insert #d (id, name) select id, name from #s a option(querytraceon 8795);
set identity_insert #d off;
set statistics xml off;
go
drop table #d, #s;

Картинка с другого сайта.
1 мар 14, 09:52    [15651294]     Ответить | Цитировать Сообщить модератору
 Re: Table insert with set identity_insert on. Можно ли избежать сортировки данных  [new]
invm
Member

Откуда: Москва
Сообщений: 9400
Можно всех обмануть :)
+
use tempdb;
go

/**************** Подготовка ***************/
create table dbo.t1 (id int not null identity primary key, v int);
create table dbo.t2 (id int not null identity primary key, v int);
go

insert into dbo.t1
 (v)
select top (1000)
 a.number
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

create procedure dbo.t1_to_t2
 @rows bigint
as
begin
 set nocount on;
 set identity_insert dbo.t2 on;

 truncate table dbo.t2;

 insert into dbo.t2
  (id, v)
  select top (@rows)
   id, v
  from
   dbo.t1;
end;
go
/******************************************/

/* 1 */
set statistics xml on;
exec dbo.t1_to_t2 1000;
set statistics xml off;
go

exec sp_recompile 'dbo.t1_to_t2';
exec dbo.t1_to_t2 1;
go

/* 2 */
set statistics xml on;
exec dbo.t1_to_t2 1000;
set statistics xml off;
go

drop procedure dbo.t1_to_t2;
drop table dbo.t1, dbo.t2;
go
1.
Картинка с другого сайта.
2.
Картинка с другого сайта.

Модератор: Вложение удалено.


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