ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / MS SQL 2016 оптимизация функции
15 сообщений из 15, страница 1 из 1
MS SQL 2016 оптимизация функции
    #39663858
Vlad_Molodoj
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad_Molodoj
Гость
Ув. Коллеги! Как можно оптимальнее с точки зрения быстродействия, решить следующую задачу: Дана таблица:
Код: 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, 01:09
| Ответить | Цитировать | Написать  
MS SQL 2016 оптимизация функции
    #39663869
Idol_111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Idol_111
Участник
Vlad_Molodoj,
ну по хорошему надо бы план посмотреть, где затык. Может статься, что все уходит на запись (IO) 100млн и улучшить можно лишь на пару процентов.

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

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

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

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

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

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

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

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

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

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

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

попробуйте переписать функцию как inline табличную. Скалярная препятствует созданию параллельного плана выполнения.
...
Рейтинг: 0 / 0
22.06.2018, 13:34
| Ответить | Цитировать | Написать  
MS SQL 2016 оптимизация функции
    #39664305
Kopelly
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kopelly
Участник
Код: 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:23
| Ответить | Цитировать | Написать  
MS SQL 2016 оптимизация функции
    #39664326
Kopelly
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kopelly
Участник
Хотя простое
Код: 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
22.06.2018, 15:57
| Ответить | Цитировать | Написать  
MS SQL 2016 оптимизация функции
    #39664515
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
KopellyХотя простое
...
Планировщик оценивает как в 4.5 раза менее затратное.Кто бы сомневался.

Vlad_Molodoj,

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

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

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

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

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

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

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


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