В базе на 2008 SQL-server-е имеется таблица с данными.
Нужно обработать данные: считываем из одной колонки, "магическим" образом обрабатываем и сохраняем в другую колонку той же самой записи.
Обработка запускается из пакетного файла с помощью sqlcmd.
Время обработки заняло где-то 250 секунд.
Решили его улучшить и запустить обработку в 5 потоков, выделив каждому свой диапазон записей.
И о чудо... первые два потока завершили свою работу приблизительно за 100 секунд, второй за 300 секунд, а оставшиеся за 400 секунд.
(время округлено для примера, но диапазон такой).
Получилось, что один поток справился быстрее чем пять.
Начальство утверждает что должно быть быстрее.
Я аргументирую тем что пять потоков читают и пишут в одну и ту же таблицу, и мешают друг-другу, поэтому время увеличивается.
Кто прав?
Если я не прав, то какие есть способы оптимизации?
И еще вопрос. Возможно ли из сторед-процедуры запустить в отдельных потоках на выполнение другие сторед-процедуры?
Здравствуйте, blonduser, Вы писали:
B>Получилось, что один поток справился быстрее чем пять. B>Начальство утверждает что должно быть быстрее. B>Я аргументирую тем что пять потоков читают и пишут в одну и ту же таблицу, и мешают друг-другу, поэтому время увеличивается.
B>Кто прав?
Никто. Возможны разные варианты.
B>Если я не прав, то какие есть способы оптимизации?
Нужно смотреть, на чем "дерутся" потоки. Скорее всего, это либо слишком широкие блокировки, либо идет драка за доступ к одним и тем же страницам.
Первое, о чем стоит подумать — разделить записи по потокам так, чтобы они обновляли непересекающиеся части таблицы (по индексам и по страницам). Дальше уже можно пытаться изголяться с индексами, хинтами и т.п.
B>И еще вопрос. Возможно ли из сторед-процедуры запустить в отдельных потоках на выполнение другие сторед-процедуры?
AFAIK, стандартными средствами — нет. При большом желании, наверное, можно сваять расширенную хранимку, которая будет такое делать. Но это уже изврат какой-то.
Здравствуйте, Lexey, Вы писали:
L>Здравствуйте, blonduser, Вы писали:
B>>Получилось, что один поток справился быстрее чем пять. B>>Начальство утверждает что должно быть быстрее. B>>Я аргументирую тем что пять потоков читают и пишут в одну и ту же таблицу, и мешают друг-другу, поэтому время увеличивается.
B>>Кто прав?
L>Никто. Возможны разные варианты.
B>>Если я не прав, то какие есть способы оптимизации?
L>Нужно смотреть, на чем "дерутся" потоки. Скорее всего, это либо слишком широкие блокировки, либо идет драка за доступ к одним и тем же страницам. L>Первое, о чем стоит подумать — разделить записи по потокам так, чтобы они обновляли непересекающиеся части таблицы (по индексам и по страницам). Дальше уже можно пытаться изголяться с индексами, хинтами и т.п.
Так они и так не пересекаются. Они делятся по уникальному ID и каждый поток обрабатывает только свою часть данных.
Подключался профайлером, но он отображает данные только для сторед процедуры которую я запускаю.
Как его заставить отображать статистику для внутренних вызовов которые делаются из сторед-процедуры.
B>>И еще вопрос. Возможно ли из сторед-процедуры запустить в отдельных потоках на выполнение другие сторед-процедуры?
L>AFAIK, стандартными средствами — нет. При большом желании, наверное, можно сваять расширенную хранимку, которая будет такое делать. Но это уже изврат какой-то.
Здравствуйте, blonduser, Вы писали: B>Так они и так не пересекаются. Они делятся по уникальному ID и каждый поток обрабатывает только свою часть данных.
Если они не пересекаются, то у тебя должно происходить одно из
-эскалация блокировок, которая произойдет после примерно 2х тыс записей
-Range-Lock-и потому что уровень изоляции слишком жесткий.
Это можно пофиксить двумя способами
-сменить уровень изоляции на snapshot
set transaction isolation level snapshot
-отключить эскалацию блокировок
ALTER TABLE [dbo].[YourTable] SET (LOCK_ESCALATION = DISABLE)
B>>>И еще вопрос. Возможно ли из сторед-процедуры запустить в отдельных потоках на выполнение другие сторед-процедуры?
Хранимки параллельно просто так не запустишь, но если сможешь переделать код на table valued functions — то можно
Здравствуйте, rm822, Вы писали:
R>Здравствуйте, blonduser, Вы писали: B>>Так они и так не пересекаются. Они делятся по уникальному ID и каждый поток обрабатывает только свою часть данных. R>Если они не пересекаются, то у тебя должно происходить одно из R>-эскалация блокировок, которая произойдет после примерно 2х тыс записей R>-Range-Lock-и потому что уровень изоляции слишком жесткий. R>Это можно пофиксить двумя способами R>-сменить уровень изоляции на snapshot R>
R>set transaction isolation level snapshot
R>
R>-отключить эскалацию блокировок R>
R>ALTER TABLE [dbo].[YourTable] SET (LOCK_ESCALATION = DISABLE)
R>
B>>>>И еще вопрос. Возможно ли из сторед-процедуры запустить в отдельных потоках на выполнение другие сторед-процедуры? R>Хранимки параллельно просто так не запустишь, но если сможешь переделать код на table valued functions — то можно
Переделать то можно, только вроде в "table valued functions" нельзя изменять записи в той же самой таблице.
Или я ошибаюсь?
Здравствуйте, blonduser, Вы писали:
L>>Нужно смотреть, на чем "дерутся" потоки. Скорее всего, это либо слишком широкие блокировки, либо идет драка за доступ к одним и тем же страницам. L>>Первое, о чем стоит подумать — разделить записи по потокам так, чтобы они обновляли непересекающиеся части таблицы (по индексам и по страницам). Дальше уже можно пытаться изголяться с индексами, хинтами и т.п.
B>Так они и так не пересекаются. Они делятся по уникальному ID и каждый поток обрабатывает только свою часть данных.
Если бы они не пересекались, то не было бы таких затыков. Не любое деление по ID приводит к изоляции тразакций. Например, если ID — это identity, по которому построен кластерный индекс и деление производится по ID mod N, то нормальной изоляции не будет, ибо запись будет производится в одни и те же страницы. Либо происходит эскалация блокировок, как уже сказали.
B>Подключался профайлером, но он отображает данные только для сторед процедуры которую я запускаю. B>Как его заставить отображать статистику для внутренних вызовов которые делаются из сторед-процедуры.
AFAIR, включить запись событий SQL Statement Start/SQL Statement finish. При дефолтных настройках для SP профайлер ловит только SP start/finish и Batch Start/finish (за точность названий не ручаюсь, но смысл такой).
B>Переделать то можно, только вроде в "table valued functions" нельзя изменять записи в той же самой таблице. B>Или я ошибаюсь?
Изменять — нельзя, но в отличие от хранимок их можно параллельно выполнять
Пример
create table MyTargetTable(id int primary Key, comment nvarchar(max))
GO
CREATE FUNCTION [dbo].[MyTVF](@oldComment nvarchar(max))
RETURNS TABLE
AS
return select @oldComment + N'suffix' as newcomment
GO
update dbo.MyTargetTable
set comment = newComment
from dbo.MyTargetTable
outer apply [MyTVF](comment) T
option(querytraceon 8649)
Здравствуйте, blonduser, Вы писали:
B>Всем доброго времени суток.
B>В базе на 2008 SQL-server-е имеется таблица с данными. B>Нужно обработать данные: считываем из одной колонки, "магическим" образом обрабатываем и сохраняем в другую колонку той же самой записи. B>Обработка запускается из пакетного файла с помощью sqlcmd.
B>Время обработки заняло где-то 250 секунд. B>Решили его улучшить и запустить обработку в 5 потоков, выделив каждому свой диапазон записей.
Стесняюсь спросить — а как именно выделяли "свой диапазон записей"?
Как выглядит select, который выбирает нужные записи; как выглядит Update, который записывает обновления; какие индексы объявлены на рассматриваемой таблице; что выдаёт sp_lock во время первых 100 секунд для сессий потоков 2, 3 и далее?
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
B>Я аргументирую тем что пять потоков читают и пишут в одну и ту же таблицу, и мешают друг-другу, поэтому время увеличивается.
А изменяемые колонки не входят в какие нибудь индексы?
и солнце б утром не вставало, когда бы не было меня
Здравствуйте, rm822, Вы писали:
B>>Переделать то можно, только вроде в "table valued functions" нельзя изменять записи в той же самой таблице. B>>Или я ошибаюсь? R>Изменять — нельзя, но в отличие от хранимок их можно параллельно выполнять
R>Пример R>
R>create table MyTargetTable(id int primary Key, comment nvarchar(max))
R>GO
R>CREATE FUNCTION [dbo].[MyTVF](@oldComment nvarchar(max))
R> RETURNS TABLE
R>AS
R> return select @oldComment + N'suffix' as newcomment
R>GO
R>update dbo.MyTargetTable
R> set comment = newComment
R>from dbo.MyTargetTable
R> outer apply [MyTVF](comment) T
R>option(querytraceon 8649)
R>
Для преобразование данных у меня вызывается функция из Assembly.
У Assembly установлены права доступа UNSAFE и происходит запись и чтение данных с диска.
При обычном способе, все корректно работает.
При вызове из этой функции выдается ошибка "Отказано в доступе по пути".
Возможно ли такой функции задать права доступа?
Здравствуйте, Sinclair, Вы писали:
S>Здравствуйте, blonduser, Вы писали:
B>>Всем доброго времени суток.
B>>В базе на 2008 SQL-server-е имеется таблица с данными. B>>Нужно обработать данные: считываем из одной колонки, "магическим" образом обрабатываем и сохраняем в другую колонку той же самой записи. B>>Обработка запускается из пакетного файла с помощью sqlcmd.
B>>Время обработки заняло где-то 250 секунд. B>>Решили его улучшить и запустить обработку в 5 потоков, выделив каждому свой диапазон записей. S>Стесняюсь спросить — а как именно выделяли "свой диапазон записей"? S>Как выглядит select, который выбирает нужные записи; как выглядит Update, который записывает обновления; какие индексы объявлены на рассматриваемой таблице; что выдаёт sp_lock во время первых 100 секунд для сессий потоков 2, 3 и далее?
ID — это уникальное целое число. Определяю минимальный и максимальный ID. Разницу делю на кол-во потоков.
SELECT * FROM table WHERE ID <= 250;
SELECT * FROM table WHERE ID > 250 AND ID <= 500; Ну и так далее.
Ну и потом UPDATE table SET filed = @Data WHERE ID = @xID;
Здравствуйте, blonduser, Вы писали: B>ID — это уникальное целое число. Определяю минимальный и максимальный ID. Разницу делю на кол-во потоков. B>Создан уникальный индекс по ID.
Ок, в таком случае false sharing не должно быть.
Смотрите на тайминг выполнения select и update при помощи профайлера — есть ли взаимосвязь между временем работы потока и временем отработки стейтментов.
Возможно, у вас блокировки за пределами SQL Server.
Был в нашей практике такой случай — лишний static, и все потоки стояли в одной очереди
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
B>Для преобразование данных у меня вызывается функция из Assembly. B>У Assembly установлены права доступа UNSAFE и происходит запись и чтение данных с диска.
ЗАПИСЬ????
Что-ж ты сразу не сказал что у тебя сборка, да еще с тяжелыми операциями IO внутри.
При таких условиях модель несколько меняется
;with Ranges as
(
select * from (values(1,100),(100,200),(200,300)) Ranges(first_row,last_row)
)
update dbo.MyTargetTable
set comment = newComment
from
(
select FN.id, FN.newComment from ranges
outer apply dbo.MyTVF(first_row,last_row) FN
) T
inner join dbo.MyTargetTable U on U.id = t.id
option(force order, querytraceon 8649)
В Ranges генерятся диапазоны записей, в идеале по числу доступных ядер.
SQL-сервер вызывает dbo.MyTVF для каждого диапазона в своем потоке
B>При вызове из этой функции выдается ошибка "Отказано в доступе по пути".
Скорее всего в атрибуте SqlFunction не указал DataAccess
Здравствуйте, rm822, Вы писали:
B>>Для преобразование данных у меня вызывается функция из Assembly. B>>У Assembly установлены права доступа UNSAFE и происходит запись и чтение данных с диска. R>ЗАПИСЬ???? R>Что-ж ты сразу не сказал что у тебя сборка, да еще с тяжелыми операциями IO внутри. R>При таких условиях модель несколько меняется
R>
R>;with Ranges as
R>(
R>select * from (values(1,100),(100,200),(200,300)) Ranges(first_row,last_row)
R>)
R>update dbo.MyTargetTable
R> set comment = newComment
R>from
R>(
R> select FN.id, FN.newComment from ranges
R> outer apply dbo.MyTVF(first_row,last_row) FN
R> ) T
R> inner join dbo.MyTargetTable U on U.id = t.id
R>option(force order, querytraceon 8649)
R>
R>В Ranges генерятся диапазоны записей, в идеале по числу доступных ядер. R>SQL-сервер вызывает dbo.MyTVF для каждого диапазона в своем потоке
Заработал первый вариант и этот тоже. Спасибо.
В описанном варианте используется "inner join" и преобразование происходит несколько раз над одними и теме же данными.
Вместо него написал обычное "where" преобразование произошло быстрее.
В чем смысл использования "inner join"?
B>>При вызове из этой функции выдается ошибка "Отказано в доступе по пути". R>Скорее всего в атрибуте SqlFunction не указал DataAccess
В этом случае указал неверный путь.
