Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Firebird, Interbase > Несовпадающие записи

Автор: Zipper 27.4.2007, 11:39

Есть таблица A и таблица Б. Структура таблиц одинаковая.
Надо выбрать из таблицы Б записи которых нет в таблице А.
Операций MINUS у нас нет?

Автор: Akella 27.4.2007, 16:45

http://forum.vingrad.ru/forum/topic-146682.html

Автор: chief39 27.4.2007, 18:25

Код


select * 
from A
where A.id not in(
                             select id
                             from B
                           )

Автор: Zipper 28.4.2007, 05:14

Цитата

Код


select * 
from A
where A.id not in(
                             select id
                             from B         )

А при использовании такого запроса на большой базе (2 млн. записей), у меня он не работал. Как только ограничивал выборку до 200-500 тыс. записей все ок! В FireBird (версия 1.5.3) есть какието ограничения на этот счет?

Автор: Akella 28.4.2007, 07:56

думаю, что нет, просто FB "критично" относится к таким, можно сказать тяжёлым запросам, лучше этот запрос вставить в процедуру

Добавлено через 41 секунду
по идее процедура начинает отдавать записи ещё до того, как выполнится запрос полностью, ну или что-то в этом роде

Автор: Zipper 29.4.2007, 07:23

Цитата(Akella @ 28.4.2007, 07:56

)

по идее процедура начинает отдавать записи ещё до того, как выполнится запрос полностью, ну или что-то в этом роде

На полной базе (2 млн.) запрос сразу срабатывает и показывает полное отсутствие таких записей (запрос не зависает), т.е. он вообще ничего не отдает. А на маленькой базе (200-500 тыс.) находит записи.

Автор: Alex 30.4.2007, 09:14

Цитата(chief39 @ 27.4.2007, 19:25)

Код


select * 
from A
where A.id not in(
                             select id
                             from B
                           )

Бедный сервер да он при 2мил записях задымится от такого запроса должен был, но видимо разработчики это учли и поставили блокировку.
А теперь разберемся по порядку:
1. Нам нужно выбрать все уникальные записи из таблицы B, мы вдруг начинаем просматривать таблицу А
2. Оператор in не использует индексированное чтение и заведомо работает крайне медлено

Протестируем запрос на таблицах с 10000 записей (под рукой с большим не оказалось)

Код


select
  B.id
from B
where B.id not in (select A.id from A)

Результат:

Код


План
PLAN (TS NATURAL)
PLAN (TL NATURAL)

Адаптированный план
PLAN (TS NATURAL) PLAN (TL NATURAL)

------ Performance info ------
Prepare time = 16ms
Execute time = 25s 843ms
Avg fetch time = 833,65 ms
Current memory = 1 265 688
Max memory = 1 291 916
Memory buffers = 4 096
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 6 786 157

Совсем немного переписав запрос:

Код


select
  B.id
from B
where not exists (select A.id from A where A.id = B.id)

Получим:

Код


План
PLAN (TS INDEX (PK_TOVAR_SVET))
PLAN (TL NATURAL)

Адаптированный план
PLAN (TS INDEX (PK_TOVAR_SVET)) PLAN (TL NATURAL)

------ Performance info ------
Prepare time = 16ms
Execute time = 31ms
Avg fetch time = 1,00 ms
Current memory = 1 282 020
Max memory = 1 291 916
Memory buffers = 4 096
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 6 431

Добавлено через 2 минуты и 47 секунд
Zipper, скажи пожалуйста, а сколько времени требовалось серверу, что бы отдать тебе результат даже для 300000 записей? Насколько я понимаю минут 6 должен был бедняга трудиться.

Автор: Zipper 2.5.2007, 07:30

Цитата(Alex @ 30.4.2007, 09:14

)

Zipper, скажи пожалуйста, а сколько времени требовалось серверу, что бы отдать тебе результат даже для 300000 записей? Насколько я понимаю минут 6 должен был бедняга трудиться.

Alex, что-то около 50 сек.

Цитата(Alex @ 30.4.2007, 09:14

)

но видимо разработчики это учли и поставили блокировку.

Так все таки блокировка существует?
Попробую твой вариант, сообщу о результатах. За ранее спасибо. Раньше работал с Oracle. Не достаточно еще разобрался с особенностями FireBird. Будем стараться. smile

Автор: Alex 2.5.2007, 10:22

Цитата(Zipper @ 2.5.2007, 08:30

)

Так все таки блокировка существует?

Официально известно одно ограничение массив элементов для оператора in не может превышать 1500 элементов

Цитата(Zipper @ 2.5.2007, 08:30

)

Попробую твой вариант, сообщу о результатах. За ранее спасибо. Раньше работал с Oracle. Не достаточно еще разобрался с особенностями FireBird.

На чем бы вы не работали запрос с оператором in тяжелая операция для сервера, да еще вы заставляете при каждой проверки условия выполнить выборку всей таблицы, а вам на самом деле нужно узнать только один элемент.

Автор: Zipper 3.5.2007, 05:13

Цитата(Alex @ 2.5.2007, 10:22

)

