ReSQL.ru
     
powered by simpleCommunicator - 2.0.18     © 2024 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Map
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Просьба помочь в оптимизации SQL-запроса
20 сообщений из 20, страница 1 из 1
Просьба помочь в оптимизации SQL-запроса
    #40136264
Explosion
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Explosion
Гость
Приветствую. Прошу помощи в оптимизации SQL-запроса.
Имеется таблица со студентами Students. Студенты в ней уникальные, не повторяются.
Имеется таблица Archive, в которой студенты могут многократно повторятся.
Таблица Student соединяется по первичному ключу id (кластерный индекс) с таблицей Archive по полю id_student (не кластерный индекс).
У таблицы Arhive есть отчетный период, поле hdr_id.
Задача: необходимо найти в таблице Students тех студентов, которые в заданном отчётном периоде нет в таблице Archive.

Запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT s.fam,
            s.im, 
            s.ot,
            s.dr
FROM Students s
LEFT JOIN Archive a ON   a.hdr_id = 434
                              AND a.id_student = s.id                               
WHERE a.id_student is null



Запрос выполняет то, что требуется, но выполняется очень долго.
План говорит, что сначала выполняется это условие из LEFT JOIN:
Код: sql
1.
a.id_student = s.id

, – и только потом всё остальное. Соединяется больше миллиарда записей.
Как заставить план в LEFT JOIN выполнять сначала это условие:
Код: sql
1.
a.hdr_id = 434

? Останется около 1000 записей и их в миллион раз легче будет джойнить, чем миллиард.

В инструкции прочёл, что можно явно указывать PLAN и последовательность выполнения запроса. Но моему уму не поддаётся инструкция..
Правильно ли я понял, что NATURAL в плане это аналог SCAN в ms sql (пробегается по всем записям)? Как называется аналог Seek?

Можете доработать запрос, отталкиваясь от максимального быстродействия, используя PLAN / JOIN PLAN (мб вообще без плана есть способ для ускорения оО)?
...
Рейтинг: 0 / 0
24.02.2022, 15:38
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136265
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
Explosion,

какой план, какие индексы есть?
www.youtube.com/watch?v=0KITHwMNDtw

Вообще при a left join b ПРИНЦИПИАЛЬНО таблица A берется целиком, если по ней нет условий отбора.
У вас, видимо, индекса по a.hdr_id нет. Ну и ...
...
Рейтинг: 0 / 0
24.02.2022, 15:42
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136267
Мимопроходящий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мимопроходящий
Участник
JOIN выкинуть
переписать на WHERE NOT EXISTS()
...
Рейтинг: 0 / 0
24.02.2022, 15:54
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136290
Фотография Старый плюшевый мишка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Старый плюшевый мишка
Участник
Какие-то дешёвые курсовики пошли. В прошлом годе позабористей были.
...
Рейтинг: 0 / 0
24.02.2022, 18:18
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136291
Гаджимурадов Рустам
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гаджимурадов Рустам
Участник
Это, наверное, не курсовик, а лабы.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
24.02.2022, 18:38
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136295
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
Гаджимурадов Рустам,

"Соединяется больше миллиарда записей."

миллиард студентов? Китайская лаба?
...
Рейтинг: 0 / 0
24.02.2022, 19:13
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136312
Гаджимурадов Рустам
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гаджимурадов Рустам
Участник
Это граничные условия, наверное.
В реале там и миллиона нет, наверняка.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
24.02.2022, 22:11
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136331
Explosion
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Explosion
Гость
kdv
Explosion,

какой план, какие индексы есть?

По индексам ведь писал:
- id (кластерный индекс) из таблицы Student;
- id_student (не кластерный индекс) из таблицы Archive;
- Также добавлю, что фио+др составной некластерный индекс.

МимопроходящийJOIN выкинуть
переписать на WHERE NOT EXISTS()
Поменял на:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT s.fam,
            s.im, 
            s.ot,
            s.dr
FROM Students s                              
WHERE not exists(SELECT 1 FROM Archive
                           WHERE a.hdr_id = 434
                               AND a.id_student = s.id)


Также пробовал при помощи CTE:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
WITH screenJan(s_id) AS (
    SELECT a.id_student
    FROM Archive a
    WHERE a.hdr_id = 434
)

SELECT s.fam,
            s.im, 
            s.ot,
            s.dr
FROM Students s
LEFT JOIN screenJan j ON j.s_id = s.id
WHERE j.s_id is null


