ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Issue with NOT IN in Firebird 3
25 сообщений из 31, страница 1 из 2
  1  все
Issue with NOT IN in Firebird 3
    #39498029
FredFF
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FredFF
Гость
I try to replace all occurrences of NOT IN into NOT EXISTS because FB 3 admitted to have a problem with optimizer. It's very, very slow when I tested query in IBExpert I have following results:
1. Original code query
select p.avg_cost, bm.item_qty, bm.part_no, bm.item_no
from parts p join bills_material bm on p.part_no=bm.item_no
where p.avg_cost is not null and bm.item_qty is not null
and p.part_no not in (select part_no from bills_material)
and bm.part_no='60-0050-00'
Total time: 1min 35sec
Result:
AVG_COST ITEM_QTY PART_NO ITEM_NO
98.910 1.000 60-0050-00 35-0077-00
121.900 1.000 60-0050-00 39-0039-0M
0.000 1.000 60-0050-00 76-0178-00
2.
select p.avg_cost, bm.item_qty, bm.part_no, bm.item_no
from parts p join bills_material bm on p.part_no=bm.item_no
where p.avg_cost is not null and bm.item_qty is not null
and not exists(select 1 from bills_material bm where p.part_no=bm.part_no)
and bm.part_no='60-0050-00'
Total time: 1min 30sec
Result the same as #1

3.
select p.avg_cost, bm.item_qty, bm.part_no, bm.item_no
from parts p join bills_material bm on p.part_no=bm.item_no
where p.avg_cost is not null and bm.item_qty is not null
and not exists(select 1 from bills_material bm join parts p on bm.part_no=p.part_no and p.part_no is null)
and bm.part_no='60-0050-00'
Total time: 1sec
Result:
AVG_COST ITEM_QTY PART_NO ITEM_NO
98.910 1.000 60-0050-00 35-0077-00
121.900 1.000 60-0050-00 39-0039-0M
191.869 1.000 60-0050-00 84-0456-00
0.000 1.000 60-0050-00 76-0178-00

Can anybody explain how can reach the same result for query #3 as for other two? It's not acceptable to wait for so long to get results! FB 3 is not optimized for NOT IN queries at all!
...
Рейтинг: 0 / 0
31.07.2017, 22:49
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39498033
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
FredFF,

if you use FB 3.0, and ISQL or up to date IBExpert, you can see explain plan and understand why query time are different.
Don't tell me that IBExpert do not show extended plan, please.
...
Рейтинг: 0 / 0
31.07.2017, 22:59
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39498035
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
FredFF,

1 and 2 queries are not equivalent. You forgot the difference between NOT IN and NOT EXISTS.
...
Рейтинг: 0 / 0
31.07.2017, 23:01
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39498037
FredFF
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FredFF
Гость
kdv,

#1
Plan
PLAN (BILLS_MATERIAL NATURAL)
PLAN (BILLS_MATERIAL NATURAL)
PLAN HASH (BM NATURAL, P NATURAL)
#2
Plan
PLAN (BM NATURAL)
PLAN HASH (BM NATURAL, P NATURAL)
#3
Plan
PLAN HASH (BM NATURAL, P NATURAL)
PLAN HASH (BM NATURAL, P NATURAL)

#1 and #2 plans returned identical result. #3 returns one more record (total 4 records)
...
Рейтинг: 0 / 0
31.07.2017, 23:12
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39498045
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
FredFF,

1. NOT IN can not be replaced with NOT EXISTS. The difference in the perception of NULL values. The coincidence of the result is just a special case.

2. There is no index on the part_no column. INNER JOIN can be performed via HASH JOIN. However NOT IN and NOT EXISTS do not execute as OUTER ANTI JOIN, and OUTER JOIN can not yet execute using the HASH JOIN algorithm.
...
Рейтинг: 0 / 0
31.07.2017, 23:33
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39498047
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
FredFFPLAN HASH (BM NATURAL, P NATURAL)
this is simple plan representation. You can find "explain plan" that show more details for Firebird 3.0.

If you are interested in access methods, used by Firebird, use google translate for the article
http://www.ibase.ru/dataaccesspaths/
or, ask us about https://ib-aid.com/en/firebird-training/
...
Рейтинг: 0 / 0
31.07.2017, 23:37
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39498402
Arioch
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arioch
Участник
is "select part_no from bills_material" a unique or primary column ?

