В Firebird есть получаемые из вне данные. В них содержится адрес объекта. Не из справочника, а строками. Есть поле street_name и street_type. В первом соответственно название, во втором - тип: "улица", "бульвар", "набережная". Есть совпадения, например, бульвар Победы и улица Победы. Хочу найти эти совпадения. Во-первых наличие:


Запрос выполняется быстро, объектов порядка 30 тыс.
Теперь хочу посмотреть, на самом деле разные типы или опечатки пользователей и пишу вложенный запрос:


Запрос выполняется бесконечно долго.
Хотя если вместо вложенного запроса после in, я набью вручную тот десяток наименований, которые возвращает вложенный запрос, то основной запрос тоже срабатывает мгновенно. Я считал, что запросы просто выполняются последовательно и время их выполнения просто суммируется. В чем, то я ошибался. Буду благодарен за разъяснение ошибки. И очень хотелось бы работающий пример по получению подобного результата, так как такие задачи встречаются в торговле, складах и т. п. Структуру базы, естественно, менять нельзя.
И еще: если я изменю условие в having так чтобы возвращалась одна запись а in меняю на =, то все отрабатывает быстро. Оставляю это же условие но снова задаю in, то все снова виснет. Запрос выполнял из flamerobin и из Sql Manager Lite - результат одинаковый.
Заранее спасибо.

?

SELECT
*
FROM
 GKN13 T1
 join GKN13 T2 on T1.STREET_NAME = T2.STREET_NAME
                         and T1.STREET_TYPE <> T2.STREET_TYPE

> [ВладОшин] ©   (27.06.13 17:36) [1]
> ?
>
> SELECT
> *
> FROM
>  GKN13 T1
>  join GKN13 T2 on T1.STREET_NAME = T2.STREET_NAME
>                          and T1.STREET_TYPE <> T2.STREET_TYPE

Понимаю, что у Вас нет под рукой соответствующих данных, чтобы попробовать, но при фул джоин мне вернулось 75 тыс записей при исходных 30, а настоящий результат - это меньше двух десятков записей.

ну тогда
SELECT
distinct T1.*
FROM
GKN13 T1
join GKN13 T2 on T1.STREET_NAME = T2.STREET_NAME
                        and T1.STREET_TYPE <> T2.STREET_TYPE

выдаст тоже самое, что

SELECT distinct STREET_NAME, STREET_TYPE
FROM GKN13
where STREET_NAME in
(SELECT STREET_NAME
FROM GKN13
group by STREET_NAME
having Count(distinct STREET_TYPE) > 1
)

на STREET_TYPE еще бы битмап-индекс неплохо бы повесить
на STREET_NAME обычный

Ну как минимум влепить суррогатный ключ на street_type, одно дело два инта сравнить, другое - две строки.

> одно дело два инта сравнить

битмап-индекс так и делает, ставит соответствие из набора бит
с опупительной скоростью потом join выполняется
фактически соединяются битовые маски

ps
правда в  [FireBird] не уверен есть ли такое, но должно быть, популярная же субд

> [ВладОшин] ©   (27.06.13 18:32) [3]

Спасибо! Результат получен за полторы минуты без навешивания индексов на исходную базу. Удовлетворяет полностью в рамках заданного вопроса о получении результата. Полдела сделано. Для меня остались две неясности. Во-первых: что делать, если вдруг понадобится

, например или равно чему-либо, в общем не единице. Меня что-то переклинило, и не могу сообразить как это через внешнее соединение реализовать.
И во-вторых, понять бы ситуацию с вложенным запросом: почему последовательно выполненные запросы занимают около секунды оба, а при объединении во вложенный все виснет. Собственно вопрос то: это я накосячил с запросом или это особенность сервера/настроек/или чего-то там еще от меня слабо зависящего. Во втором случае просто забуду о вложенных запросах с in и перейду к внешнему соединению.

> что делать, если вдруг понадобится
> having Count(distinct STREET_TYPE) > 5

применить группировку на полученное, например( select from select, если думать лень и надо разово сделать. Там уже набор не большой будет, full scan вполне приемлимо отработает)
А если не лень/ не устроит - подумать :) (мне лень :))


>  почему последовательно выполненные запросы занимают около
> секунды оба, а при объединении во вложенный все виснет.

ИМХО
думаю, что для каждой записи выполняется подзапрос.
(вместо того, что бы один раз выполнить)
План надо смотреть

юзай join везде, где можно/можешь.
(А часто оптимизатор сам выбирает именно его, даже если написать вложением. Но может и не понять, будет лупы гонять)

>_guest_   (28.06.13 09:07) [6]
внимательно смотрите "turbouser ©   (28.06.13 09:18) [7]"
не знаю, как отработает оптимизатор, но - логически - в данном случае semi-join существенно лучше equi-join и исключает операцию сортировки при distinct