а вам на самом деле нужно узнать только один элемент

Мне надо отобрать все записи id которых не встречается в другой таблице.
Я написал такой запрос

Код


select s.tab_num, s.fam, s.im, s.ot
from "1SOTRONOS" s
where not exists  (
                        select substring(c.fields from 9 for 6)
                        from cards c
                        where c.pfirm = 10 and '00000007' in (c.fields) and 
                        cast (substring(c.fields from 9 for 6) as integer) = s.tab_num
                      )

При выполнении выдается ошибка
Arithmetic overflow or division by zero has ocurred.
arithmetic exception, numeric overflow, or string truncation.
Cannot transliterate character between character sets.

c.fields типа Blob, s.tab_num типа integer
В чем может быть дело?

Периписал запрос следующим образом

Код


select s.tab_num, s.fam, s.im, s.ot
from "1SOTRONOS" s
where not exists
                                            (
                                            select substring(c.fields from 9 for 6)
                                            from cards c
                                            where c.pfirm = 10 and '00000007' in (c.fields) and
                                            substring(c.fields from 9 for 6) like cast(s.tab_num as varchar(6))
                                            )

все заработало.
Наверно где-то в substring(c.fields from 9 for 6) были данные не конвертируемые в integer.

Автор: Zipper 3.5.2007, 11:38

А еще одну проблему поможете решить?
Есть такой update

Код


update cards c
set fields = '00000007'||
            (
            select cast(s.tab_num as varchar(6))
            from "1SOTRONOS" s
            where s.fam = c.name1 and s.im = c.name2 and s.ot = c.name3 )
where c.pfirm=10 and substring(c.fields from 9 for 6) is null

Надо дополнить поле fields таблицы cards, если оно пустое, строчкой '00000007'+табельный_номер, который берется из поля tab_num (типа integer) таблицы "1SOTRONOS" при совпадении фамилии имени отчества. Но дело в том что встречаются полные тезки и соответственно выбираются несколько записей табельного номера, а он уникальный. как мне исклбчить такую ситуацию? Пусть выбирался бы только один из них. Второго можно поправить руками.

Автор: Akella 3.5.2007, 14:20

Цитата(Alex @ 30.4.2007, 09:14

)

2. Оператор in не использует индексированное чтение и заведомо работает крайне медлено

в FB 2.0 и выше уже и in использует индексы

Добавлено @ 14:23

Цитата(Alex @ 2.5.2007, 10:22

)

Официально известно одно ограничение массив элементов для оператора in не может превышать 1500 элементов

да это так, но в этом случае вываливается исключение, а не так что сервер тихо блокирует...

Добавлено @ 14:25
Zipper, это по идее уже должна была быть новая тема про UPDATE

Добавлено через 13 минут и 37 секунд
Alex, если можно использовать exists, то вообще зачем тогда in? Или in это старый оператор, так сказать и его лучше не использовать?

Автор: Alex 3.5.2007, 15:02

Цитата(Zipper @ 3.5.2007, 12:38

)

Код


update cards c
set fields = '00000007'||
            (
            select FIRST 1 cast(s.tab_num as varchar(6))
            from "1SOTRONOS" s
            where s.fam = c.name1 and s.im = c.name2 and s.ot = c.name3 )
where c.pfirm=10 and substring(c.fields from 9 for 6) is null

Цитата(Akella @ 3.5.2007, 15:20

)

в FB 2.0 и выше уже и in использует индексы

На практике это у него не всегда получается

Цитата(Akella @ 3.5.2007, 15:20

)

да это так, но в этом случае вываливается исключение, а не так что сервер тихо блокирует...

лично мне до сих пор слабо верится, что там тихо промолчали...

Цитата(Akella @ 3.5.2007, 15:20

)

Alex, если можно использовать exists, то вообще зачем тогда in? Или in это старый оператор, так сказать и его лучше не использовать?

Задачи разные у людей бывают

Автор: Zipper 4.5.2007, 05:40

Цитата(Alex @ 3.5.2007, 15:02

)

лично мне до сих пор слабо верится, что там тихо промолчали...

Вериться не вериться, а промолчал.
То есть если SQL возвращает более 1500 тысячей записей в операторе IN ( select ...), то сервер такой запрос не выполнит и должен выдать исключение?

Автор: Alex 4.5.2007, 08:14

Цитата(Zipper @ 4.5.2007, 06:40

)

То есть если SQL возвращает более 1500 тысячей записей в операторе IN ( select ...), то сервер такой запрос не выполнит и должен выдать исключение?

Лично я не уверен, что на select в in это условие распространяется

Цитата(Zipper @ 4.5.2007, 06:40

)

Вериться не вериться, а промолчал.

В чем тестировался запрос?

Автор: Akella 4.5.2007, 11:53

Цитата(Zipper @ 4.5.2007, 05:40

)

нет, в этом случае думаю, что всё нормально будет (хотя не уверен), а вот если так:

Код

WHERE id IN (1, 2, 3, .... 1600)

будет ошибка точно

Автор: Akella 7.5.2007, 14:51

