Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 MS SQL 2016 оптимизация функции  [new]
Vlad_Molodoj
Member

Откуда:
Сообщений: 7
Ув. Коллеги! Как можно оптимальнее с точки зрения быстродействия, решить следующую задачу: Дана таблица:
CREATE TABLE [dbo].[MyTable](
  [ID36] [char](6) NOT NULL,
  [ID10] [int] NOT NULL
) ON [PRIMARY]

Поле - [ID36] - число, в 36-ричной системе исчисления. Кто переливал данные из баз 1С 7.7 - поймёт откуда "растут ноги" у задачи. [ID36] - число строго из 6 символов, с ведущими пробелами.
"Алфавит" системы исчисления - "стандартный": '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
Суть задачи: преобразовать значение колонки [ID36] из 36-ричной системы исчисления в 10-ричную, и полученным значением заполнить поле [ID10].
Сама по себе задача перевода из 36-ричной системы исчисления в 10-ричную - задача не сложная.
Учитывая, что ведущий пробел, это то же самое, что и ведущий ноль,
Решение #1 (самое простое, "в лоб"):
create Function [dbo].[To_10_1]  (@param as char(6)) returns int as
begin
  declare @alpha char(35) = '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
  return CHARINDEX(substring(@param, 1, 1), @alpha) * 60466176
       + CHARINDEX(substring(@param, 2, 1), @alpha) * 1679616
       + CHARINDEX(substring(@param, 3, 1), @alpha) * 46656
       + CHARINDEX(substring(@param, 4, 1), @alpha) * 1296
       + CHARINDEX(substring(@param, 5, 1), @alpha) * 36
       + CHARINDEX(substring(@param, 6, 1), @alpha)
end

Решение #2: (основано на ascii-кодах символов: '0'..'9' : 48..57; 'A'..'Z' : 65..90
cretae Function [dbo].[To_10_2] (@param as char(6)) returns int as begin
  declare @rez int = 0, @i int = 1, @Mult int = 60466176
  declare @p int = 0, @a int
  while @i < 7 begin
    select @a = ascii(substring(@param, @i, 1))
    select @p = case when @a >= 48 and @a < 58 then @a - 48 else case when @a >= 65 and @a < 91 then @a - 55 else 0 end end
    select @rez = @rez + @p * @Mult
    select @i = @i + 1
    select @Mult = @Mult / 36
  end
  return @rez
end

Ну и соответственно:
update [MyTable] set [ID10] = [dbo].[To_10_1] ([ID36])
  или же 
update [MyTable] set [ID10] = [dbo].[To_10_2] ([ID36])

Однако для таблицы размером в 100 млн записей продолжительность выполнения update составляет примерно 1 час, т.е. примерно 3600 секунд.

Можно ли каким-то образом ускорить обновление таблицы?
Какие есть идеи оптимизации?
22 июн 18, 01:09    [21511362]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
Idol_111
Member

Откуда:
Сообщений: 604
Vlad_Molodoj,
ну по хорошему надо бы план посмотреть, где затык. Может статься, что все уходит на запись (IO) 100млн и улучшить можно лишь на пару процентов.

ну а чтобы избежать расчетов по каждой строке, подсчитайте один раз и запишите в отдельную таблицу. Оттуда потом и тащить.
22 июн 18, 02:36    [21511412]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5960
Vlad_Molodoj
Какие есть идеи оптимизации?

Заменить скалярку на инлайновую функцию.
22 июн 18, 03:23    [21511422]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
aleks222
Member

Откуда:
Сообщений: 985
Сон Веры Павловны
Vlad_Molodoj
Какие есть идеи оптимизации?

Заменить скалярку на инлайновую функцию.


Это паллиатив.

Спасет тока таблица. ПОЛНАЯ таблица соответствий [ID36] -> [ID10]. С индексом по [ID36]

Опосля чего join и ... фсе.
22 июн 18, 06:28    [21511445]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3712
aleks222
Сон Веры Павловны
пропущено...

Заменить скалярку на инлайновую функцию.


Это паллиатив.

Спасет тока таблица. ПОЛНАЯ таблица соответствий [ID36] -> [ID10]. С индексом по [ID36]

Опосля чего join и ... фсе.


йез.
зы. слово "паллиатив" понравилось
22 июн 18, 11:12    [21512291]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5960
aleks222
Спасет тока таблица. ПОЛНАЯ таблица соответствий [ID36] -> [ID10]. С индексом по [ID36]

Vlad_Molodoj
[ID36] - число строго из 6 символов

36^6-1=2176782335. Ну, ничё так оптимизация. Из области военно-полевой хирургии.
22 июн 18, 11:34    [21512414]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1723
Интересный вопрос - а если сделать [ID10] вычисляемым persisted столбцом, то это поможет топикстартеру или нет? Формула расчёта для ID10 уже есть.