Not Exists отработал быстрее.

Подскажите есть ли в Firebird возможность разбивать базу на файловые группы?
...
Рейтинг: 0 / 0
25.02.2022, 07:05
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136335
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Explosion,

1. В Firebird нет кластерных индесов
2. Файловых групп тоже нет
...
Рейтинг: 0 / 0
25.02.2022, 08:26
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136336
Basil A. Sidorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Basil A. Sidorov
Участник
Explosion
Подскажите есть ли в Firebird возможность разбивать базу на файловые группы?
Технически - да , а практически многофайловые базы существовали для преодоления ограничения в 2(4)ГБ на файл.
...
Рейтинг: 0 / 0
25.02.2022, 08:27
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136366
Фотография Старый плюшевый мишка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Старый плюшевый мишка
Участник
Гаджимурадов Рустам
Это граничные условия, наверное.
В реале там и миллиона нет, наверняка.


В реале там тыщ 10. Студентов. Миллиард в архиве событий. У нас в институте стипендию давали 23-го числа. Таки локальный ЛЭТИшный анек "Дневник студента":

20 число - кушать хочется.
21 число - кушать хочется.
22 число - кушать хочется.
23 число - не помню.
24 число - не помню.
25 число - кушать хочется.
...
Рейтинг: 0 / 0
25.02.2022, 11:32
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136369
Фотография Старый плюшевый мишка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Старый плюшевый мишка
Участник
Explosion

По индексам ведь писал:
- id (кластерный индекс) из таблицы Student;
- id_student (не кластерный индекс) из таблицы Archive;
- Также добавлю, что фио+др составной некластерный индекс.


По части кластерности - трудно найти чёрную кошку в тёмной комнате, особенно если её там нет. По части фио+др вопрос имею - какое отношение этот индекс имеет к присоединению архива по двум id?
...
Рейтинг: 0 / 0
25.02.2022, 11:36
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136373
Фотография Дегтярев Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дегтярев Евгений
Участник
забористо
...
Рейтинг: 0 / 0
25.02.2022, 11:40
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136386
Explosion
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Explosion
Гость
Симонов Денис
Explosion,

1. В Firebird нет кластерных индесов

Спасибо за подсказку, Денис. Думал как в ms sql, если сделать первичный ключ через интерфейс management studio, автоматически навешается кластерный индекс.

Старый плюшевый мишкаПо части фио+др вопрос имею - какое отношение этот индекс имеет к присоединению архива по двум id?
К присоединению никакого отношения не имеет. В предыдущем вопросе спрашивалось какие индексы имеются, перечислил все.

Скажите, вариант работы через таблицу из пункта WITH медленнее NOT EXISTS потому что табличка из CTE не использует индексы?
...
Рейтинг: 0 / 0
25.02.2022, 12:09
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136394
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Explosion,

нет. Потому что NOT EXISTS моментально прекращает поиск если найдена хоть одна запись и выдаёт false, в то время как left join сначала всё присоединит что найдёт, а только потом начнёт проверять что записей нет (a.id_student is null). Хотя это не всегда так. Всё зависит от того чего больше найденных записей или нет

И да попробуй вот так

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
WITH screenJan(s_id) AS (
    SELECT a.id_student
    FROM Archive a
    WHERE a.hdr_id = 434
    GROUP BY a.id_student
)

SELECT s.fam,
            s.im, 
            s.ot,
            s.dr
FROM Students s
LEFT JOIN screenJan j ON j.s_id = s.id
WHERE j.s_id is null
...
Рейтинг: 0 / 0
25.02.2022, 12:28
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136417
Фотография Старый плюшевый мишка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Старый плюшевый мишка
Участник
Explosion

К присоединению никакого отношения не имеет. В предыдущем вопросе спрашивалось какие индексы имеются, перечислил все.

Скажите, вариант работы через таблицу из пункта WITH медленнее NOT EXISTS потому что табличка из CTE не использует индексы?


Да как же ей использовать индексы, если нужных для этого запроса нет? Чтобы это работало нужен однозначно индекс по id_student на архиве. Нужен ли по hdr_id - надо смотреть на его селективность. Если там в этом миллиарде три значения hdr_id - не только не нужен, но и вреден. Тогда, возможно, будет полезен композит student_id, hdr_id, а не просто student_id. И да, если на архиве есть FK на студентов и на хыдыры, то это значит, что индексы таки есть. Тогда для начала надо вместо беллетристики показать DDL этих таблиц и планы, которые строит оптимизатор.
...
Рейтинг: 0 / 0
25.02.2022, 14:32
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136649
Explosion
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Explosion
Гость
Старый плюшевый мишка