> Кщд   (28.06.13 10:25) [9]
> >_guest_   (28.06.13 09:07) [6]
> внимательно смотрите "turbouser ©   (28.06.13 09:18) [7]"
> не знаю, как отработает оптимизатор, но - логически - в
> данном случае semi-join существенно лучше equi-join и исключает
> операцию сортировки при distinct

Уже и даже нашел соответствующее место у Борри:
Не используйте SELECT COUNT(*) как способ проверки существования строк, соответствующих некоторому критерию. Такая техника часто обнаруживается в приложениях, которые были переведены в Firebird из основанных на файлах базах данных с блокировкой таблиц, таких как Paradox или MySQL. От этой техники нужно отказаться. Вместо этого используйте функциональный предикат EXISTS(), который был разработан для этих целей и является очень быстрым.

Только пока не смог это под свою задачу применить - экспериментирую.

>_guest_   (28.06.13 10:33) [10]
>Уже и даже нашел соответствующее место у Борри...
цитата не к месту

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

> Кщд   (28.06.13 11:48) [11]
> IN меняете на EXISTS, не забывая дополнить предикат вложенного
> запроса условием из внешнего

Если честно, так и не смог написать запрос с exists, который бы выполнился за приемлемое время. Получился аналог [3] через внутреннее соединение таблицы с собой же, так это аналог и есть только сам запрос чуть иначе выглядит. Не могли бы Вы привести пример с exists по мотивам моего запроса в топике. Для меня проблема заключается в том, что таблица одна, а не две как в примерах. И как решить задачу без group by и count() я придумать не могу. Создание просмотра проблему не решает, а создавать отдельную таблицу для результата вложенного запроса не кажется мне правильным решением, хотя по времени оно может оказаться самым быстрым.
Если можно, все-таки пример с exists и одной таблицей.

План запросов приведи - от этого можно поплясать.

> Cobalt ©   (30.06.13 07:05) [13]
> План запросов приведи - от этого можно поплясать.

Работающий запрос от [ВладОшин]:


План:
PLAN SORT (MERGE (SORT (T1 NATURAL), SORT (T2 NATURAL)))
Адаптированный план:
PLAN SORT (MERGE (SORT (T1 NATURAL), SORT (T2 NATURAL)))

------ QUERY PERFORMANCE ------
Prepare       : 0 мс
Execute       : 0 мс
Avg fetch time: 0 мс

----------- MEMORY ------------
Current       : 8.74 MB
Max           : 28.87 MB
Buffers       : 2048

------ TABLE OPERATIONS -------
Table name    : GKN13
Idx reads     : 0
Non-idx reads : 60926
Updates       : 0
Deletes       : 0
Inserts       : 0

------ QUERY PERFORMANCE ------
Prepare       : 0 мс
Execute       : 0 мс
Avg fetch time: 0 мс

----------- MEMORY ------------
Current       : 8.74 MB
Max           : 28.87 MB
Buffers       : 2048

------ TABLE OPERATIONS -------
Table name    : GKN13
Idx reads     : 0
Non-idx reads : 60926
Updates       : 0
Deletes       : 0
Inserts       : 0

------ QUERY PERFORMANCE ------
Prepare       : 0 мс
Execute       : 0 мс
Avg fetch time: 0 мс

----------- MEMORY ------------
Current       : 8.74 MB
Max           : 28.87 MB
Buffers       : 2048

------ TABLE OPERATIONS -------
Table name    : GKN13
Idx reads     : 0
Non-idx reads : 60926
Updates       : 0
Deletes       : 0
Inserts       : 0

Не работающий, точнее долго работающий с вложенным запросом:


План:
PLAN SORT ((GKN13 NATURAL))
PLAN SORT ((GKN13 NATURAL))
Адаптированный план:
PLAN SORT ((GKN13 NATURAL))
PLAN SORT ((GKN13 NATURAL))
а результатов по прошествии часа пока нет.
Как уже сказал Влад в [8] вложенный запрос выполняется для каждой записи и если бы был способ этого избежать все было бы хорошо.
Мне пока бы понять поможет ли exists в моей задаче (в топике).

>_guest_   (28.06.13 16:23) [12]
>Если можно, все-таки пример с exists и одной таблицей.
http://www.firebirdfaq.org/faq37/
что не ясно?

> Кщд   (01.07.13 10:34) [15]

Я даже не знаю что Вам ответить, что именно мне не ясно. На мой взгляд пример по Вашей ссылке совершенно не показателен. Большинству в голову бы не пришло именно в этом примере использовать in - сделали бы через соединение таблиц. Если бы Вы смогли привести пример по моей задаче, вот тогда для меня многое прояснилось бы. Результат уже получен, это не попытка заткнуть дыру чужими руками. Я действительно пока не понимаю как в случае одной таблицы:

> не забывая дополнить предикат вложенного запроса условием
> из внешнего

Я понимаю, что Вы хотите, что бы я сам дошел до результата, но вот пока не получается.

