ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Проталкивание внешней сортировки
22 сообщений из 22, страница 1 из 1
Проталкивание внешней сортировки
    #38992845
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Имеем два запроса

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SELECT
    SUN.NAME AS NAME,
    SUN.BRAND AS BRAND,
    FATHER.GPB_NUMBER AS FATHERGPB_NUMBER,
    FATHER.NAME AS FATHERNAME,
    FATHER.BRAND AS FATHERBRAND,
    MOTHER.GPB_NUMBER AS MOTHERGPB_NUMBER,
    MOTHER.NAME AS MOTHERNAME,
    MOTHER.BRAND AS MOTHERBRAND,
    SUN.NAME_EN AS NAME_EN,
    SEX.SHORTNAME AS SEXSHORTNAME,
    SUN.DEATHDATE AS DEATHDATE,
    SUN.BIRTHDAY AS BIRTHDAY
FROM
    HORSE SUN
    JOIN SEX ON SUN.CODE_SEX = SEX.CODE_SEX
    JOIN HORSE FATHER ON SUN.CODE_FATHER = FATHER.CODE_HORSE
    JOIN HORSE MOTHER ON SUN.CODE_MOTHER = MOTHER.CODE_HORSE
WHERE SUN.CODE_DEPARTURE  = 1
ORDER BY SUN.NAME, SUN.BIRTHDAY



с планом

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
Select Expression
    -> Sort (record length: 484, key length: 68)
        ->  Nested Loop Join (inner)
            -> Table "SEX" Full Scan
            -> Filter
                -> Table "HORSE" as "SUN" Access By ID
                    -> Bitmap And
                        -> Bitmap
                            -> Index "FK_HORSE_SEX" Range Scan (full match)
                        -> Bitmap
                            -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
            -> Filter
                -> Table "HORSE" as "FATHER" Access By ID
                    -> Bitmap
                        -> Index "PK_HORSE" Unique Scan
            -> Filter
                -> Table "HORSE" as "MOTHER" Access By ID
                    -> Bitmap
                        -> Index "PK_HORSE" Unique Scan

и

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SELECT
    SUN.NAME AS NAME,
    SUN.BRAND AS BRAND,
    FATHER.GPB_NUMBER AS FATHERGPB_NUMBER,
    FATHER.NAME AS FATHERNAME,
    FATHER.BRAND AS FATHERBRAND,
    MOTHER.GPB_NUMBER AS MOTHERGPB_NUMBER,
    MOTHER.NAME AS MOTHERNAME,
    MOTHER.BRAND AS MOTHERBRAND,
    SUN.NAME_EN AS NAME_EN,
    SEX.SHORTNAME AS SEXSHORTNAME,
    SUN.DEATHDATE AS DEATHDATE,
    SUN.BIRTHDAY AS BIRTHDAY
FROM
    HORSE SUN
    JOIN SEX ON SUN.CODE_SEX = SEX.CODE_SEX
    LEFT JOIN HORSE FATHER ON SUN.CODE_FATHER = FATHER.CODE_HORSE
    LEFT JOIN HORSE MOTHER ON SUN.CODE_MOTHER = MOTHER.CODE_HORSE
WHERE SUN.CODE_DEPARTURE  = 1
ORDER BY SUN.NAME, SUN.BIRTHDAY



с планом

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
Select Expression
    -> Filter
        ->  Nested Loop Join (outer)
            ->  Nested Loop Join (outer)
                -> Sort (record length: 260, key length: 68)
                    ->  Nested Loop Join (inner)
                        -> Table "SEX" Full Scan
                        -> Filter
                            -> Table "HORSE" as "SUN" Access By ID
                                -> Bitmap And
                                    -> Bitmap
                                        -> Index "FK_HORSE_SEX" Range Scan (full match)
                                    -> Bitmap
                                        -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                -> Filter
                    -> Table "HORSE" as "FATHER" Access By ID
                        -> Bitmap
                            -> Index "PK_HORSE" Unique Scan
            -> Filter
                -> Table "HORSE" as "MOTHER" Access By ID
                    -> Bitmap
                        -> Index "PK_HORSE" Unique Scan

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

Вот тут у меня возник собственно вопрос. А нельзя ли тоже самое проделать для внутренних джойнов? Т.е. для первого случая. Раз уж там порядок соединения определился так что FATHER и MOTHER всё равно присоединяются потом. Почему бы для первого случая плану не быть таким

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
Select Expression
    ->  Nested Loop Join (inner)
        -> Sort (record length: 260, key length: 68)
            ->  Nested Loop Join (inner)
                -> Table "SEX" Full Scan
                    -> Filter
                        -> Table "HORSE" as "SUN" Access By ID
                            -> Bitmap And
                                -> Bitmap
                                    -> Index "FK_HORSE_SEX" Range Scan (full match)
                                -> Bitmap
                                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
            -> Filter
                -> Table "HORSE" as "FATHER" Access By ID
                    -> Bitmap
                        -> Index "PK_HORSE" Unique Scan
            -> Filter
                -> Table "HORSE" as "MOTHER" Access By ID
                    -> Bitmap
                        -> Index "PK_HORSE" Unique Scan
...
Рейтинг: 0 / 0
25.06.2015, 15:56
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38992851
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
последний план не копировал а набирал руками, поэтому там могла быть ошибка. По идее отформатирован он должен быть как-то так

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
Select Expression
    ->  Nested Loop Join (inner)
        -> Sort (record length: 260, key length: 68)
            ->  Nested Loop Join (inner)
                -> Table "SEX" Full Scan
                    -> Filter
                        -> Table "HORSE" as "SUN" Access By ID
                            -> Bitmap And
                                -> Bitmap
                                    -> Index "FK_HORSE_SEX" Range Scan (full match)
                                -> Bitmap
                                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
        -> Filter
            -> Table "HORSE" as "FATHER" Access By ID
                -> Bitmap
                    -> Index "PK_HORSE" Unique Scan
        -> Filter
            -> Table "HORSE" as "MOTHER" Access By ID
                -> Bitmap
                    -> Index "PK_HORSE" Unique Scan
...
Рейтинг: 0 / 0
25.06.2015, 15:59
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993661
Romanzek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romanzek
Участник
Симонов Денис,

В первом случае Ваши JOIN являтся фильтрами, ограничивающими выборку. И они по индексу. Логично предположить, что после их наложения результат может уменьшиться и сортировать придется меньше строк. Сервер собственно так и поступает, что вполне логично.

Если Вы сделаете индекс в направлении сортировки на табличке, то план может еще другой вид принять и он будет разный для этих двух видов запросов. Также он возможно будет различным при использовании подсказок оптимизатору (либо будет индекс использовать для сортировки, либо нет). Правда все это зависит от наличия нужных индексов по объединяемым полям.
...
Рейтинг: 0 / 0
26.06.2015, 14:06
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993670
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Romanzek,

ну так я и не предлагаю это делать всегда и безапелляционно. Оптимизатор он на то и оптимизатор чтобы просчитывать наименьшую стоимость. Конкретно в моём случае выборка не уменьшается и не увеличивается. CODE_FATHER и CODE_MOTHER всегда принимают значения. Т.е. NULL заменён на псевдо записи с кодами -2 и -3
...
Рейтинг: 0 / 0
26.06.2015, 14:11
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993782
Romanzek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romanzek
Участник
Симонов Денис,

Уменьшается выборка в случае отсутствия в таблицах строк, удовлетворяющих предикату.
Если записи всегда имеют связь 1 к одному, то можно в таких случаях заменять JOIN на LEFT JOIN, однако нежелательно. В ранних версиях FB это применяли, в последних (2.1 и новее) уже оптимизатор вполне справляется, так как учитывает статистику по индексам корректно. В РедБазе также была доработка, позволяющая эффективно использовать индексы по NULLABLE полям с учетом статистики значений NULL (например для случаев, когда все значения столбца NULL - в старом варианте статистика была 1 и оптимизатор не использовал такие индексы).
...
Рейтинг: 0 / 0
26.06.2015, 15:04
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993785
Romanzek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romanzek
Участник
А у вас, кстати, какие отличия в скорости выполнения? Приложите Performance Statitstics.
...
Рейтинг: 0 / 0
26.06.2015, 15:06
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993813
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Romanzek,