Да как же ей использовать индексы, если нужных для этого запроса нет? Чтобы это работало нужен однозначно индекс по id_student на архиве.

В первом и шестом сообщение писал, что индекс стоит.

Старый плюшевый мишка

Нужен ли по hdr_id - надо смотреть на его селективность.

Про этот индекс не писал, он есть. Статистика уникальности индекса примерно 0,014, поэтому решил поставить.

Старый плюшевый мишка

Тогда для начала надо вместо беллетристики показать DDL этих таблиц и планы, которые строит оптимизатор.

Подскажите как вывести план?
Есть команда для вывода плана без запроса "set planonly".
Правильно ли всё понял, в IB Expert открываю New SQL Editor, пишу:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
set planonly;
-- запрос
WITH screenJan(s_id) AS (
    SELECT a.id_student
    FROM Archive a
    WHERE a.hdr_id = 434
    GROUP BY a.id_student
)

SELECT s.fam,
            s.im, 
            s.ot,
            s.dr
FROM Students s
LEFT JOIN screenJan j ON j.s_id = s.id
WHERE j.s_id is null


На команду planonly ругается. Firebird 2.5.
...
Рейтинг: 0 / 0
28.02.2022, 08:03
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136650
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Explosion,

Это команда isql. В эксперт план и без неё выводится рядом со статистикой выполнения
...
Рейтинг: 0 / 0
28.02.2022, 08:30
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136671
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
ExplosionПравильно ли всё понял, в IB Expert открываю New SQL Editor, пишу:
у ибэксперта в sql editor жмем кнопку Prepare {}, смотрим план и explained (в 3 и выше).
...
Рейтинг: 0 / 0
28.02.2022, 11:50
| Ответить | Цитировать | Написать  
Просьба помочь в оптимизации SQL-запроса
    #40136886
Фотография Старый плюшевый мишка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Старый плюшевый мишка
Участник
Explosion

В первом и шестом сообщение писал, что индекс стоит.


Ваша правда, затупил, не перечитал первый пост. Издержки эпизодического заглядывания сюда, извиняюсь. Тогда, по идее, оптимизатору просто деваться некуда, запрос простой как мычание. Если объёмы действительно огромные, есть смысл подумать об эффектах второго порядка - распределении значений hdr в индексе на архиве, например. Если это 434 - 90%, а остальные по крошке, то при общей хорошей селективности именно на нём будут тормоза. Ну и размер страницы для баз с малым количеством таблиц, но содержащих огромные объёмы данных, надо увеличивать, возможно, до упора. Ну и к слову - в Эксперте в редакторе таблиц есть закладка DDL. Чем рассказывать что там да как, гораздо эффективней скопипастить всё оттуда. Не относящиеся к делу неиндексированные атрибуты, если их много, лучше отрезать, чтоб глаза не застили, триггера при обсуждении селектов тоже. В этой теме нужны, собственно, констрейнты, индексы и поля, на которых они живут.
...
Рейтинг: 0 / 0
01.03.2022, 13:04
| Ответить | Цитировать | Написать  
20 сообщений из 20, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Просьба помочь в оптимизации SQL-запроса
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (1): Анонимы (1)
Читали форум (3): Анонимы (2), Yandex Bot 9 мин.
Пользователи онлайн (9): Анонимы (6), Bing Bot 1 мин., Yandex Bot 2 мин., Google Bot 9 мин.
Польз. соглашение
NoSQL.ru, Чат Кролика       Пашэ: Рисунки, Чат       Фотоальбом: Участники, Встречи
Закрыть
Start [/forum/topic.php?fid=40&tid=1559810]:
 0ms
get settings:
 1ms
get forum list:
 7ms
check forum access:
 0ms
check topic access:
 0ms
track hit:
 29ms
get topic data:
 6ms
get forum data:
 1ms
get page messages:
 20ms
update_topic_read_status (1559810): 01.03.2022 13:04:52:
 0ms
get tp. blocked users:
 0ms
get online users:
 18ms
check new:
 1ms
others: 95ms
total:  178ms
созд. / загр.: 178ms (нв.: 1ms)
x
x
Закрыть


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