0. dmurk 90 05.07.16 16:33 Сейчас в теме

Ускорение запросов к СУБД при помощи горизонтального масштабирования

В статье речь пойдет о том, как ускорять запросы, имея в руках только платформу 1С, и рассмотрим проблемы достижения предельной производительности, когда запрос к СУБД уже оптимизирован с использованием стандартных методик по оптимизации

Перейти к публикации

Комментарии
Избранное Подписка Сортировка: Древо
1. Ganjubas 13.07.16 22:25 Сейчас в теме
А где можно ознакомиться с практическими примерами данной методики? И есть ли более развернутый пример с оптимизацией кода (в т.ч. запроса) обработки "Поиск и замена дублей" ?
2. Alien_job 157 14.07.16 06:52 Сейчас в теме
На каком основании вы делаете допущение что все ссылки одной секции всегда уникальны по отношению к ссылкам других секций? Если не обосновать этот момент то после применения "оптимизированного" алгоритма придется вновь вызывать нормальный
3. dmurk 90 14.07.16 10:35 Сейчас в теме
(2) Alien_job, когда вы выбираете первую тысячу записей, в ней не будет ссылок из второй тысячи записей ))
8. Alien_job 157 15.07.16 06:56 Сейчас в теме
(3) Хм.. я имел ввиду другое. Вы же дубли удаляете - уникальность ПО T1.НазначениеСостояния = Т2.НазначениеСостояния. У вас в результате оптимизированной обработки в каждой выбранной секции назначения состояния будут разными. Но после объединения результатов итоговая таблица будет полна "дублей" ПО T1.НазначениеСостояния = Т2.НазначениеСостояния примерно по 28 на каждое назначение состояния
14. dmurk 90 15.07.16 14:14 Сейчас в теме
(8) Alien_job, именно так. Но сложность задачи уже уменьшена
20. Alien_job 157 15.07.16 14:27 Сейчас в теме
(14) Конкретно для ваших данных (тысячи дублей одного и того же) такой подход уменьшит сложность, но если дубли распределены равномерно (по 5 дублей на каждое НазначениеСостояния ) то такой подход ничего не даст
22. dmurk 90 15.07.16 14:29 Сейчас в теме
23. dmurk 90 15.07.16 14:30 Сейчас в теме
(20) Alien_job, хотя, можно сгенерировать и проверить ))
5. ValeriTim 20 14.07.16 11:01 Сейчас в теме
(2) Alien_job, Ну как бы выбирает из одного индексированного источника ...
6. ValeriTim 20 14.07.16 11:05 Сейчас в теме
А вообще не до конца понятно как же оптимизиорован запрос ... конечный результат нужно было в статью ... и с кодом
15. dmurk 90 15.07.16 14:17 Сейчас в теме
(6) ValeriTim, Боюсь, формат доклада для аудитории не подразумевал многостраничных текстов. Весь проект можно скачать: http://www.dudin.by/Technodemo.zip
7. ildarovich 6204 14.07.16 23:25 Сейчас в теме
Что я понял: в запросе можно
1) поделить исходную таблицу на секции приемом "ВЫБРАТЬ ПЕРВЫЕ 100 Х ИЗ (ВЫБРАТЬ ПОСЛЕДНИЕ 1000 Х)",
2) обработать секции отдельными подзапросами,
3) а затем собрать результат юнионами.
Можно надеяться, что при этом СУБД использует параллелизм и будет быстрее.
Что я не понял:
Зачем нужен запрос:
ВЫБРАТЬ РАЗЛИЧНЫЕ
  Т1.НазначениеСостояния КАК ЗначениеРеквизита
  ИЗ
    Справочник.НазначениеСостояния КАК Т1 
      ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.НазначениеСостояния КАК Т2
        ПО T1.НазначениеСостояния = Т2.НазначениеСостояния И 
          Т1.Ссылка <> Т2.Ссылка

Если та же задача решается запросом:
ВЫБРАТЬ
  НазначениеСостояния
    ИЗ Справочник.НазначениеСостояния
СГРУППИРОВАТЬ ПО
  НазначениеСостояния
ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Ссылка) > 1
Zhilyakovdr; zqzq; gadjik; soulsteps; i_lo; BigB; bulpi; starik-2005; v3rter; h00k; +10 Ответить
17. dmurk 90 15.07.16 14:19 Сейчас в теме
(7) ildarovich, первая часть кода - написана разработчиками 1С. Действительно, и зачем так писать? )))
18. dmurk 90 15.07.16 14:21 Сейчас в теме
(7) ildarovich, по второй части кода рекомендую скачать проект и попробовать применить ваш вариант кода
24. ildarovich 6204 15.07.16 14:32 Сейчас в теме
(18) скачать проект - это как? и зачем это мне?
Меня такой ответ категорически не устраивает. Да и ссылка (17) на разработчиков 1С тоже не в кассу. На то мы и внедренцы, чтобы кастомизировать решения на всякие частные случаи типа 139 тысяч дублей.
28. ildarovich 6204 15.07.16 14:37 Сейчас в теме
+(24) Кстати, по ссылке из (15) вместо проекта - сообщение
404 - файл или каталог не найден.
Запрашиваемый ресурс перемещен, переименован либо временно недоступен.
29. Alien_job 157 15.07.16 14:40 Сейчас в теме
(28) ildarovich, на 10-й раз загрузка пошла. Если постараетесь, возможно получится скачать
31. dmurk 90 16.07.16 11:20 Сейчас в теме
9. e.kogan 1837 15.07.16 10:22 Сейчас в теме
Очень хочется поставить сразу много плюсов за идею.
50. Yashazz 2300 19.07.16 16:41 Сейчас в теме
(9) e.kogan, этой идее сто лет в обед.
10. Sheff 15.07.16 12:18 Сейчас в теме
11. Alien_job 157 15.07.16 12:48 Сейчас в теме
Некорректно сравнивать время работы нормального и "оптимизорованного" подхода т.к. результат разный.
12. v3rter 15.07.16 13:03 Сейчас в теме
Длиннотекст. Суть, видимо в "если мы разделим первоначальную таблицу на секции, которые помещаются в память SQL-сервера, оптимизатор SQL каждое уникальное сравнение внутри секции будет выполнять по упрощенной схеме". И непонятно, будет ли выигрыш на алгоритмах с линейной сложностью.
16. dmurk 90 15.07.16 14:18 Сейчас в теме
(12) v3rter, идея комплексная, не упрощайте
13. starik-2005 1429 15.07.16 13:32 Сейчас в теме
Суть статьи в том, чтобы программисты использовали многопоточность. Это однозначный плюс. Минус в том, что идиотский алгоритм поиска дублей оставили идиотским и не смогли найти решения, которое бы вместо поиска дублей сравнивало не все со всем, а только с правильным. Для 139к Х 2 состояния нужно всего лишь 139к Х 2 сравнения с правильными значениями, которыми и будут заменены все остальные. Для этого достаточно выбрать различные по наименованию ((если у нас наименование является определителем уникальности) - в итоге будет 2 значения, потом выбрать максимумы или минимумы ссылок - без разницы - с данными наименованиям и считать их верными. Дальше найти все неверные и заменить. Никаких 18ккк сравнений не нужно. И это уже показывает "силу" команды внедрятелей, которые дальше совершенно правильных мыслей, которые можно было вычитать даже в моей статье о многопоточности, где я говорил о наличии мощностей на серверах 1С и SQL, которые можно и нужно утилизировать, не пошли. Алгоритмическое мышление - это то, что неприсуще многим специалистам 1С, но ведь надо как-то самосовершенствоваться, учиться, развиваться...
gubanoff; bulpi; +2 1 Ответить
19. dmurk 90 15.07.16 14:25 Сейчас в теме
(13) starik-2005, вы описываете нерабочий алгоритм. По ВЫБРАТЬ РАЗЛИЧНЫЕ вы получите ПЯТЬ наименований. И что? ))))
21. dmurk 90 15.07.16 14:28 Сейчас в теме
(13) starik-2005, применяя МАКСИМУМ или МИНИМУМ ссылок вы можете получить более одной записи в таблице. В случаях когда ссылки получены из разных узлов РИБ максимум и минимум возвращает количество строк идентичное количеству узлов исходной таблицы. Глюк-с платформы, однако, все вопросы разработчикам...
33. starik-2005 1429 16.07.16 14:35 Сейчас в теме
(21) минимум или максимум возвращают ссылку, которая в SQL - суть число. Какие-то отсылы к РИБ тут совершенно неуместны, ибо это число для разных узлов в принципе одинаково, либо это разные ссылки. И даже если "ВЫБРАТЬ РАЗЛИЧНЫЕ Наименование ИЗ Справочник.Статусы" дает нам пять значений - это не повод сравнивать все со всем, ибо в статье описана четкая задача замены дублей ссылок. Тут не нужны 18ккк сравнений если хоть немного подумать мозгом, но, как я понял, авторы мозгом думать не желают.
34. dmurk 90 17.07.16 13:33 Сейчас в теме
(33) starik-2005, вы имеете ввиду такое решение?
"ВЫБРАТЬ
| СостоянияСогласования.НазначениеСостояния,
| МИНИМУМ(СостоянияСогласования2.Ссылка) КАК СсылкаМин
|ИЗ
| Справочник.СостоянияСогласования КАК СостоянияСогласования
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.СостоянияСогласования КАК СостоянияСогласования2
| ПО СостоянияСогласования.НазначениеСостояния = СостоянияСогласования2.НазначениеСостояния
|
|СГРУППИРОВАТЬ ПО
| СостоянияСогласования.НазначениеСостояния
|
|ИМЕЮЩИЕ
| КОЛИЧЕСТВО(РАЗЛИЧНЫЕ СостоянияСогласования2.Ссылка) > 1";
36. starik-2005 1429 17.07.16 14:51 Сейчас в теме
(34) если поле "НазначениеСостояния" является уникальным, то и представленное Вами решение может подойти, полагаю, для чего-нибудь. Но если задача - это найти все "неправильные" статусы и заменить их на произвольно выбранные "правильные", то даже такого не надо - достаточно на первом шаге отобрать уникальные, на втором шаге найти минимум или максимум ссылки, чтобы привязать правильное к одной ссылке из множества. На третьем шаге найти все, отличные от "правильных" и заменить их на "правильные", сопоставив по полю уникальности. Агрегация тут при наличии множества ссылок на одинаковые статусы не нужна, на выходе (139к - количество_уникальных_статусов) неверных ссылок, которые уже можно искать в системе и менять. Дальше можно разбить их на примерно равные части и скормить приемлемому количеству потоков для изменения. Если скармливать постепенно, то можно получить примерно равнораспределенную нагрузку, если скормить сразу все, поделив случайным образом, то можно ожидать, что часть заданий отработают раньше что приведет к замедлению обработки.
37. dmurk 90 18.07.16 10:04 Сейчас в теме
(36) starik-2005, в данной задаче, на практике справочник был заполнен заново двумя элементами, и их ссылки записаны в объекты. Поднятый вопрос - адаптация стандартного ПоискИЗаменаДублей для универсального применения, но в более вменяемые сроки, для того чтобы сопровождение проекта не нагружало отдел разработки типовыми решениями.
25. bulpi 136 15.07.16 14:35 Сейчас в теме
Криворукость предыдущей команды программистов попытались исправить чуть менее криворукие. Получилась красивая диаграмка.
palsergeich; fuxic; Alien_job; +3 2 Ответить
32. dmurk 90 16.07.16 11:25 Сейчас в теме
(25) bulpi, видимо Ваш гений программирования не имеет желания публиковать свой единственной ровный вариант решения этой проблемы. Зачем что-то обсуждать, если можно обгадить? ))
26. v3rter 15.07.16 14:36 Сейчас в теме
То есть пока получается эмпирическая оптимизация запроса разбивкой по группам строк и, исходя из размера таблицы, количество процессоров, объем выделенной SQL памяти, предложить относительно оптимальную разбивку не получится?
35. dmurk 90 17.07.16 13:35 Сейчас в теме
(26) v3rter, так как я не ставил себе задач по коммерческому применению методики, то да, оптимизировано под конкретный сервер.
27. bulpi 136 15.07.16 14:36 Сейчас в теме
П.С.
Читайте ildarovich , кланяйтесь, и благодарите.
30. ildarovich 6204 15.07.16 14:42 Сейчас в теме
(27) bulpi, спасибо на добром слове, но вот кланяться точно не нужно. Идеи должны проходить апробацию, строго критиковаться, без оглядки на авторитет. Иначе большой риск допустить ошибку, которая надолго останется в коде и будет портить жизнь нам и нашим клиентам.
h00k; roofless; starik-2005; +3 Ответить
38. v3rter 18.07.16 11:35 Сейчас в теме
В любом случае новый приём оптимизации - это хорошо )
39. Dach 199 19.07.16 10:21 Сейчас в теме
Господа, хочу на Ваш суд вынести вот такой вариант решения озвученной задачи.

Попробую своими словами, немного абстрагируясь от исходной задачи.

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

Далее, я сознательно вношу некое допущение, весьма важное. Я буду считать, что внутри этих строк дубли распределены равномерно. Считаю, что чем больше строк, то тем более равномерно внутри нее распределены дубли. Конечно, это может быть не так, если например алгоритм системы, порождающий дубли - долгое время работал неправильно и создавал дубли, а затем пришел разработчик и что-то там исправил, в результате чего дубли стали рождаться намного более редко.

Да, и еще очень важно - считаю, что дубли располагаются относительно "рядом" друг с другом". Ну то есть ситуация, когда дублируются строки номер 1 и номер 7 - вероятность 99 процентов, а строки с номерами 1 и 100 - вероятность такого события 1 процент. То есть, если алгоритм, порождающий дубли - более менее цикличен, то наверное это допустимо.

В стандартном случае, если мне искать дубли, по сути, нужно каждый элемент-строку сравнить с каждой строкой из 100. Мое любимое декартово произведение "каждый с каждым", то есть в данном случае 100*100 = 10000. То есть, чтобы выявить дубли - придется сделать 10000 проходов. Запомним.

Что если решить задачу так:

1. Разбиваем наши 100 записей на части, каждая по 20 записей. 20, 20, 20, 20 и еще раз 20. 20 строк - выборка, которая охватывает возможные дубли наиболее вероятно, то есть расстояние между дублями не более 20 строк.
2. Внутри каждой выборки выполняем поиск "каждый с каждым". Получается: (20*20) * 5 = 2000 проходов.
3. Учитывая, что дубли распределены равномерно - 50 дублей должны сидеть внутри каждой выборки по 20. 50 дублей на 5 выборок по 20 - по 10 дублей "сидит" в каждой.
4. В результате, после выполнения - остается 5 выборок по 10 строк в каждой. Это, если бы не было перекрестных дублей. Пусть перекрестных дублей 10 из 50.,
тогда в каждой выборке "сидит" 8 схлопываемых дублей. То есть на самом деле, не по 10 строк остается, а по 12 строк в каждой выборке.
5. Теперь нужно избавиться от возможных" перекрестных дублей, когда дубли "сидят" в разных выборках.