запрос с внутренними джойнами

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
План
PLAN SORT (JOIN (SEX NATURAL, SUN INDEX (FK_HORSE_SEX, FK_HORSE_DEPARTURE), FATHER INDEX (PK_HORSE), MOTHER INDEX (PK_HORSE)))

------ Информация о производительности ------
Время подготовки запроса = 0ms
Время выполнения запроса = 1s 123ms
Среднее время на получение одной записи = 51,05 ms
Current memory = 602 724 888
Max memory = 602 778 288
Memory buffers = 32 768
Reads from disk to cache = 0
Writes from cache to disk = 0
Чтений из кэша = 930 734

запрос с внешними джойнами

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
План
PLAN JOIN (JOIN (SORT (JOIN (SEX NATURAL, SUN INDEX (FK_HORSE_SEX, FK_HORSE_DEPARTURE))), FATHER INDEX (PK_HORSE)), MOTHER INDEX (PK_HORSE))

------ Информация о производительности ------
Время подготовки запроса = 0ms
Время выполнения запроса = 312ms
Среднее время на получение одной записи = 14,18 ms
Current memory = 586 937 144
Max memory = 602 778 288
Memory buffers = 32 768
Reads from disk to cache = 0
Writes from cache to disk = 0
Чтений из кэша = 175 469

на самом деле эта статистика без FetchAll. Запрос с внешними джойнами позволяет получить первую порцию записей быстрей. Когда делаешь FetchAll то по времени никакой разницы уже нет. Разве что по фетчам и памяти. Но у меня TempCacheLimit в гигабайт выставлен.
...
Рейтинг: 0 / 0
26.06.2015, 15:23
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993817
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Romanzek,

Вопрос чисто теоретический. К практике отношения не имеет ибо на самом деле он чуток другой и там всё быстро работает.
...
Рейтинг: 0 / 0
26.06.2015, 15:24
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993842
Romanzek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romanzek
Участник
Симонов Денис,

На самом деле, отличия все равно скорее всего есть, по скорости сортировки, так как в FB скорость сортировки зависит от количества участвующих в результирующем наборе полей (сортировка ведется всего набора, а не только тех полей, которые в ней участвуют). В РедБазе это исправлено, но имеется ряд ограничений, снимающихся консистентными стейтментами. Если выборка вся будет читаться - то Вам в общем-то все равно, разница в данном случае будет незаметной (мало полей в выборке). Если первые несколько строк, то придется либо LEFT JOIN лепить, либо при помощи +0 загонять под нужный план, либо использовать подсказки оптимизатору.
...
Рейтинг: 0 / 0
26.06.2015, 15:41
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993844
Romanzek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romanzek
Участник
Ах да, есть еще first :) Он тоже будет подсказкой.
...
Рейтинг: 0 / 0
26.06.2015, 15:43
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993855
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Romanzek,

у вас же там нативные подсказки прилепили для стратегии FIRST/ALL ROWS. Для FB dimitr почему-то не стал их добавлять
...
Рейтинг: 0 / 0
26.06.2015, 15:46
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993870
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
RomanzekНа самом деле, отличия все равно скорее всего есть, по скорости сортировки, так как в FB скорость сортировки зависит от количества участвующих в результирующем наборе полей (сортировка ведется всего набора, а не только тех полей, которые в ней участвуют). В РедБазе это исправлено, но имеется ряд ограничений, снимающихся консистентными стейтментами.

несколько лет назад ДЕ выкладывал пропатченную версию где именно так и делалось. Но после тестирования оказалось что это не всегда благо. А оптимизатор распознать когда такой подход применять, а когда старый пока не может.
...
Рейтинг: 0 / 0
26.06.2015, 15:54
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993902
Romanzek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romanzek
Участник
Симонов Денис,

Да, изменение алгоритма сортировки приводит к проблемам на неконсистентных курсорах. Эти две доработки надо в паре брать. Точнее первая без второй имеет ряд ограничений. В случае с LEFT JOIN тоже может быть такая проблема, но она уже приведет к ошибочному результату на неконсистентном запросе (пусть Дмитрий поправит, если я неправ). То есть может получиться так, что ваш запрос с inner join всегда возвращает родителя для всех экземпляров, а в случае с LEFT JOIN вы можете получить NULL на достаточно большой выборке именно из-за сортировки, затащенной внутрь. Так что это еще вопрос - благо ли это или нет.

Да, подсказки есть. Оператор first работает как стратегия optimize for first rows.
...
Рейтинг: 0 / 0
26.06.2015, 16:10
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993929
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Romanzek,

я говорил не о возможных ошибках, а о том что новый алгоритм сортировки не всегда выигрывал у старого.
...
Рейтинг: 0 / 0
26.06.2015, 16:22
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993941
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Romanzek,

раз уж заговорили о стабильном курсоре в RC.

У меня по поводу того патча который делает его стабильным беспокойство осталось. Долгая RO RC транзакция теперь удерживает сборку мусора? Вроде да. И чтобы уменьшить эту проблему была введена промежуточная сборка мусора. В какие моменты она срабатывает и за счёт чего долгие RO RC транзакции не будут приводить к проблемам с производительностью?
...
Рейтинг: 0 / 0
26.06.2015, 16:27
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993943
Romanzek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romanzek
Участник
Симонов Денис,

Да, действительно это так. Но доработка делалась с учетом этих особенностей, некоторая эмпирика там присутствует. Скорее всего таких случаев осталось немного и разница не будет существенной. Выигрыш же на больших и широких выборках просто огромен.
...
Рейтинг: 0 / 0
26.06.2015, 16:28
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38993995
Romanzek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romanzek
Участник
Симонов ДенисДолгая RO RC транзакция теперь удерживает сборку мусора? Вроде да.
Пока открыт курсор.
Симонов ДенисИ чтобы уменьшить эту проблему была введена промежуточная сборка мусора.
Если я правильно понимаю, о какой промежуточной сборке мусора речь, то этот патч пока еще не готов и не был влит. Точнее был влит, но потом откачен для доработки. Очень бы хотелось его доработать, но пока нет ресурса.
...
Рейтинг: 0 / 0
26.06.2015, 16:50
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38994032
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
RomanzekСимонов ДенисДолгая RO RC транзакция теперь удерживает сборку мусора? Вроде да.
Пока открыт курсор.


вот это и плохо. Большинство программ на Delphi открывают RO RC и не парятся. Таким образом, можно получать недофетченный курсор и дофетчивать когда потребуется, при этом сборка мусора не удерживается.

А теперь получается что все эти проги придётся переписывать чтобы не огрести проблемы с производительностью.
...
Рейтинг: 0 / 0
26.06.2015, 17:11
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38994041
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Симонов ДенисБольшинство программ на Delphi открывают RO RC и не парятся.
А всё потому, что кое-кто, находясь в эйфории от этой фичи, в своё время порекомендовал
так делать. Причём совершенно зря.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
26.06.2015, 17:17
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38994054
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Dimitry Sibiryakov,

да в новых проектах можно и писать по новому. Но вот с унаследованными как быть
...
Рейтинг: 0 / 0
26.06.2015, 17:28
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38994161
Romanzek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romanzek
Участник
Симонов Денис
вот это и плохо. Большинство программ на Delphi открывают RO RC и не парятся. Таким образом, можно получать недофетченный курсор и дофетчивать когда потребуется, при этом сборка мусора не удерживается.


И какое время живет такой курсор? Я так понимаю, что это клиентское приложение, то есть максимум - рабочий день? Да и то пользователь наверняка обновляет данные, в результате чего курсор переоткрывается.
...
Рейтинг: 0 / 0
26.06.2015, 19:38
| Ответить | Цитировать | Написать  
Проталкивание внешней сортировки
    #38994479
dimitr
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dimitr
Участник
Симонов ДенисА нельзя ли тоже самое проделать для внутренних джойнов?
в текущем коде нельзя, увы. Сейчас сортировка проталкивается для еще неоптимизированного джойна, который для outer априори не изменится, а вот для inner очень даже может.
...
Рейтинг: 0 / 0
28.06.2015, 12:46
| Ответить | Цитировать | Написать  
22 сообщений из 22, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Проталкивание внешней сортировки
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&msg=38993782&tid=1562754]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 44ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 57ms
get tp. blocked users:
 1ms
others: 13ms
total:  153ms
созд. / загр.: 153ms
x
x
Закрыть


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