Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Проталкивание внешней сортировки / 22 сообщений из 22, страница 1 из 1
25.06.2015, 15:56
    #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:59
    #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
| Ответить | Цитировать | Написать  
26.06.2015, 14:06
    #38993661
Romanzek
Romanzek
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Проталкивание внешней сортировки
Симонов Денис,

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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


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


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