SqlBulkCopy и DataTables с соотношением родительских и дочерних элементов в столбце идентичности

Ответ 1

Прежде всего: SqlBulkCopy не может делать то, что вы хотите. Как следует из названия, это просто "улица в одну сторону". Я перемещаю данные в sql-сервер как можно быстрее. Это .Net-версия старой команды массового копирования, которая импортирует необработанные текстовые файлы в таблицы. Таким образом, нет способа вернуть значения идентификатора, если вы используете SqlBulkCopy.

Я много обрабатывал объемную обработку данных и несколько раз сталкивался с этой проблемой. Решение зависит от вашей архитектуры и распределения данных. Вот несколько идей:

• Создайте один набор целевых таблиц для каждого потока, импортируйте в эти таблицы. В конце соедините эти таблицы. Большинство из них могут быть реализованы довольно общим способом, когда вы автоматически генерируете таблицы TABLENAME_THREAD_ID из таблиц, называемых TABLENAME.

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

• Попробуйте генерировать идентификаторы ваших данных. Если возможно, ваша проблема исчезла бы. Не говорите "не возможно" быстро. Возможно, вы можете использовать идентификаторы строк, которые можно очистить на этапе последующей обработки?

И еще одно замечание: увеличение коэффициента 34 при использовании BulkCopy звучит малозначительно. Если вы хотите быстро вставлять данные, убедитесь, что ваша база данных настроена правильно.

Ответ 2

Прочтите эту статью. Я думаю, что это именно то, что вы ищете и многое другое. Очень приятное и элегантное решение.

http://www.codinghelmet.com/?path=howto/bulk-insert

Ответ 3

Единственный способ сделать то, что вы хотите, используя SqlBulkCopy, - это сначала вставить данные в промежуточную таблицу. Затем используйте хранимую процедуру для распространения данных в таблицы destinate. Да, это приведет к замедлению, но все равно будет быстро.

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

Ответ 4

set identity_insert <table> on и dbcc checkident являются вашими друзьями здесь. Это то, что я делал в прошлом (см. Пример кода). Единственное реальное препятствие заключается в том, что процесс обновления является единственным, который может вставлять данные: все остальные должны выйти из пула во время обновления. Разумеется, вы могли бы сделать такое сопоставление программно до загрузки рабочих таблиц. Но те же ограничения для вставок применяются: процесс обновления является единственным процессом, который позволяет играть.

--
-- start with a source schema -- doesn't actually need to be SQL tables
-- but from the standpoint of demonstration, it makes it easier
--
create table source.parent
(
  id   int         not null primary key ,
  data varchar(32) not null ,
)
create table source.child
(
  id        int         not null primary key ,
  data      varchar(32) not null ,
  parent_id int         not null foreign key references source.parent(id) ,
)

--
-- On the receiving end, you need to create staging tables.
-- You'll notice that while there are primary keys defined,
-- there are no foreign key constraints. Depending on the
-- cleanliness of your data, you might even get rid of the
-- primary key definitions (though you'll need to add
-- some sort of processing to clean the data one way or
-- another, obviously).
--
-- and, depending context, these could even be temp tables
--
create table stage.parent
(
  id   int         not null primary key ,
  data varchar(32) not null ,
)

create table stage.child
(
  id        int         not null primary key ,
  data      varchar(32) not null ,
  parent_id int         not null ,
)

--
-- and of course, the final destination tables already exist,
-- complete with identity properties, etc.
--
create table dbo.parent
(
  id int not null identity(1,1) primary key ,
  data varchar(32) not null ,
)
create table dbo.child
(
  id int not null identity(1,1) primary key ,
  data varchar(32) not null ,
  parent_id int not null foreign key references dbo.parent(id) ,
)

-----------------------------------------------------------------------
-- so, you BCP or otherwise load your staging tables with the new data
-- frome the source tables. How this happens is left as an exercise for
-- the reader. We'll just assume that some sort of magic happens to
-- make it so. Don't forget to truncate the staging tables prior to
-- loading them with data.
-----------------------------------------------------------------------

-------------------------------------------------------------------------
-- Now we get to work to populate the production tables with the new data
--
-- First we need a map to let us create the new identity values.
-------------------------------------------------------------------------
drop table #parent_map
create table #parent_map
(
  old_id int not null primary key nonclustered       ,
  offset int not null identity(1,1) unique clustered ,
  new_id int     null ,  
)
create table #child_map
(
  old_id int not null primary key nonclustered ,
  offset int not null identity(1,1) unique clustered ,
  new_id int     null ,
)

insert #parent_map ( old_id ) select id from stage.parent
insert #child_map  ( old_id ) select id from stage.child

-------------------------------------------------------------------------------
-- now that we've got the map, we can blast the data into the production tables
-------------------------------------------------------------------------------

--
-- compute the new ID values
--
update #parent_map set new_id = offset + ( select max(id) from dbo.parent )

--
-- blast it into the parent table, turning on identity_insert
--
set identity_insert dbo.parent on

insert dbo.parent (id,data)
select id   = map.new_id   ,
       data = staging.data
from stage.parent staging
join #parent_map  map     on map.old_id = staging.id

set identity_insert dbo.parent off

--
-- reseed the identity properties high water mark
--
dbcc checkident dbo.parent , reseed


--
-- compute the new ID values
--
update #child_map set new_id = offset + ( select max(id) from dbo.child )

--
-- blast it into the child table, turning on identity_insert
--
set identity_insert dbo.child on

insert dbo.child ( id , data , parent_id )
select id        = parent.new_id   ,
       data      = staging.data    ,
       parent_id = parent.new_id

from stage.child staging
join #child_map  map      on map.old_id    = staging.id
join #parent_map parent   on parent.old_id = staging.parent_id

set identity_insert dbo.child off

--
-- reseed the identity properties high water mark
--
dbcc checkident dbo.child , reseed

------------------------------------
-- That about all there is too it.
------------------------------------

Ответ 5

Я полагаю, что компромисс с вами - это производительность BulkInsert против надежности Identity.

Можете ли вы временно поместить базу данных в SingleUserMode для выполнения вашей вставки?

У меня возникла очень похожая проблема с моим проектом конверсии, где я добавляю столбец Identity к очень большим таблицам, и у них есть дети. К счастью, я смог установить идентификатор родительского и дочернего источников (я использовал TextDataReader) для выполнения BulkInsert, и одновременно создал родительский и дочерний файлы.

Я также получил прирост производительности, о котором вы говорите, OleDBDataReader Source → StreamWriter..., а затем TextDataReader → SQLBulk