can this existence check be reimplemented as LEFT JOIN with non-null check ?
...
Рейтинг: 0 / 0
01.08.2017, 14:11
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39498404
Arioch
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arioch
Участник
kdvuse google translate for the article

god help him!

better use dedicated www.translate.ru than monte-carlo Google/Microsoft translators
...
Рейтинг: 0 / 0
01.08.2017, 14:13
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39498728
FredFF
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FredFF
Гость
Arioch,

I don't need god to translate - I understand Russian language - for programming issues I prefer to use English. I changed table Parts by setting part_no as PK and original query runs as fast as for FB 2.1. Little magic
...
Рейтинг: 0 / 0
02.08.2017, 02:20
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39512429
FredFF
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FredFF
Гость
I have stored procedure developed under FB 2.1 and it's very, very slow (more than 8 minutes) under FB 3 because it has NOT IN condition in WHERE clause:
not SHIPPING.ORDERNO in (select orderno from invoices)
I wrote equivalent NOT EXIST statement with the same result:
NOT EXISTS(SELECT 1 FROM INVOICES where INVOICES.ORDERNO = SHIPPING.ORDERNO)
but it's runs for 6min 45sec to get result!
I tried join tables - it runs for couple seconds
NOT EXISTS(SELECT 1 FROM INVOICES INNER JOIN SHIPPING ON INVOICES.ORDERNO = SHIPPING.ORDERNO) 
but result is wrong

FB 3 is not solved the NOT IN so I try to get fast equivalent. Thanks
...
Рейтинг: 0 / 0
29.08.2017, 19:31
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39512652
Ivan_Pisarevsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan_Pisarevsky
Участник
DDL of all tables, include indexes and full text of select (with plan) need to do your select faster.

ЧИТАТЬ__ВСЕМ,__КТО__ПРИШЕЛ__СЮДА__В__ПЕРВЫЙ__РАЗ!
...
Рейтинг: 0 / 0
30.08.2017, 11:06
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39512740
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
FredFF,

from my POV lot of "not in" and things like "not SHIPPING.ORDERNO in (select orderno from invoices)" indicates bad database design.
...
Рейтинг: 0 / 0
30.08.2017, 12:42
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39512777
Ivan_Pisarevsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan_Pisarevsky
Участник
FredFF NOT EXISTS(SELECT 1 FROM INVOICES INNER JOIN SHIPPING ON INVOICES.ORDERNO = SHIPPING.ORDERNO)  Where is "where" in this select?

Короче, без полного списка объектов нихрена не понятно.
...
Рейтинг: 0 / 0
30.08.2017, 13:22
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39512814
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
Ivan_PisarevskyКороче, без полного списка объектов нихрена не понятно.
из этого короткого куска и так уже понятно, что он ищет неотгруженные через их отсутствие в отгрузках через суб-селект, что само по себе ужасный ужас. Если уж искать такое, то хотя бы так
select ...
from invoices i left join shipping s on s.orderno = i.orderno
where s.orderno is null

А уж если shipping и invoices связаны как 1:1, то тогда, вероятно, можно было бы от shipping вообще избавиться, перенеся столбцы в invoices.
...
Рейтинг: 0 / 0
30.08.2017, 14:11
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39512994
FredFF
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FredFF
Гость
Stored procedure and plan that works fast in FB 2.1 and runs for more than 7 minutes in FB 3
===============================================================
 
Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
SET TERM ^ ;

create or alter procedure monthly_pack_slip (
    datefrom date,
    dateto date)
returns (
    pack_slip_no varchar(10),
    invoiceno integer,
    orderno integer,
    suborderno integer,
    date_ship date,
    custno integer,
    company_name varchar(45),
    purchase_order_no varchar(20),
    part_no varchar(10),
    total_qty_ship integer,
    price float,
    total float)
as
BEGIN
FOR
 SELECT
  SHIPPING.PACK_SLIP_NO,
  INVOICES.INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP,
  INVOICES_PART.PRICE,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP*INVOICES_PART.PRICE AS TOTAL
 FROM ORDERS join SHIPPING on SHIPPING.ORDERNO=ORDERS.ORDERNO   
  join ORDERS_PRODUCT on SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
  join COMPANY on ORDERS.CUSTNO=COMPANY.CUST_NO 
  join INVOICES on SHIPPING.ORDERNO=INVOICES.ORDERNO
  join INVOICES_PART on INVOICES_PART.INVOICENO=INVOICES.INVOICENO 
       and INVOICES_PART.PART_NO=ORDERS_PRODUCT.PART_NO
 WHERE
  SHIPPING.SUBORDERNO IS NULL AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
UNION
 SELECT
  SHIPPING.PACK_SLIP_NO,
  cast (null as INTEGER) INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP,
  ORDERS_PRODUCT.PRICE,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP*ORDERS_PRODUCT.PRICE AS TOTAL
 FROM ORDERS join SHIPPING on SHIPPING.ORDERNO=ORDERS.ORDERNO   
  join ORDERS_PRODUCT on SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
  join COMPANY on ORDERS.CUSTNO=COMPANY.CUST_NO 
 WHERE
  SHIPPING.SUBORDERNO IS NULL AND
  not SHIPPING.ORDERNO in (select orderno from invoices) AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
UNION
 SELECT 
  SHIPPING.PACK_SLIP_NO,
  INVOICES.INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  SUBORDERS_PRODUCT.QTY_SHIP,
  INVOICES_PART.PRICE,
  SUBORDERS_PRODUCT.QTY_SHIP*INVOICES_PART.PRICE AS TOTAL
 FROM ORDERS JOIN SHIPPING ON SHIPPING.ORDERNO=ORDERS.ORDERNO
 JOIN ORDERS_PRODUCT ON SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
 JOIN SUBORDERS_PRODUCT ON SHIPPING.ORDERNO    = SUBORDERS_PRODUCT.ORDERNO AND
                           SHIPPING.SUBORDERNO = SUBORDERS_PRODUCT.SUBORDERNO AND
                        ORDERS_PRODUCT.PART_NO = SUBORDERS_PRODUCT.PART_NO
 JOIN COMPANY ON ORDERS.CUSTNO=COMPANY.CUST_NO 
 JOIN INVOICES ON SHIPPING.ORDERNO    = INVOICES.ORDERNO AND
                  SHIPPING.SUBORDERNO = INVOICES.SUBORDERNO 
 join INVOICES_PART on INVOICES_PART.INVOICENO=INVOICES.INVOICENO 
       and INVOICES_PART.PART_NO=ORDERS_PRODUCT.PART_NO
 WHERE
  SHIPPING.SUBORDERNO IS NOT NULL AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
UNION
 SELECT 
  SHIPPING.PACK_SLIP_NO,
  cast (null as INTEGER) INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  SUBORDERS_PRODUCT.QTY_SHIP,
  ORDERS_PRODUCT.PRICE,
  SUBORDERS_PRODUCT.QTY_SHIP*ORDERS_PRODUCT.PRICE AS TOTAL
 FROM ORDERS JOIN SHIPPING ON SHIPPING.ORDERNO=ORDERS.ORDERNO
 JOIN ORDERS_PRODUCT ON SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
 JOIN SUBORDERS_PRODUCT ON SHIPPING.ORDERNO    = SUBORDERS_PRODUCT.ORDERNO AND
                           SHIPPING.SUBORDERNO = SUBORDERS_PRODUCT.SUBORDERNO AND
                        ORDERS_PRODUCT.PART_NO = SUBORDERS_PRODUCT.PART_NO
 JOIN COMPANY ON ORDERS.CUSTNO=COMPANY.CUST_NO 
 WHERE
  SHIPPING.SUBORDERNO IS NOT NULL AND
  NOT SHIPPING.SUBORDERNO IN 
   (SELECT INVOICES.SUBORDERNO FROM INVOICES WHERE INVOICES.ORDERNO=SHIPPING.ORDERNO) AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
 INTO :PACK_SLIP_NO, :INVOICENO, :ORDERNO, :SUBORDERNO, :DATE_SHIP , :CUSTNO,
      :COMPANY_NAME, :PURCHASE_ORDER_NO, :PART_NO, :TOTAL_QTY_SHIP,
      :PRICE, :TOTAL
DO SUSPEND;
END^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT ON ORDERS TO PROCEDURE MONTHLY_PACK_SLIP;
GRANT SELECT ON SHIPPING TO PROCEDURE MONTHLY_PACK_SLIP;
GRANT SELECT ON ORDERS_PRODUCT TO PROCEDURE MONTHLY_PACK_SLIP;
GRANT SELECT ON COMPANY TO PROCEDURE MONTHLY_PACK_SLIP;
GRANT SELECT ON INVOICES TO PROCEDURE MONTHLY_PACK_SLIP;
GRANT SELECT ON INVOICES_PART TO PROCEDURE MONTHLY_PACK_SLIP;
GRANT SELECT ON SUBORDERS_PRODUCT TO PROCEDURE MONTHLY_PACK_SLIP;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE MONTHLY_PACK_SLIP TO SYSDBA;

=======================================================================
PLAN (INVOICES NATURAL)(INVOICES NATURAL)MERGE (SORT (SHIPPING NATURAL), SORT (JOIN (INVOICES NATURAL, ORDERS INDEX (ORDERS_ORDNO), COMPANY INDEX (PK_COMPANY), ORDERS_PRODUCT INDEX (ORDERS_PRODUCT_ORDPART), INVOICES_PART INDEX (RDB$PRIMARY23))))
PLAN JOIN (SHIPPING NATURAL, ORDERS INDEX (ORDERS_ORDNO), COMPANY INDEX (PK_COMPANY), ORDERS_PRODUCT INDEX (ORDERS_PRODUCT_ORDPART))
PLAN MERGE (SORT (SHIPPING NATURAL), SORT (JOIN (INVOICES NATURAL, ORDERS INDEX (ORDERS_ORDNO), COMPANY INDEX (PK_COMPANY), SUBORDERS_PRODUCT INDEX (SUBORDERS_PRODUCT_OSP), ORDERS_PRODUCT INDEX (ORDERS_PRODUCT_ORDPART), INVOICES_PART INDEX (RDB$PRIMARY23))))
PLAN JOIN (SHIPPING NATURAL, ORDERS INDEX (ORDERS_ORDNO), COMPANY INDEX (PK_COMPANY), SUBORDERS_PRODUCT INDEX (SUBORDERS_PRODUCT_OSP), ORDERS_PRODUCT INDEX (ORDERS_PRODUCT_ORDPART))
=======================================================================

This procedure runs on FB 3 but very slow! In Firebird FAQ stated that the problem with NOT IN remains not solved.
...
Рейтинг: 0 / 0
30.08.2017, 18:59
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39513000
Ivan_Pisarevsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan_Pisarevsky
Участник
UNION а не UNION ALL написано сознательно? В здравом уме и твердой памяти?

price float тип данных выбран сознательно? В здравом уме и твердой памяти?

где DDL таблиц с индексами
CREATE TABLE ...

в код пока не вникал.
...
Рейтинг: 0 / 0
30.08.2017, 19:18
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39513002
Ivan_Pisarevsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan_Pisarevsky
Участник
Код: 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.
25.
26.
27.
28.
 SELECT 
  SHIPPING.PACK_SLIP_NO,
  cast (null as INTEGER) INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  SUBORDERS_PRODUCT.QTY_SHIP,
  ORDERS_PRODUCT.PRICE,
  SUBORDERS_PRODUCT.QTY_SHIP*ORDERS_PRODUCT.PRICE AS TOTAL
 FROM ORDERS 
 JOIN SHIPPING ON SHIPPING.ORDERNO=ORDERS.ORDERNO
 JOIN ORDERS_PRODUCT ON SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
 JOIN SUBORDERS_PRODUCT ON SHIPPING.ORDERNO    = SUBORDERS_PRODUCT.ORDERNO AND
                           SHIPPING.SUBORDERNO = SUBORDERS_PRODUCT.SUBORDERNO AND
                        ORDERS_PRODUCT.PART_NO = SUBORDERS_PRODUCT.PART_NO
 JOIN COMPANY ON ORDERS.CUSTNO=COMPANY.CUST_NO 
  
 WHERE
  SHIPPING.SUBORDERNO IS NOT NULL AND
  NOT exists (select 1 FROM INVOICES 
                   WHERE INVOICES.ORDERNO=SHIPPING.ORDERNO 
                       and  SHIPPING.SUBORDERNO = INVOICES.SUBORDERNO) AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
...
Рейтинг: 0 / 0
30.08.2017, 19:24
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39513003
FredFF
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FredFF
Гость
Ivan_Pisarevsky,

 
Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
CREATE TABLE ORDERS
(
  ORDERNO Integer NOT NULL,
  CUSTNO Integer,
  SHIPTOCOMPANY Varchar(45),
  SHIPTOADDR1 Varchar(30),
  SHIPTOADDR2 Varchar(30),
  SHIPTOADDR3 Varchar(30),
  SHIPTOSTATE Varchar(20),
  SHIPTOZIP Varchar(10),
  SHIPTOCOUNTRY Varchar(20),
  SHIPTOCONTACT Varchar(35),
  ORDER_DATE Timestamp,
  PURCHASE_ORDER_NO Varchar(20),
  TERMS Varchar(15),
  DUE_DATE Timestamp,
  SHIP_DATE Timestamp,
  SHIP_VIA Varchar(7),
  SHIP_METHOD Varchar(15),
  PACK_SLIP_NO Varchar(10),
  SHIPTOPHONE Varchar(15),
  STATUS Varchar(8),
  STATUS_DATE Timestamp,
  SALESPERSON Varchar(6),
  MARK_FOR Varchar(35),
  SALETAX Varchar(1),
  SHIPTOADDRTYPE Varchar(10),
  REMARKS Blob sub_type 1,
  SOME_NAME Varchar(15),
  SHIPPED_DATE Timestamp,
  USE_MAIN4BILL Char(1),
  REP_CREDIT Char(1),
  INDUSTRY Char(1),
  SR_NO Integer,
  SHIPTOCITY Varchar(25),
  COMMENTS Blob sub_type 1,
  BILLTOCOMPANY Varchar(45),
  BILLTOADDR1 Varchar(30),
  BILLTOADDR2 Varchar(30),
  BILLTOADDR3 Varchar(30),
  BILLTOSTATE Varchar(20),
  BILLTOZIP Varchar(10),
  BILLTOCOUNTRY Varchar(20),
  BILLTOCITY Varchar(25),
  BILLTOADDRTYPE Varchar(10),
  CONSTRAINT PK_ORDERS PRIMARY KEY (ORDERNO)
);