Ищем "каждый с каждым", выборка №1 (все строки) - с выборкой №2 (все строки); выборка №1 (все строки) - с выборкой №3 (все строки) ; выборка №1 (все строки) - с выборкой №4 (все строки) ; выборка №1 (все строки) - с выборкой №5 (все строки) = (12*12) * 4 = 576. Ну и для каждой выборки = 576 * 5 = 2880 проходов.

Итого, чтобы выявить все дубли, при таком подходе - понадобилось 2000 + 2880 = 4880 проходов.

Что чуть более чем в 2 раз меньше, чем если делать 100 на 100.

Проблема в допущениях. Нужно быть уверенным, что А) Дубли распределены внутри таблицы равномерно Б) Определить оптимальный размер выборки, покрывающей максимальное расстояние в строках между дублями В) Насчет количества перекрестных дублей - не знаю, как посчитать, но думаю, зависимость прямо пропорциональная количеству выборок, на которое делится таблица-источник.

Как думаете, мои рассуждения имеют место быть или они ошибочны?
42. ildarovich 6204 19.07.16 13:11 Сейчас в теме
(39) Dach, рассуждения нормальные, но только вслед за автором вы пытаетесь слегка поправить решение, дополнить его, решив проблему остающихся дублей. Тогда как, по моему мнению, нужно было кардинально поменять основу - сам алгоритм решения.