>_guest_   (01.07.13 10:53) [16]
>На мой взгляд пример по Вашей ссылке совершенно не показателен.
это ссылка из "turbouser ©   (28.06.13 09:18) [7]"

>Большинству в голову бы не пришло именно в этом примере использовать in - сделали бы через соединение таблиц.
Вам не нужно объединять таблицы - Вам нужно отфильтровать данные из одной таблицы по данным из другой

>Результат уже получен, это не попытка заткнуть дыру чужими руками.
Вы можете переписать свой первоначальный запрос так, как указано в ссылке?

>Я действительно пока не понимаю как в случае одной таблицы...
подставив в качестве "второй" таблицы "первую" разве нет?

>Я понимаю, что Вы хотите, что бы я сам дошел до результата, но вот пока не получается.
так обычно случается, если не пытаться

да, и постройте уже индекс по street_name

> да, и постройте уже индекс по street_name

Хорошо.
> так обычно случается, если не пытаться
Вы считаете, что паузы между постами обусловлены ковырянием в носу :)

> Вы можете переписать свой первоначальный запрос так, как
> указано в ссылке?
Если имеется в виду это:
SELECT distinct STREET_NAME, STREET_TYPE
FROM GKN13 t1
where exists
(SELECT 1 FROM GKN13 t2 where t1.STREET_NAME = t2.STREET_NAME
group by STREET_NAME
having Count(distinct STREET_TYPE) > 1)
то да, могу, но этот запрос не может конкурировать с [3] по времени выполнения, несмотря на наличие теперь индекса по street_name. Если же все-таки в виду имеется какой-то более правильный запрос, то кивните, дескать да - надо по другому. Буду пробовать по другому.

> [ВладОшин] ©   (01.07.13 13:53) [19]

Влад, спасибо! Все понял, ну или думаю что все :)
Если я не окончательно надоел, то самый последний вопрос:
почему в запросе [19], [18]  и из топика индекс ускоряет работу,
а для Вашего запроса [3] существенно замедляет (больше чем в 4 раза)?
Ну а для статистики: [3] без индекса - самый быстрый из всех рассмотренных здесь и тех что я не приводил.

> _guest_   (01.07.13 15:52) [20]


> почему в запросе [19], [18]  и из топика индекс ускоряет
> работу,
> а для Вашего запроса [3] существенно замедляет (больше чем
> в 4 раза)?

Надо смотреть план. Есть подозрение, что и на STREET_TYPE индекс надо сделать.

>[ВладОшин] ©   (01.07.13 13:53) [19]
distinct?))

>_guest_   (01.07.13 12:33) [18]
>Вы считаете, что паузы между постами обусловлены ковырянием в носу :)
да

>[3] без индекса - самый быстрый из всех рассмотренных здесь и тех что я не приводил.
смотрите план запроса
ответы на все Ваши вопросы в этой ветке - прописные истины
применить очевидный пример из ссылки - Вы не в состоянии
вывод два:
1. "[ВладОшин] ©" бесконечно добр;
2. сделайте пять шагов назад от баз данных - там стена. повернитесь, приступайте.

> _guest_   (01.07.13 15:52) [20]

>> turbouser ©   (01.07.13 16:00) [21]

> Есть подозрение, что и на STREET_TYPE индекс надо сделать.

очень есть! :)


> Кщд   (01.07.13 16:03) [22]

:)
да просто скопировал


> 1. "[ВладОшин] ©" бесконечно добр;

наоборот, Злой. Автору бы подучить немного. Не до Вашего конечно уровня (я помню, как Вы мне подсказывали, спасибо еще раз)
Просто было так, что sql вообще не знал, а надо было прямо сейчас. Поэтому и говорю ответ. Надо будет потом - подучит, нет -ССЗБ :)

> Кщд   (01.07.13 16:03) [22]
В правильности Ваших выводов нет ни тени сомнения (за что отдельное спасибо Владу). Как нет их и в объеме Ваших знаний, а вот.. ну даже термин подобрать не могу.. в доброжелательности - есть. Не надо считать, что часть тех кто задает такие вопросы только и делают, что сидят в форуме, ковыряют в носу и отнимают у Вас время. Ну не любите Вы непрофессионалов, ну и пройдите мимо. Была бы ветка "начинающим" ->"базы данных" я бы там вопрос задал. Мне всего-то было нужно посмотреть кое-какие данные в базе и с помощью 15-страничного учебника по sql я их посмотрел, пусть и перебив результат одного запроса на вход другого. А вот за дополнительные знания всем спасибо.
ЗЫ. Справедливости ради - очевидный пример из ссылки, который был озвучен в [19] я пробовал, правда опять же с distinct (а что с ним не так - это уже выше моего понимания, тем более что практика показывает, что без него результат то не получаем), но поскольку тогда еще не было индексов (читай - прав на их создание), и он не отработал за 20 минут, то был прерван и вычеркнут из рассмотрения.
ЗЫЫ что такое ССЗБ ?

> ССЗБ :)

Смешно, а главное, почти в точку.