CREATE UNIQUE INDEX ORDERS_OC ON ORDERS (ORDERNO,CUSTNO);
CREATE UNIQUE INDEX ORDERS_ORDNO ON ORDERS (ORDERNO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON ORDERS TO  "PUBLIC";

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON ORDERS TO  SYSDBA WITH GRANT OPTION;

CREATE TABLE SHIPPING
(
  PACK_SLIP_NO Varchar(10) NOT NULL,
  ORDERNO Integer,
  SUBORDERNO Integer,
  DATE_SHIP Timestamp,
  FREIGHT Float,
  SHIPHANDLE Float,
  PRIMARY KEY (PACK_SLIP_NO)
);
CREATE UNIQUE INDEX SHIPPING_PSN ON SHIPPING (PACK_SLIP_NO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON SHIPPING TO  "PUBLIC";

CREATE TABLE ORDERS_PRODUCT
(
  ORDER_NO Integer NOT NULL,
  PART_NO Varchar(10) NOT NULL,
  YOUR_PART_NO Varchar(20),
  TOTAL_QTY Integer,
  TOTAL_QTY_SHIP Integer,
  PRICE Float,
  OPCHANGE Varchar(1),
  PRIMARY KEY (ORDER_NO,PART_NO)
);

CREATE UNIQUE INDEX ORDERS_PRODUCT_ORDPART ON ORDERS_PRODUCT (ORDER_NO,PART_NO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON ORDERS_PRODUCT TO  "PUBLIC";

CREATE TABLE COMPANY
(
  CUST_NO Integer,
  COMPANY_NAME Varchar(45),
  CONTACT_FIRST Varchar(30),
  CONTACT_SECOND Varchar(30),
  PHONE_NO Varchar(20),
  STREET_1 Varchar(30),
  STREET_2 Varchar(30),
  CITY Varchar(25),
  STATE_PROVINCE Varchar(20),
  COUNTRY Varchar(15),
  POSTAL_CODE Varchar(12),
  FAX_NO Varchar(20),
  STREET_3 Varchar(30),
  E_MAIL Varchar(45),
  CUST_TYPE Varchar(10),
  BUS_TYPE Varchar(25),
  LAST_ORDER_DAY Timestamp,
  THIS_YEAR_SALES Double precision,
  LAST_YEAR_SALES Double precision,
  YEAR_BEFORE_LAST_SALES Double precision,
  REMARKS Varchar(35),
  LABEL Varchar(3),
  PERCENT_DISCOUNT Float,
  CREDIT_APPROVED Varchar(3),
  REP Varchar(30),
  CREDIT_PROBLEMS Varchar(3),
  DAY_START Timestamp DEFAULT "TODAY",
  CATEGORY Integer,
  BILL_COMPANY_NAME Varchar(45),
  BILL_STREET_1 Varchar(30),
  BILL_STREET_2 Varchar(30),
  BILL_STREET_3 Varchar(30),
  BILL_CITY Varchar(25),
  BILL_STATE_PROVINCE Varchar(20),
  BILL_POSTAL_CODE Varchar(12),
  BILL_COUNTRY Varchar(15),
  BILL_PHONE_NO Varchar(20),
  BILL_FAX_NO Varchar(20),
  BILL_CONTACT_FIRST Varchar(30),
  BILL_CONTACT_SECOND Varchar(30),
  CUST_ACTIVE Char(1),
  USE_MAIN4BILL Char(1),
  REP_CREDIT Char(1),
  INDUSTRY Char(1),
  SHIP_TYPE Char(1),
  SHIP_NUMBER Varchar(25),
  SR_NO Integer,
  CREDIT_LINE Integer,
  CREDIT_APPROVED_DAY Timestamp,
  CONSTRAINT PK_COMPANY PRIMARY KEY (CUST_NO)
);

ALTER TABLE COMPANY ADD CONSTRAINT COMPANY_CATEGORY
  FOREIGN KEY (CATEGORY) REFERENCES CUST_CATEGORY (CC_NO) ON UPDATE CASCADE ON DELETE SET NULL;
CREATE INDEX COMPANY_NM ON COMPANY (COMPANY_NAME);
CREATE UNIQUE INDEX COMPANY_NO ON COMPANY (CUST_NO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON COMPANY TO  "PUBLIC";

CREATE TABLE INVOICES
(
  INVOICENO Integer NOT NULL,
  DATE_INV Timestamp,
  ORDERNO Integer,
  SUBORDERNO Integer,
  PRICE_TYPE Varchar(15),
  SALE_AMOUNT Float,
  FREIGHT Float,
  SALES_TAX Float,
  TOTAL Float,
  TRANSFERRED Varchar(1),
  PRIMARY KEY (INVOICENO)
);

CREATE UNIQUE INDEX INVOICES_INVO ON INVOICES (INVOICENO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON INVOICES TO  "PUBLIC";

CREATE TABLE INVOICES_PART
(
  INVOICENO Integer NOT NULL,
  PART_NO Varchar(10) NOT NULL,
  PRICE Float,
  AVG_COST Float,
  INCL_LABOR Float,
  CONSTRAINT INVOICES_PART_PRIMARYKEY1 PRIMARY KEY (INVOICENO,PART_NO)
);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON INVOICES_PART TO  "PUBLIC";
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON INVOICES_PART TO  SYSDBA WITH GRANT OPTION;

CREATE TABLE SUBORDERS_PRODUCT
(
  ORDERNO Integer NOT NULL,
  SUBORDERNO Integer NOT NULL,
  PART_NO Varchar(10) NOT NULL,
  QTY Integer,
  DUE_DATE Timestamp,
  QTY_SHIP Integer,
  SHIP_DATE Timestamp,
  SHIP_VIA Varchar(15),
  SHIP_METHOD Varchar(15),
  PACK_SLIP_NO Varchar(10),
  SHIPPED_DATE Timestamp,
  PRIMARY KEY (ORDERNO,SUBORDERNO,PART_NO)
);
CREATE UNIQUE INDEX SUBORDERS_PRODUCT_OSP ON SUBORDERS_PRODUCT (ORDERNO,SUBORDERNO,PART_NO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON SUBORDERS_PRODUCT TO  "PUBLIC";



I extracted all tables DDL. Each table has PK. How can I replace NOT IN in WHERE clause with LEFT JOIN clause?
...
Рейтинг: 0 / 0
30.08.2017, 19:39
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39513006
FredFF
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FredFF
Гость
Ivan_Pisarevsky,

 
Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
SET TERM ^ ;
ALTER PROCEDURE MONTHLY_PACK_SLIP (
    DATEFROM Timestamp,
    DATETO Timestamp )
RETURNS (
    PACK_SLIP_NO Varchar(10),
    INVOICENO Integer,
    ORDERNO Integer,
    SUBORDERNO Integer,
    DATE_SHIP Timestamp,
    CUSTNO Integer,
    COMPANY_NAME Varchar(45),
    PURCHASE_ORDER_NO Varchar(20),
    PART_NO Varchar(10),
    TOTAL_QTY_SHIP Integer,
    PRICE Float,
    TOTAL Float )
AS
BEGIN
FOR
 SELECT
  SHIPPING.PACK_SLIP_NO,
  INVOICES.INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP,
  INVOICES_PART.PRICE,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP*INVOICES_PART.PRICE AS TOTAL
 FROM ORDERS join SHIPPING on SHIPPING.ORDERNO=ORDERS.ORDERNO   
  join ORDERS_PRODUCT on SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
  join COMPANY on ORDERS.CUSTNO=COMPANY.CUST_NO 
  join INVOICES on SHIPPING.ORDERNO=INVOICES.ORDERNO
  join INVOICES_PART on INVOICES_PART.INVOICENO=INVOICES.INVOICENO 
       and INVOICES_PART.PART_NO=ORDERS_PRODUCT.PART_NO
 WHERE
  SHIPPING.SUBORDERNO IS NULL AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
UNION
 SELECT
  SHIPPING.PACK_SLIP_NO,
  cast (null as INTEGER) INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP,
  ORDERS_PRODUCT.PRICE,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP*ORDERS_PRODUCT.PRICE AS TOTAL
 FROM ORDERS join SHIPPING on SHIPPING.ORDERNO=ORDERS.ORDERNO   
  join ORDERS_PRODUCT on SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
  join COMPANY on ORDERS.CUSTNO=COMPANY.CUST_NO 
 WHERE
  SHIPPING.SUBORDERNO IS NULL AND
  NOT EXISTS(SELECT 1 FROM INVOICES where INVOICES.ORDERNO = SHIPPING.ORDERNO) AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
UNION
 SELECT 
  SHIPPING.PACK_SLIP_NO,
  INVOICES.INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  SUBORDERS_PRODUCT.QTY_SHIP,
  INVOICES_PART.PRICE,
  SUBORDERS_PRODUCT.QTY_SHIP*INVOICES_PART.PRICE AS TOTAL
 FROM ORDERS JOIN SHIPPING ON SHIPPING.ORDERNO=ORDERS.ORDERNO
 JOIN ORDERS_PRODUCT ON SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
 JOIN SUBORDERS_PRODUCT ON SHIPPING.ORDERNO    = SUBORDERS_PRODUCT.ORDERNO AND
                           SHIPPING.SUBORDERNO = SUBORDERS_PRODUCT.SUBORDERNO AND
                        ORDERS_PRODUCT.PART_NO = SUBORDERS_PRODUCT.PART_NO
 JOIN COMPANY ON ORDERS.CUSTNO=COMPANY.CUST_NO 
 JOIN INVOICES ON SHIPPING.ORDERNO    = INVOICES.ORDERNO AND
                  SHIPPING.SUBORDERNO = INVOICES.SUBORDERNO 
 join INVOICES_PART on INVOICES_PART.INVOICENO=INVOICES.INVOICENO 
       and INVOICES_PART.PART_NO=ORDERS_PRODUCT.PART_NO
 WHERE
  SHIPPING.SUBORDERNO IS NOT NULL AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
UNION
 SELECT 
  SHIPPING.PACK_SLIP_NO,
  cast (null as INTEGER) INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  SUBORDERS_PRODUCT.QTY_SHIP,
  ORDERS_PRODUCT.PRICE,
  SUBORDERS_PRODUCT.QTY_SHIP*ORDERS_PRODUCT.PRICE AS TOTAL
 FROM ORDERS JOIN SHIPPING ON SHIPPING.ORDERNO=ORDERS.ORDERNO
 JOIN ORDERS_PRODUCT ON SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
 JOIN SUBORDERS_PRODUCT ON SHIPPING.ORDERNO    = SUBORDERS_PRODUCT.ORDERNO AND
                           SHIPPING.SUBORDERNO = SUBORDERS_PRODUCT.SUBORDERNO AND
                        ORDERS_PRODUCT.PART_NO = SUBORDERS_PRODUCT.PART_NO
 JOIN COMPANY ON ORDERS.CUSTNO=COMPANY.CUST_NO 
 WHERE
  SHIPPING.SUBORDERNO IS NOT NULL AND
  NOT EXISTS(SELECT 1 FROM INVOICES WHERE INVOICES.ORDERNO=SHIPPING.ORDERNO AND INVOICES.SUBORDERNO = SHIPPING.SUBORDERNO) AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
 INTO :PACK_SLIP_NO, :INVOICENO, :ORDERNO, :SUBORDERNO, :DATE_SHIP , :CUSTNO,
      :COMPANY_NAME, :PURCHASE_ORDER_NO, :PART_NO, :TOTAL_QTY_SHIP,
      :PRICE, :TOTAL
DO SUSPEND;
END^
SET TERM ; ^


GRANT EXECUTE
 ON PROCEDURE MONTHLY_PACK_SLIP TO  SYSDBA;



I already tried this - it's running for 7 minutes! May be LEFT JOIN runs faster but I don't know how to rewrite sql to use LEFT JOIN like:

Код: sql
1.
SELECT 1 FROM INVOICES LEFT JOIN SHIPPING ON INVOICES.ORDERNO = SHIPPING.ORDERNO WHERE SHIPPING.ORDERNO IS NULL
...
Рейтинг: 0 / 0
30.08.2017, 19:48
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39513007
Гаджимурадов Рустам
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гаджимурадов Рустам
Участник
Use tag "spoiler", Luke! (c)

P.S. Show PLANs for your queries, it can help.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
30.08.2017, 19:51
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39513016
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
FredFF,

if you do not have duplicates in different parts of UNION, use UNION ALL instead, it will be much faster.
...
Рейтинг: 0 / 0
30.08.2017, 20:41
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39513020
Гаджимурадов Рустам
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гаджимурадов Рустам
Участник
Я в его проблему не вникал, но неужто вообще без UNIONa нельзя обойтись?
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
30.08.2017, 20:45
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39513058
Ivan_Pisarevsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan_Pisarevsky
Участник
попрятал портянки под спойлер.

жду ответа про юнионы. Если расколоть процедуру на несколько, соответственно юнионам, проблема четко локализуется на экзисте? можно применить и лефт джойн, только отсюда (не видя данных) непонятно связь там один ко многим или как? просто лефт джойн может привести к задвоению строк на выходе.

P.S. за флоат в цене мне бы бухгалтерия мозг не то чтоб вынесла, она бы его выклевала.
ну это так, лирика.
...
Рейтинг: 0 / 0
30.08.2017, 22:39
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39513065
Фотография Старый плюшевый мишка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Старый плюшевый мишка
Участник
Ivan_PisarevskyP.S. за флоат в цене мне бы бухгалтерия мозг не то чтоб вынесла, она бы его выклевала.
ну это так, лирика.

Это не лирика. Это железный закон проектировщика - то, что мы считаем, должно быть целого типа, то, что мереям - плавающего. А за float да, давно уже канделябром.
...
Рейтинг: 0 / 0
30.08.2017, 23:11
| Ответить | Цитировать | Написать  
Issue with NOT IN in Firebird 3
    #39513073
Гаджимурадов Рустам
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гаджимурадов Рустам
Участник
СПМ> железный закон проектировщика - то, что мы считаем,
СПМ> должно быть целого типа, то, что мереям - плавающего.

Замечательно сформулировано. Надо выбить в камне.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
30.08.2017, 23:38
| Ответить | Цитировать | Написать  
25 сообщений из 31, страница 1 из 2
  1  все
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Issue with NOT IN in Firebird 3
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&msg=39498037&tid=1561448]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 50ms
get topic data:
 6ms
get forum data:
 2ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 306ms
total:  445ms
созд. / загр.: 445ms
x
x
Закрыть


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