Если развивать идею с отдельной таблицей для join-a, то сначала:
а) select distinct ID36, convert(int, NULL) as ID10 в временную таблицу;
б) сравниваем число строк в исходной таблице и во временной. Если число строк в временной таблице больше 1/4 - идею в топку, временную таблицу туда же.
в) если продолжаем работу - рассчитываем и заполняем ID10 в новой таблице, строим индексы по Id36, и update исходной таблицы рассчитанными значениями из временной, удаляем индекс на временной таблице, удаляем временную таблицу. Индекс на исходной таблице - с ним по вкусу.
22 июн 18, 12:03    [21512559]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Vlad_Molodoj,

суть задачи guid -> int что ли?
22 июн 18, 12:05    [21512573]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
Владислав Колосов
Member

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

попробуйте переписать функцию как inline табличную. Скалярная препятствует созданию параллельного плана выполнения.
22 июн 18, 13:34    [21512984]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
With cte as 
(Select cast('0' as char(1)) as N36, 0 as n10,'123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' as String
union all
Select cast(left(cte.String,1)as char(1)),cte.n10 + 1,stuff(cte.String,1,1,'') 
  from cte
 Where String<>'' 
)
Select N36,n10
into #Table36
From cte


CREATE TABLE #MyTable(
  [ID36] [char](6),
  [ID10] [bigint] 
);

insert into #MyTable([ID36])
 Select top 100000000 a.N36+b.N36+c.N36+d.N36+e.N36+f.N36
   From #Table36 a
   cross join #Table36 b
   cross join #Table36 c
   cross join #Table36 d
   cross join #Table36 e
   cross join #Table36 f

Select a.id36,
sum(cast(c.n10*b.x as Bigint))
 From 
#MyTable a
cross join (values 
(6,1),
(5,36),
(4,1296),
(3,46656),
(2,1679616),
(1,60466176)) b(n,x)
join #Table36 c on c.N36 = substring(id36,b.n,1) 
Group by a.id36
22 июн 18, 15:23    [21513428]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Хотя простое
Select a.id36,
         cast(CHARINDEX(substring(a.id36, 1, 1), '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') as bigint) * 60466176
       + cast(CHARINDEX(substring(a.id36, 2, 1), '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') as bigint) * 1679616
       + cast(CHARINDEX(substring(a.id36, 3, 1), '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') as bigint) * 46656
       + cast(CHARINDEX(substring(a.id36, 4, 1), '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') as bigint) * 1296
       + cast(CHARINDEX(substring(a.id36, 5, 1), '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') as bigint) * 36
       + cast(CHARINDEX(substring(a.id36, 6, 1), '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') as bigint) as id10
 From 
#MyTable a

Планировщик оценивает как в 4.5 раза менее затратное.
22 июн 18, 15:57    [21513578]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Kopelly
Хотя простое
...
Планировщик оценивает как в 4.5 раза менее затратное.
Кто бы сомневался.

Vlad_Molodoj,

используйте Решение #1, только перепишите на инлайн.

Скалярные функции будут медленные, даже если функция будет содержать только код return 0, это бай дизайн
23 июн 18, 00:24    [21514692]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
Интересно, сколько еще раз надо написать об инлайн-функции?
23 июн 18, 10:45    [21514939]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
Vlad_Molodoj
Можно ли каким-то образом ускорить обновление таблицы?
Какие есть идеи оптимизации?
Во-первых, ваша первая функция неверна - не учитывается '0'.

Обе ваши реализации примерно одинаково тормозные, ибо, как уже писали, - скалярные функции на T-SQL не блещут производительностью.

Для вашего конкретного случая (update), можно ощутимо увеличить производительность, объявив функции с опцией with schemabinding.
Это уберет из плана выполнения ненужный table spool.

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

Вариант 21511445 будет хорош, если вам не жалко примерно 30Гб на таблицу соответствий и у обеих таблиц имеется кластерный индекс по [ID36].
И в этом варианте возможно распараллеливание соединения таблиц.

В общем, есть задачи, которые на чистом T-SQL решаются плохо. Ваша как раз из таких.
Если действительно нужно существенно ускорить выполнение - пишите функцию конвертации на SQLCLR - будет быстрее всего вышеперечисленного.
23 июн 18, 17:40    [21515419]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2016 оптимизация функции  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5960
invm
Во-первых, ваша первая функция неверна - не учитывается '0'.

Во-вторых, 6-разрядное 36-значное число в общем случае не влезет в int.
23 июн 18, 18:10    [21515446]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить