Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / MS SQL 2016 оптимизация функции / 15 сообщений из 15, страница 1 из 1
22.06.2018, 01:09
    #39663858
Vlad_Molodoj
Vlad_Molodoj
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
Ув. Коллеги! Как можно оптимальнее с точки зрения быстродействия, решить следующую задачу: Дана таблица:
Код: sql
1.
2.
3.
4.
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 (самое простое, "в лоб"):
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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


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


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

Можно ли каким-то образом ускорить обновление таблицы?
Какие есть идеи оптимизации?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.06.2018, 02:36
    #39663869
Idol_111
Idol_111
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
Vlad_Molodoj,
ну по хорошему надо бы план посмотреть, где затык. Может статься, что все уходит на запись (IO) 100млн и улучшить можно лишь на пару процентов.

ну а чтобы избежать расчетов по каждой строке, подсчитайте один раз и запишите в отдельную таблицу. Оттуда потом и тащить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.06.2018, 03:23
    #39663873
Сон Веры Павловны
Сон Веры Павловны
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
Vlad_MolodojКакие есть идеи оптимизации?
Заменить скалярку на инлайновую функцию.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.06.2018, 06:28
    #39663883
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
Сон Веры ПавловныVlad_MolodojКакие есть идеи оптимизации?
Заменить скалярку на инлайновую функцию.

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

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

Опосля чего join и ... фсе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.06.2018, 11:12
    #39664046
Ролг Хупин
Ролг Хупин
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
aleks222Сон Веры Павловныпропущено...

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

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

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

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

йез.
зы. слово "паллиатив" понравилось
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.06.2018, 11:34
    #39664073
Сон Веры Павловны
Сон Веры Павловны
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
aleks222Спасет тока таблица. ПОЛНАЯ таблица соответствий [ID36] -> [ID10]. С индексом по [ID36]
Vlad_Molodoj[ID36] - число строго из 6 символов
36^6-1=2176782335. Ну, ничё так оптимизация. Из области военно-полевой хирургии.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.06.2018, 12:03
    #39664102
DaniilSeryi
DaniilSeryi
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
Интересный вопрос - а если сделать [ID10] вычисляемым persisted столбцом, то это поможет топикстартеру или нет? Формула расчёта для ID10 уже есть.

Если развивать идею с отдельной таблицей для join-a, то сначала:
а) select distinct ID36, convert(int, NULL) as ID10 в временную таблицу;
б) сравниваем число строк в исходной таблице и во временной. Если число строк в временной таблице больше 1/4 - идею в топку, временную таблицу туда же.
в) если продолжаем работу - рассчитываем и заполняем ID10 в новой таблице, строим индексы по Id36, и update исходной таблицы рассчитанными значениями из временной, удаляем индекс на временной таблице, удаляем временную таблицу. Индекс на исходной таблице - с ним по вкусу.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.06.2018, 12:05
    #39664108
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
Vlad_Molodoj,

суть задачи guid -> int что ли?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.06.2018, 13:34
    #39664205
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
Vlad_Molodoj,

попробуйте переписать функцию как inline табличную. Скалярная препятствует созданию параллельного плана выполнения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.06.2018, 15:23
    #39664305
Kopelly
Kopelly
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.06.2018, 15:57
    #39664326
Kopelly
Kopelly
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
Хотя простое
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
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 раза менее затратное.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.06.2018, 00:24
    #39664515
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
KopellyХотя простое
...
Планировщик оценивает как в 4.5 раза менее затратное.Кто бы сомневался.

Vlad_Molodoj,

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

Скалярные функции будут медленные, даже если функция будет содержать только код return 0, это бай дизайн
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.06.2018, 10:45
    #39664576
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
Интересно, сколько еще раз надо написать об инлайн-функции?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.06.2018, 17:40
    #39664663
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
Vlad_MolodojМожно ли каким-то образом ускорить обновление таблицы?
Какие есть идеи оптимизации?Во-первых, ваша первая функция неверна - не учитывается '0'.

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

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

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

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

В общем, есть задачи, которые на чистом T-SQL решаются плохо. Ваша как раз из таких.
Если действительно нужно существенно ускорить выполнение - пишите функцию конвертации на SQLCLR - будет быстрее всего вышеперечисленного.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.06.2018, 18:10
    #39664672
Сон Веры Павловны
Сон Веры Павловны
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MS SQL 2016 оптимизация функции
invmВо-первых, ваша первая функция неверна - не учитывается '0'.
Во-вторых, 6-разрядное 36-значное число в общем случае не влезет в int.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / MS SQL 2016 оптимизация функции / 15 сообщений из 15, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]