|
Вопрос по оптимизации запроса
#39457243
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Здорова товарищи, вопрос в следующем, сразу говорю не бейте сильно
Есть таблицы две
1. я содержит детали заказа
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.
CREATE GENERATOR GEN_ORDERSDETAILS_ID;
CREATE TABLE ORDERSDETAILS (
ID DINT_NOT_NULL NOT NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
ORDID DINT_NOT_NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
PRODUCTID DINT_NOT_NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
QUANTITY SMALLINT,
UNITPRICE DMONEY /* DMONEY = DECIMAL(15,2) */,
DISCOUNT DINT_NULL /* DINT_NULL = INTEGER */,
TAKCIYA SMALLINT DEFAULT 0,
MOREINFOITEM DVARCHAR /* DVARCHAR = VARCHAR(250) */,
CURRMINFOMOREITEM DMONEY DEFAULT 0 /* DMONEY = DECIMAL(15,2) */,
TPRODUCT SMALLINT DEFAULT 1,
RESTITEM SMALLINT DEFAULT 0
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE ORDERSDETAILS ADD CONSTRAINT PK_ORDERSDETAILS PRIMARY KEY (ID);
/******************************************************************************/
/*** Foreign Keys ***/
/******************************************************************************/
ALTER TABLE ORDERSDETAILS ADD CONSTRAINT FK_ORDERSDETAILS_1 FOREIGN KEY (ORDID) REFERENCES ORDERS (ID) ON UPDATE CASCADE;
ALTER TABLE ORDERSDETAILS ADD CONSTRAINT FK_ORDERSDETAILS_2 FOREIGN KEY (PRODUCTID) REFERENCES PRODUCTS (ID) ON DELETE CASCADE ON UPDATE CASCADE;
/******************************************************************************/
/*** Indices ***/
/******************************************************************************/
CREATE INDEX ORDERSDETAILS_IDX1 ON ORDERSDETAILS (QUANTITY);
CREATE INDEX ORDERSDETAILS_IDX2 ON ORDERSDETAILS (UNITPRICE);
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/* Trigger: ORDERSDETAILS_BI */
CREATE OR ALTER TRIGGER ORDERSDETAILS_BI FOR ORDERSDETAILS
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_ordersdetails_id,1);
end
^
2- я таблица, продукты
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.
CREATE GENERATOR GEN_PRODUCTS_ID;
CREATE TABLE PRODUCTS (
ID DINT_NOT_NULL NOT NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
ID_FAC DINT_NULL DEFAULT 0 /* DINT_NULL = INTEGER */,
ID_CAT DINT_NOT_NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
PRODUCTNAME DVCHAR_AVERAGE /* DVCHAR_AVERAGE = VARCHAR(150) */,
UNITPRICE DMONEY /* DMONEY = DECIMAL(15,2) */,
DESCPROD BLOB SUB_TYPE 1 SEGMENT SIZE 80,
UNITDIMEN VARCHAR(5),
QUANTSG DINT_NULL /* DINT_NULL = INTEGER */,
AKCIYA SMALLINT DEFAULT 2
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE PRODUCTS ADD CONSTRAINT PK_PRODUCTS PRIMARY KEY (ID);
/******************************************************************************/
/*** Foreign Keys ***/
/******************************************************************************/
ALTER TABLE PRODUCTS ADD CONSTRAINT FK_PRODUCTS_1 FOREIGN KEY (ID_CAT) REFERENCES CATEGORIES (ID) ON DELETE CASCADE ON UPDATE CASCADE;
/******************************************************************************/
/*** Indices ***/
/******************************************************************************/
CREATE INDEX PRODUCTS_IDX1 ON PRODUCTS (ID_FAC);
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/*** Triggers for tables ***/
/******************************************************************************/
/* Trigger: PRODUCTS_BD0 */
CREATE OR ALTER TRIGGER PRODUCTS_BD0 FOR PRODUCTS
ACTIVE BEFORE DELETE POSITION 0
AS
declare variable cRec integer;
begin
/* Trigger text */
select count(id) from ordersdetails where productid = old.id into :cRec;
if (:cRec > 0) then exception not_delete_products;
end
^
/* Trigger: PRODUCTS_BI */
CREATE OR ALTER TRIGGER PRODUCTS_BI FOR PRODUCTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_products_id,1);
end
^
SET TERM ; ^
Делаю запрос следующего характера (хочу узнать какое количество и сколько продано каждого товара)
1.
2.
3.
4.
5.
6.
7.
8.
select r.productid,
sum(r.quantity) as qty, sum(proiz) as sm
, r.productname
from (
select od.productid,
od.quantity, (od.quantity*od.unitprice) as proiz, p.productname
from ordersdetails od join products p on od.productid = p.id
) r group by r.productid, r.productname
План запроса следующий
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
PLAN SORT (JOIN (R P NATURAL, R OD INDEX (FK_ORDERSDETAILS_2)))
------ Информация о производительности ------
Время подготовки запроса = 0ms
Время выполнения запроса = 141ms
Среднее время на получение одной записи = 1,02 ms
Current memory = 35 159 776
Max memory = 56 550 940
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 73 696
Вопрос в следующем нормально ли что он выполняется 141ms
В таблице детали заказа, где 36 тыс записей |
|
|