Пусть у вас есть пачки банкнот (лотерейных билетов), среди которых есть фальшивые. Номера фальшивых повторяются. Они - дубли.
Мало кому придет в голову сравнивать номер каждой купюры с номерами всех других купюр. Чаще всего люди в данном случае либо сортируют купюры, когда их немного, либо заводят таблицу, в которой отмечают встретившиеся номера.
Трудоемкость последней операции, очевидно, пропорциональна числу купюр. Такой способ и предлагается в (7).

Ошибка автора, вероятно, в том, что гибкий универсальный настраиваемый алгоритм поиска дублей, нацеленный на разные варианты выявления относительно немногочисленных дублей по похожести наименований, сравнения по равенству разных реквизитов он применил к очень редкому частному случаю, на который наверняка не рассчитывали разработчики.
43. Dach 199 19.07.16 14:28 Сейчас в теме
(42) ildarovich, постойте-постойте.

Я оперирую низкоуровневым понятием "проход". Это нечто даже более детальное, чем операторы SQL-инструкций. Это именно проход в цикле.
Скажем так, я подразумеваю, что наиболее вероятным и чаще всего применяемым является алгоритм вложенных циклов.

"Чаще всего люди в данном случае либо сортируют купюры, когда их немного, либо заводят таблицу, в которой отмечают встретившиеся номера."

Ок. Начинаю перебирать номера. Номер 12345 - встретился 1 раз. Записываю в таблицу.
Номер 234 - встретился 1 раз.

*****
спустя 500 шагов цикла

Номер 12345! Как узнать, сколько раз он мне встретился? Абстрагируйтесь, плз, от всяких кэшей, соответствий, индексированных таблиц, ибо чтобы ответить на этот вопрос, все равно придется
перебрать от начала до конца (ну или до тех пор, пока не встретится) весь второй список!

Так вот, ИМХО, ключевая фраза тут "до тех пор, пока не встретится".

Как Вы думаете, как сервер на физическом уровне выполняет сортировку? А как группировку? Думаю, что да - как Вы верно подметили удачной аналогией, "записывая номер".
Но это далеко не дает гарантий, что количество проходов будет существенно меньше, чем в моем способе. Да к тому же это намного более трудно прогнозируемо, тогда как как мой способ при известных входящих данных куда более легко просчитать.
44. Dach 199 19.07.16 14:43 Сейчас в теме
(43) +

поэтому я не подпишусь, что в задаче поиска дублей в большой таблице более 100 тыс строк запрос вида

ВЫБРАТЬ
НазначениеСостояния
ИЗ Справочник.НазначениеСостояния
СГРУППИРОВАТЬ ПО
НазначениеСостояния
ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Ссылка) > 1

отработает быстрее, чем алгоритм, предложенный автором. К слову, мне кажется, он не просто так порекомендовал опробировать на реальных данных. Доберусь вечером до ноута - сам проверю в удовольствием
45. ildarovich 6204 19.07.16 14:45 Сейчас в теме
(43) Dach,
Но это далеко не дает гарантий, что количество проходов будет существенно меньше, чем в моем способе
Будет существенно меньше. Проход будет ровно один. С гарантией. Посмотрите запрос в (7). Он решает ту же самую задачу. Приведу его еще раз.
ВЫБРАТЬ
  НазначениеСостояния
    ИЗ Справочник.НазначениеСостояния
СГРУППИРОВАТЬ ПО
  НазначениеСостояния
ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Ссылка) > 1

Вы думаете, что расчет агрегата КОЛИЧЕСТВО(РАЗЛИЧНЫЕ потребует нескольких проходов по таблице и поиска? - Тогда вы ошибаетесь.
(44) Отработает ГОРАЗДО БЫСТРЕЕ - на два порядка, меньше, чем за секунду, скорее всего, то есть быстрее примерно в 200 раз.
46. Dach 199 19.07.16 14:56 Сейчас в теме
(45) ildarovich, один проход по таблице-источнику - это ок, согласен.

Для того, чтобы узнать, сколько раз в таблице-источнике встретился дубль - мы заносим анализируемое поле во второй список. Со временем второй список растет.

Ответьте на один вопрос, когда встречается дубль - для того, чтобы понять - дубль он (или "трубль" или "четверубль" ;))) ) - Вы будете заглядывать во второй список? Вы будете его перебирать или нет? Мне кажется да, потому что как иначе? Ну и в чем тогда принципиальное отличие от джойна на физическом уровне?

Да только во фразе "буду перебирать весь второй список, пока не найду искомый элемент, чтобы понять, сколько он раз встречался в первой таблице"

То есть там в цикле стоит "Прервать", грубо говоря. Вот и все.

Ну и если во втором списке наша строка сидит в самом конце - разве мне его весь не придется просмотреть?

Разве не логично рассуждаю?
47. ildarovich 6204 19.07.16 15:21 Сейчас в теме
(46) Dach, агрегатные функции считаются с использованием хэшей. Грубо говоря, найденные номера купюр заносятся в соответствие. В этом случае нет необходимости перебора списка ранее встретившихся элементов. Обращение к соответствию - это простая и быстрая операция.
48. Dach 199 19.07.16 15:25 Сейчас в теме
(47) ildarovich, то есть второй список хэшируется и индексируется? Ну, тогда вопросов нет.

Однако, способ рабочий, если возможностей использовать соответствие нет
49. ildarovich 6204 19.07.16 15:36 Сейчас в теме
(48) Dach,
если возможностей использовать соответствие нет
соответствие я привел для удобства объяснения. План запроса может и сортировку использовать и индекс-скан. Но все это будет еще быстрее или не медленнее. Квадратичной зависимости в этой задаче не должно быть, если специально не накосячить.
Способ рабочий? - А есть ли вообще этот способ, если нет задачи?
40. v3rter 19.07.16 10:49 Сейчас в теме
Дубли, считаю, неудачный пример, особенно если "задублившееся" поле индексировано.
41. Dach 199 19.07.16 11:09 Сейчас в теме
(40) v3rter, считаем, что оно не индексируемое. И таблица не сортированная
Оставьте свое сообщение
Новые вопросы с вознаграждением
Автор темы объявил вознаграждение за найденный ответ, его получит тот, кто первый поможет автору.

Вакансии

Программист 1С
Санкт-Петербург
зарплата от 130 000 руб. до 150 000 руб.
Полный день

Программист 1С
Санкт-Петербург
зарплата от 100 000 руб.
Полный день

Руководитель группы сервисов FRM на 1С
Москва
зарплата от 150 000 руб.
Полный день

Руководитель группы сервисов ЭДО, ЭЦП и криптографии
Москва
зарплата от 150 000 руб.
Полный день

Руководитель группы интеграций (1С)
Москва
зарплата от 150 000 руб.
Полный день