Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Problem with LEFT JOIN-ing sub-VIEWs... [CORE876] #1269

Open
firebird-automations opened this issue Jul 27, 2006 · 4 comments
Open

Problem with LEFT JOIN-ing sub-VIEWs... [CORE876] #1269

firebird-automations opened this issue Jul 27, 2006 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

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);

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Yes, this is a known issue in the optimizer.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

priority: Critical [ 2 ] => Major [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 11101 ] => Firebird [ 15214 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants