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

Откуда:
Сообщений: 136
День добрый.

Подскажите в реализации такой задачи:
Программным путем в многопоточном приложении, которое перелопачивает базу SQL и формирует порядка 8 млн. записей, создается текстовый файл вида:

Update table set parent_id=1277 where ID=1586776
Update table set parent_id= 44 where ID=3177877

Меняются только ID.

При выполнении запроса через SQLCMD файл подхватывается, данные начинают обновляться, но очень долго.
Каким образом можно быстро "пролить" эти миллионы Update'ов на сервер? При попытке не формировать файл из приложения, а обновлять непосредственно из программы многопоточно, вылетают массовые исключения о блокировках таблицы.
7 окт 14, 04:16    [16669230]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
Mdel
Member

Откуда:
Сообщений: 136
Может быть имеет смысл эти два ID кидать в таблицу на сервере? А потом UPDATE основной со связкой по полям итоговой таблицы?
7 окт 14, 04:48    [16669235]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
aleks2
Guest
1. Как бы "8 млн. записей" быстро не обновятся. Никак.
2. С таблицей будет те яйцы, тока вид сбоку.
3. Раза в два-три можно ускорить создав 2-3 файла и запустив их параллельно.
4. Шоб не было дедлоков - упорядочите по ID (хотя тут надо на индексы глядеть).
7 окт 14, 05:07    [16669237]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32173
Mdel
Может быть имеет смысл эти два ID кидать в таблицу на сервере? А потом UPDATE основной со связкой по полям итоговой таблицы?
Да, присоединяюсь, загрузить табличку с 8 миллионами записей будет быстро (можно даже балк-операцией), обновление тоже потом будет намного быстрее, чем одиночными операциями.
7 окт 14, 09:28    [16669468]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6254
Еще как вариант - передавать на сервер табличный тип, заполненный данными, и апдейт по джойну с экземпляром переданного типа. Правда, мне лично ни разу не доводилось передавать в табличном типе 8 миллионов записей.
7 окт 14, 11:40    [16670072]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
Glory
Member

Откуда:
Сообщений: 104751
alexeyvg
Mdel
Может быть имеет смысл эти два ID кидать в таблицу на сервере? А потом UPDATE основной со связкой по полям итоговой таблицы?
Да, присоединяюсь, загрузить табличку с 8 миллионами записей будет быстро (можно даже балк-операцией), обновление тоже потом будет намного быстрее, чем одиночными операциями.

По-моему, это будет тот же цикл по 8 млн + накладные расходы на join
Плюс одна транзакция, которая если что отменится тоже вся.
7 окт 14, 11:57    [16670210]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
хмхмхм
Guest
Mdel,

я бы делал так:

1. все 8 млн. записей записываете в несколько таблиц на сервере (например по 1 млн. на таблицу)
2. делаете по каждой таблице свой update (в 8 потоков обновляете данные)

На время изменений можно снести индексы, в которых участвует поле parent_id, затем их создать заново
7 окт 14, 12:00    [16670240]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
хмхмхм
Guest
Glory
По-моему, это будет тот же цикл по 8 млн + накладные расходы на join
Плюс одна транзакция, которая если что отменится тоже вся.


Да ну.

create table dbo.test(id int)


set nocount on

insert into dbo.test(id)
select o.object_id from sys.objects o



select count(*) from dbo.test


2 секунды:
update dbo.test 
set id = -id
where id = 3
go 1000



< 1 секунды:
declare @t table(id int)


insert into @t (id)
select top 1000 id from dbo.test 


update dbo.test
set 
id = -id
where exists(select 1 from @t t where t.id = id)
7 окт 14, 12:09    [16670320]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
хмхмхм
Guest
второй мой запрос конечно же такой:

update tt
set 
id = -id
from dbo.test tt
where exists(select 1 from @t t where t.id = tt.id)
7 окт 14, 12:12    [16670346]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
Glory
Member

Откуда:
Сообщений: 104751
хмхмхм
Да ну.

