You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Hi,
I've just migrated one project from MS Access to Firebird, translated the SQL from Access Queries to Firebird Views, but some of the new views are running 50-100 times slower.
I isolated the problem, and created a simple sample DB to illustrate it.
The idea is to get a VIEW, that links a table with products (all rows) with quantities, calculated in another VIEWs.
Table Products:
ID Name
1 product1
2 product2
3 product3
4 product4
...
Result looks like this:
Product Quantity
product1 10
product2 20
product3 null - no quantity in the calculations view (left join)
product4 30
...
(These "products" in sample DB are called "SubProducts")
All seems elementary, but it's not.
With the help of IBExpert "Performance Analysis" i observe, that the result view executes the sub-view (that calculates quantities) for every row in the master table. Primary Keys, Foreign Keys are set right (my opinion).
There is no problem with 5 products and one simple sub-view, but with thousands of products wih several attached complex sub-views (for sales, supplies, ... - quantities) the waste of time is terrible (millions unnecessary reads from db).
I'm guessing the problem is somewhere in the auto-generated PLAN of the result view, but that's out of my competence (for now).
P.S.
The same sql queries works fine in MS Access, and PostgreSQL.
My environment: Win, Firebird 2.0 RC3
Here is the source of the entire sample DB:
/* THE PROBLEM SELECT STATEMENT IS IN: VIEW "ALLSubProductsWithQuantities" */
Submitted by: Georgi Lambrev (piro)
Votes: 1
Hi,
I've just migrated one project from MS Access to Firebird, translated the SQL from Access Queries to Firebird Views, but some of the new views are running 50-100 times slower.
I isolated the problem, and created a simple sample DB to illustrate it.
The idea is to get a VIEW, that links a table with products (all rows) with quantities, calculated in another VIEWs.
Table Products:
ID Name
1 product1
2 product2
3 product3
4 product4
...
View Quantities:
ProductID Quantity
1 10
2 20
4 30
Result looks like this:
Product Quantity
product1 10
product2 20
product3 null - no quantity in the calculations view (left join)
product4 30
...
(These "products" in sample DB are called "SubProducts")
All seems elementary, but it's not.
With the help of IBExpert "Performance Analysis" i observe, that the result view executes the sub-view (that calculates quantities) for every row in the master table. Primary Keys, Foreign Keys are set right (my opinion).
There is no problem with 5 products and one simple sub-view, but with thousands of products wih several attached complex sub-views (for sales, supplies, ... - quantities) the waste of time is terrible (millions unnecessary reads from db).
I'm guessing the problem is somewhere in the auto-generated PLAN of the result view, but that's out of my competence (for now).
P.S.
The same sql queries works fine in MS Access, and PostgreSQL.
My environment: Win, Firebird 2.0 RC3
Here is the source of the entire sample DB:
/* THE PROBLEM SELECT STATEMENT IS IN: VIEW "ALLSubProductsWithQuantities" */
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE TABLE PRODUCTS (
ID INTEGER NOT NULL,
NAME VARCHAR(100)
);
CREATE TABLE RECIPES (
PRODUKTID INTEGER,
SUBPRODUKTID INTEGER,
QUANTITY INTEGER
);
CREATE TABLE SALES (
ID INTEGER NOT NULL,
PRODUKTID SMALLINT,
QUANTITY SMALLINT
);
CREATE TABLE "SubPRODUCTS" (
ID INTEGER NOT NULL,
NAME VARCHAR(100)
);
/******************************************************************************/
/*** Views ***/
/******************************************************************************/
/* View: "SumSalesQUANTITYbyPRODUCT" */
CREATE VIEW "SumSalesQUANTITYbyPRODUCT"(
PRODUKTID,
QUANTITY)
AS
select sales.produktid, sum( sales.quantity ) "QUANTITY"
from sales
group by sales.produktid
;
/* View: "SumSalesQUANTITYbySubPRODUCT" */
CREATE VIEW "SumSalesQUANTITYbySubPRODUCT"(
SUBPRODUKTID,
"SalesSubProductQUANTITY")
AS
select recipes.subproduktid, "SumSalesQUANTITYbyPRODUCT"."QUANTITY" * recipes."QUANTITY" AS "SalesSubProductQUANTITY"
from recipes
inner join "SumSalesQUANTITYbyPRODUCT" on (recipes.produktid = "SumSalesQUANTITYbyPRODUCT".produktid)
;
/* View: "ALLSubProductsWithQuantities" */
CREATE VIEW "ALLSubProductsWithQuantities"(
ID,
NAME,
"SalesSubProductQUANTITY")
AS
select "SubPRODUCTS".id, "SubPRODUCTS".name, "SumSalesQUANTITYbySubPRODUCT"."SalesSubProductQUANTITY"
from "SubPRODUCTS"
left join "SumSalesQUANTITYbySubPRODUCT" on ("SumSalesQUANTITYbySubPRODUCT".subproduktid = "SubPRODUCTS".id)
;
INSERT INTO PRODUCTS (ID, NAME) VALUES (1, 'Produkt1');
INSERT INTO PRODUCTS (ID, NAME) VALUES (2, 'Produkt2');
INSERT INTO PRODUCTS (ID, NAME) VALUES (3, 'Produkt3');
INSERT INTO PRODUCTS (ID, NAME) VALUES (4, 'Produkt4');
INSERT INTO PRODUCTS (ID, NAME) VALUES (5, 'Produkt5');
INSERT INTO PRODUCTS (ID, NAME) VALUES (6, 'Produkt6');
COMMIT WORK;
INSERT INTO "SubPRODUCTS" (ID, NAME) VALUES (1, 'SubProdukt1');
INSERT INTO "SubPRODUCTS" (ID, NAME) VALUES (2, 'SubProdukt2');
INSERT INTO "SubPRODUCTS" (ID, NAME) VALUES (3, 'SubProdukt3');
INSERT INTO "SubPRODUCTS" (ID, NAME) VALUES (4, 'SubProdukt4');
INSERT INTO "SubPRODUCTS" (ID, NAME) VALUES (5, 'SubProdukt5');
INSERT INTO "SubPRODUCTS" (ID, NAME) VALUES (6, 'SubProdukt6');
COMMIT WORK;
INSERT INTO RECIPES (PRODUKTID, SUBPRODUKTID, QUANTITY) VALUES (1, 1, 2);
INSERT INTO RECIPES (PRODUKTID, SUBPRODUKTID, QUANTITY) VALUES (1, 2, 2);
INSERT INTO RECIPES (PRODUKTID, SUBPRODUKTID, QUANTITY) VALUES (2, 3, 3);
INSERT INTO RECIPES (PRODUKTID, SUBPRODUKTID, QUANTITY) VALUES (2, 4, 3);
COMMIT WORK;
INSERT INTO SALES (ID, PRODUKTID, QUANTITY) VALUES (1, 1, 2);
INSERT INTO SALES (ID, PRODUKTID, QUANTITY) VALUES (2, 2, 2);
INSERT INTO SALES (ID, PRODUKTID, QUANTITY) VALUES (3, 1, 3);
INSERT INTO SALES (ID, PRODUKTID, QUANTITY) VALUES (4, 2, 3);
COMMIT WORK;
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE PRODUCTS ADD CONSTRAINT PK_PRODUCTS PRIMARY KEY (ID);
ALTER TABLE SALES ADD CONSTRAINT PK_SALES PRIMARY KEY (ID);
ALTER TABLE "SubPRODUCTS" ADD CONSTRAINT PK_SUBPRODUCTS PRIMARY KEY (ID);
/******************************************************************************/
/*** Foreign Keys ***/
/******************************************************************************/
ALTER TABLE RECIPES ADD CONSTRAINT FK_RECIPES_1 FOREIGN KEY (PRODUKTID) REFERENCES PRODUCTS (ID);
ALTER TABLE RECIPES ADD CONSTRAINT FK_RECIPES_2 FOREIGN KEY (SUBPRODUKTID) REFERENCES "SubPRODUCTS" (ID);
ALTER TABLE SALES ADD CONSTRAINT FK_SALES_1 FOREIGN KEY (PRODUKTID) REFERENCES PRODUCTS (ID);
The text was updated successfully, but these errors were encountered: