Как оптимизировать запрос с большим количеством join к одной и той же таблице?

@da50h · Регистрация: 30.03.2016

Студворк — интернет-сервис помощи студентам

Здравствуйте. Есть таблица вида :

id fk1 fk2

1 1 7
2 1 3
3 1 9

4 2 3
5 2 5

Нужно выбрать fk1 where fk2 = x
При этом пользователь может задать произвольное количество fk2.
То есть искать только если fk2 = 3 , или же fk2 = 3 and fk2 = 9
В чем суть моей проблемы и, собственно, вопроса:
Если пользователь задает 1 параметр для поиска, выборку делать абсолютно просто.
Я это делаю таким запросом, хотя там вложенный select и не нужен:

SQL
1
2
3
4
SELECT t1.fk1
  FROM (SELECT  fk1
          FROM tabl
         WHERE fk2 = 9) AS t1

Если пользователь задает 2 параметра для поиска (fk2 = 3 and fk2 = 9) код запроса удваивается:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT t1.fk1
FROM 
    (
     SELECT  fk1
     FROM tabl
     WHERE fk2 = 9
     ) AS t1 
 JOIN 
     (
       SELECT  fk1
       FROM tabl
       WHERE fk2 = 9
      ) AS t2 
ON (t1.fk1 = t2.fk1 )

Но если задано 3 параметра и больше(ограничения быть не может) - начинается дурдом! потому что размер запроса увеличивается в геометрической прогрессии. Как с этим можно бороться, мб есть способом заменить join чем-нибудь другим? чтоб с каждым новым параметром к предыдущему запросу добавлялся еще 1 простой запрос, а не приставка с предыдущего запроса + еще 1 select + еще 1 джоин

Добавлено через 7 минут
Прошу прощения за глупую тему. сказывается недостаток сна. Сам себе проблему придумал и сам себя напугал. можно считать тему закрытой. жаль нету такого функционала на форуме.

@invm · 16.11.2016, 18:22

Сообщение от da50h

начинается дурдом! потому что размер запроса увеличивается в геометрической прогрессии.

OMG...

T-SQL
1
2
3
4
5
6
select
 fk1
from
 tabl
where
 fk2 in (3, 9, ..., N)

@da50h · 17.11.2016, 04:19 **[ТС]**

Спасибо, конечно, но я сначала создал тему, а потом вспомнил курс по БД в часности оператор ин.

Добавлено через 6 часов 39 минут
хотел не ворошить уже, но чет задело "OMG". Тк изначально лично думал, что это рабочий вариант, решил прочитать ман. после чего понял что in не есть решением данной задачи. Прочтите внимательно условие, в выборке должен быть результат where fk2=3 AND fk2=9 но не or. То есть просто дальше к запросу добавлять ещё 1 джоин на каждый параметр.

Добавлено через 2 часа 26 минут
как оказалось чистые join работали не всегда корректно. помогло внедрение all. зы это для тех, кто попадет на эту тему с поисковиков.

@texnik-san · 17.11.2016, 08:08

SQL
1
2
3
4
5
6
SELECT  TT.fk1
FROM tabl TT
WHERE EXISTS (SELECT id FROM tabl WHERE fk1=TT.fk1 AND fk2=3)
  AND EXISTS (SELECT id FROM tabl WHERE fk1=TT.fk1 AND fk2=9)
  AND EXISTS (SELECT id FROM tabl WHERE fk1=TT.fk1 AND fk2=145)
  AND EXISTS (SELECT id FROM tabl WHERE fk1=TT.fk1 AND fk2=2229)

Добавлено через 2 минуты
Только по плану выполнения проверьте, точно ли этот запрос лучше, чем ваш. Потому что более аккуратненький текст еще не гарантирует лучшей работы.

Добавлено через 2 минуты

Сообщение от da50h

чистые join работали не всегда корректно. помогло внедрение all

?? Подробнее можно?

Добавлено через 5 минут
А вообще, я дуб

Есть же Intersect

))

SQL
1
2
3
4
5
6
7
SELECT  fk1 FROM tabl WHERE fk2 = 3
INTERSECT 
SELECT  fk1 FROM tabl WHERE fk2 = 9
INTERSECT 
SELECT  fk1 FROM tabl WHERE fk2 = 145
INTERSECT 
SELECT  fk1 FROM tabl WHERE fk2 = 2229