Вы этим "дану" хотите сказать, что у вас не будет полного сканирования таблицы с 8 млн. записей ?
Интересно, а как вы тогда выполните каждое из этих обновлений ?
7 окт 14, 12:13    [16670356]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
Glory
Member

Откуда:
Сообщений: 104751
хмхмхм
второй мой запрос конечно же такой:

update tt
set 
id = -id
from dbo.test tt
where exists(select 1 from @t t where t.id = tt.id)

И что он делает ? Обновляет значение поля таблицы на себя же ?
7 окт 14, 12:14    [16670361]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
хмхмхм
Guest
Glory
хмхмхм
Да ну.

Вы этим "дану" хотите сказать


Я этим "да ну", хочу сказать, что 1000 раз запуск одиночного update занимает больше времени, чем один update на 1000 записей.

что у вас не будет полного сканирования таблицы с 8 млн. записей ?


это уже другой вопрос. Его можно решить update порцией данных, а не сразу 8 млн.
7 окт 14, 12:16    [16670383]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
Glory
Member

Откуда:
Сообщений: 104751
хмхмхм
Я этим "да ну", хочу сказать, что 1000 раз запуск одиночного update занимает больше времени, чем один update на 1000 записей.

Охренеть.
А какое отношение это имеет к текущей задаче ?
И где в ващем примере соединение таблиц то ?
Где рассчет ресурсов под это соединение ?
7 окт 14, 12:20    [16670403]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Glory
хмхмхм
второй мой запрос конечно же такой:

update tt
set 
id = -id
from dbo.test tt
where exists(select 1 from @t t where t.id = tt.id)

И что он делает ? Обновляет значение поля таблицы на себя же ?


там еще жоще: id = -id
7 окт 14, 12:28    [16670463]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
Glory
Member

Откуда:
Сообщений: 104751
Winnipuh
Glory
пропущено...

И что он делает ? Обновляет значение поля таблицы на себя же ?


там еще жоще: id = -id

Фейспалм.
Вы меня хотие убедить, что
не надо будет для каждой из 8 млн записей в одной таблице искать одну(еще не известно) запись в другой таблице ? Что каким то образом можно будет пропустить какую то часть из этих 8млн поисков ?

И что просто 8 млн отдельных update ко второй таблице не будудт дедлать тоже самое ? Т.е. также искать запись ?
7 окт 14, 12:33    [16670495]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
хмхмхм
Guest
Winnipuh
Glory
пропущено...

И что он делает ? Обновляет значение поля таблицы на себя же ?


там еще жоще: id = -id


что же тут "жосткого"?
Я просто моделирую операцию одиночного update, можно добавить вторую колонку parent_id и менять её.


[quot Glory]А какое отношение это имеет к текущей задаче ?[/quot ]
Это имеет отношение к вашему высказыванию:

автор
По-моему, это будет тот же цикл по 8 млн + накладные расходы на join

Я решил посмотреть на практике цикл из 1000 изменений и 1 изменение 1000 записей. Вышло не по-вашему.
7 окт 14, 12:39    [16670539]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
Glory
Member

Откуда:
Сообщений: 104751
хмхмхм
Я решил посмотреть на практике цикл из 1000 изменений и 1 изменение 1000 записей. Вышло не по-вашему.

Возрадуйтесь и делайте как хотите. Мне лично вас переубеждать не надо.
7 окт 14, 12:41    [16670555]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4974
хмхмхм
Я решил посмотреть на практике цикл из 1000 изменений и 1 изменение 1000 записей. Вышло не по-вашему.


Версия SQL какая?
7 окт 14, 13:12    [16670753]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
хмхмхм
Guest
a_voronin,

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
7 окт 14, 13:16    [16670772]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
хмхмхм
Я решил посмотреть на практике цикл из 1000 изменений и 1 изменение 1000 записей.
А теперь оберните "цикл из 1000 изменений" в транзакцию и сравните результаты.
7 окт 14, 13:21    [16670808]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
хмхмхм
Guest
invm
хмхмхм
Я решил посмотреть на практике цикл из 1000 изменений и 1 изменение 1000 записей.
А теперь оберните "цикл из 1000 изменений" в транзакцию и сравните результаты.


1000 одиночных изменений в транзакции = 1403 мс
1 изменение 1000 записей в транзакции = 23 мс
7 окт 14, 13:41    [16670948]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4974
Сон Веры Павловны
Еще как вариант - передавать на сервер табличный тип, заполненный данными, и апдейт по джойну с экземпляром переданного типа. Правда, мне лично ни разу не доводилось передавать в табличном типе 8 миллионов записей.


Если уж так надо вставлять одиночные записи, но надо ставить SQL 2014 и переходить на IN-MEMORY OLTP -- там по барабану -- очередью или одиночными.

Если всё же можно вставлять порциями, то надо перейти использовать BULK INSERT из кода. (SqlBulkCopy в .NET).
7 окт 14, 14:29    [16671281]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
mayton
Member

Откуда: loopback
Сообщений: 52942
Mdel
При попытке не формировать файл из приложения, а обновлять непосредственно из программы многопоточно, вылетают массовые исключения о блокировках таблицы.

Мне кажется тут надо смотреть в корень самой проблемы. Я не знаю как этих ваших МССКЛях
реализованы блокировки. Но возможно стоит посмотреть кто их блокирует. Вы сами? Или
посторонняя транзакция? В зависимости от ответа нужно будет творить разные заклинания
и пассы руками.
7 окт 14, 14:44    [16671397]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
mayton
Mdel
При попытке не формировать файл из приложения, а обновлять непосредственно из программы многопоточно, вылетают массовые исключения о блокировках таблицы.

Мне кажется тут надо смотреть в корень самой проблемы. Я не знаю как этих ваших МССКЛях
реализованы блокировки.
Но возможно стоит посмотреть кто их блокирует. Вы сами? Или
посторонняя транзакция? В зависимости от ответа нужно будет творить разные заклинания
и пассы руками.


Хорошо ответил
7 окт 14, 15:12    [16671596]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое выполнение множественных команд Update  [new]
Glory
Member

Откуда:
Сообщений: 104751
хмхмхм
invm
пропущено...
А теперь оберните "цикл из 1000 изменений" в транзакцию и сравните результаты.


1000 одиночных изменений в транзакции = 1403 мс
1 изменение 1000 записей в транзакции = 23 мс

Хорошо бы еще к цифрам привосокуплять понимание того, что может происходить на сервере

тестовые данные
set nocount on;
set ansi_warnings off;

if object_id('tempdb..#t', 'U') is not null
 drop table #t;
create table #t (id int identity primary key, v int not null);

insert into #t
select top (1000000)
 case when row_number() over (order by char('a')) % 10 <> 0 then 1 else -1 end
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

if object_id('tempdb..#t2', 'U') is not null
 drop table #t2;
create table #t2 (id int primary key, v int not null);

тесты

declare @sql varchar(max)
set @sql = 'begin transaction'
select @sql = @sql+CHAR(13)+CHAR(10)+'update #t set v = '+STR(v)+ ' where id = '+STR(id) from #t2

set @sql = @sql + +CHAR(13)+CHAR(10)+'commit transaction'

declare @start datetime2 = SYSDATETIME ()
exec(@sql)
select datediff(ms,@start,SYSDATETIME()) AS [10 000 updates]

set @start = SYSDATETIME ()
update a set v = b.v
from #t a inner loop join #t2 b on b.id = a.id
select datediff(ms,@start,SYSDATETIME()) as [LOOP JOIN]

set @start = SYSDATETIME ()
update a set v = b.v
from #t a inner merge join #t2 b on b.id = a.id
select datediff(ms,@start,SYSDATETIME()) as [MERGE JOIN]


set @start = SYSDATETIME ()
update a set v = b.v
from #t a inner hash join #t2 b on b.id = a.id
select datediff(ms,@start,SYSDATETIME()) as [HASH JOIN]

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