но иногда, например, как у меня не получается заменить IN на exist, почему спросите?
вот почему: на форме есть до десяти TCheckBoxList`ов, пользователь отмечает в некоторых флажки
в итоге получается такой запрос

Код


select a.* from vw_apartments a
where
a.id_type in (9, 91)
and
a.id_region in (2776, 2777)
and
a.id_street in (100,111,112,158,166)

всё время переменное число параметров для инструкции IN

не удасться переделать на

Код


select a.* from vw_apartments a
where
(exists (select id from types t where t = 9))
и так далее

или так создавать запрос??? smile

Код


select a.* from vw_apartments a
where
(
(exists (select t.id from types t where t.id = 9))
or
(exists (select t.id from types t where t.id = 51))
)
AND
(
(exists (select s.id from streets s where s.id = 9))
or
(exists (select s.id from streets s where s.id = 51))
or
(exists (select s.id from streets s where s.id = 511))
or
(exists (select s.id from streets s where s.id = 151))
or
(exists (select s.id from streets s where s.id = 11))
or
(exists (select s.id from streets s where s.id = 16))
or
(exists (select s.id from streets s where s.id = 25))
)
AND
(...

Добавлено через 11 минут и 42 секунды
Ну понятно, что пользователь не отметит такой кол-во флажков. Но реально была ситуация, когда нужно было отметить все 2100 флажков и снять отметки у 5-ти флажков и так в двух списках

Автор: Alex 7.5.2007, 15:06

Цитата(Akella @ 7.5.2007, 15:51

)

или так создавать запрос???

бред полный написан... Такой запрос всегда вернет все записи из таблицы vw_apartments

Автор: Akella 7.5.2007, 15:20

переделываю smile

Добавлено через 3 минуты и 46 секунд
к сожалению нельзя использовать что-то вроде
a.id_type exist(10, 20...)

Автор: Alex 7.5.2007, 15:26

Цитата(Akella @ 7.5.2007, 16:20

)

к сожалению нельзя использовать что-то вроде
a.id_type exist(10, 20...)

Очень интересно, чем это от использования in отличается????

Автор: Akella 7.5.2007, 15:27

разница во времени между

Код


select a.* from vw_apartments a
where
(exists (select t.id from types t where (a.id_type = 9) and (a.id_type = t.id)))

Цитата

Prepare time = 16ms
Execute time = 938ms
Avg fetch time = 44,67 ms
Current memory = 846 700
Max memory = 863 300
Memory buffers = 2 048
Reads from disk to cache = 434
Writes from cache to disk = 0
Fetches from cache = 604 433

Код


select a.* from vw_apartments a
where
a.id_type in (9)

Цитата

Prepare time = 15ms
Execute time = 47ms
Avg fetch time = 2,24 ms
Current memory = 864 828
Max memory = 881 268
Memory buffers = 2 048
Reads from disk to cache = 174
Writes from cache to disk = 0
Fetches from cache = 4 938

незначительная
а код на много проще

Добавлено через 5 минут и 38 секунд
странно, что в первом случае больше времени понадобилось... неужели опять запрос неверный?

Автор: Akella 7.5.2007, 15:50

Цитата(Alex @ 7.5.2007, 15:26

)

Очень интересно, чем это от использования in отличается????

синтаксис разный:

Код


where a.id in(10, 20, 125, 588, 1698)

как такое реализовать с пом. EXISTS?

Автор: Akella 7.5.2007, 16:07

как видите в IN индексы используются

Код


select a.* from apart a
where
a.id_type in (9)

Цитата

План
PLAN (PHONES INDEX (FK_PHONES_APART))
PLAN (A INDEX (FK_APART_TYPE))

Автор: Alex 8.5.2007, 07:10

Цитата(Akella @ 7.5.2007, 16:27

)

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

Вообще то, а что в нем верного ты видишь? Единственное, какое применение я вижу этому кошмару, это если при разработке БД с помощью внешних ключей не наложили ограничение, что в поле a.id_type могут храниться данные только из таблицы type и теперь при каждом запросе выборки как сумашедшие это проверяют.
И потом что вообще сравниваешь? Если хочется сравнить скорости, то пиши

Код


select a.* from vw_apartments a
where
a.id_type = 9

Код


select a.* from vw_apartments a
where
a.id_type in (9)

И будет показан одинаковый результат

Ну и даже немного если включить логику и вспомнить sql, то и первый запрос можно ускорит одним простым движением

Код


select a.* from vw_apartments a
where (a.id_type = 9) and 
(exists (select t.id from types t where (a.id_type = t.id)))

PS:
Сереж, заканчивай, а то у меня уже волосы начинают шевелиться от твоих запросов...

Автор: Akella 8.5.2007, 08:05

ну хорошо, как же мне перестроить "логику" построения запроса в "таких условиях" smile

Автор: Deniz 18.5.2007, 10:50

Вроде все прочитал, но не увидел запроса с join, который выполняет заданное условие задачи.

Цитата

Надо выбрать из таблицы Б записи которых нет в таблице А.

Преложу решение с JOIN

Код


select b.*
from b 
  left join a on (a.id = b.id)
where a.id is null