@invm · 17.11.2016, 11:11

Сообщение от da50h

Прочтите внимательно условие, в выборке должен быть результат where fk2=3 AND fk2=9 но не or

Прочел. Был неправ.
То, что вам нужно называется "реляционное деление". Реализовать можно, например, так:

T-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
declare @t table (id int, fk1 int, fk2 int);
 
insert into @t
values
 (1, 1, 7),
 (2, 1, 3),
 (3, 1, 9),
 (4, 2, 3),
 (5, 2, 5);
 
declare @fk2 table (fk2 int primary key);
insert into @fk2
values
 (3), (9);
 
select
 t.fk1
from
 @t t join
 @fk2 a on a.fk2 = t.fk2
group by
 t.fk1
having
 count(distinct t.fk2) = (select count(*) from @fk2);

@texnik-san · 17.11.2016, 15:35

invm, круть!

@invm · 18.11.2016, 11:14

texnik-san, материал по теме - http://www.sql-tutorial.ru/ru/... ision.html

Новые блоги и статьи Все статьи Все блоги /
Программная установка даты и запрет ее изменения Maks 02.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "СписаниеМатериалов", разработанного в конфигурации КА2. Задача: при создании документов установить период списания автоматически. . .	Вывод данных в справочнике через динамический список Maks 01.04.2026 Реализация из решения ниже выполнена на примере нетипового справочника "Спецтехника" разработанного в конфигурации КА2. Задача: вывести данные из ТЧ нетипового документа. . .	Функция заполнения текстового поля в реквизите формы документа Maks 01.04.2026 Алгоритм из решения ниже реализован на нетиповом документе "ВыдачаОборудованияНаСпецтехнику" разработанного в конфигурации КА2, в дополнении к предыдущему решению. На форме документа создается. . .	К слову об оптимизации kumehtar 01.04.2026 Вспоминаю начало 2000-х, университет, когда я писал на Delphi. Тогда среди программистов на форумах активно обсуждали аккуратную работу с памятью: нужно было следить за переменными, вовремя. . .
Идея фильтра интернета (сервер = слой+фильтр). Hrethgir 31.03.2026 Суть идеи заключается в том, чтобы запустить свой сервер, о чём я если честно мечтал давно и давно приобрёл книгу как это сделать. Но не было причин его запускать. Очумелые учёные напечатали на. . .	Модель здравосоХранения 6. ESG-повестка и устойчивое развитие; углублённый анализ кадрового бренда anaschu 31.03.2026 В прикрепленном документе раздумья о том, как можно поменять модель в будущем	10 пpимет, которые всегда сбываются Maks 31.03.2026 1. Чтобы, наконец, пришла маршрутка, надо закурить. Если сигарета последняя, маршрутка придет еще до второй затяжки даже вопреки расписанию. 2. Нaдоели зима и снег? Не надо переезжать. Достаточно. . .	Перемещение выделенных строк ТЧ из одного документа в другой Maks 31.03.2026 Реализация из решения ниже выполнена на примере нетипового документа "ВыдачаОборудованияНаСпецтехнику" с единственной табличной частью "ОборудованиеИКомплектующие" разработанного в конфигурации КА2. . . .

@da50h 2 / 2 / 3 Регистрация: 30.03.2016 Сообщений: 19
	17.11.2016, 04:19 [ТС]
	Спасибо, конечно, но я сначала создал тему, а потом вспомнил курс по БД в часности оператор ин. Добавлено через 6 часов 39 минут хотел не ворошить уже, но чет задело "OMG". Тк изначально лично думал, что это рабочий вариант, решил прочитать ман. после чего понял что in не есть решением данной задачи. Прочтите внимательно условие, в выборке должен быть результат where fk2=3 AND fk2=9 но не or. То есть просто дальше к запросу добавлять ещё 1 джоин на каждый параметр. Добавлено через 2 часа 26 минут как оказалось чистые join работали не всегда корректно. помогло внедрение all. зы это для тех, кто попадет на эту тему с поисковиков. 0

@texnik-san шапоклякистка 8-го дня 3681 / 2241 / 391 Регистрация: 26.06.2015 Сообщений: 4,647 Записей в блоге: 1
	17.11.2016, 15:35
	invm, круть! 0

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	18.11.2016, 11:14
	texnik-san, материал по теме - http://www.sql-tutorial.ru/ru/... ision.html 1