Issue Details (XML | Word | Printable)

Key: CORE-876
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Georgi Lambrev
Votes: 1
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Problem with LEFT JOIN-ing sub-VIEWs...

Created: 27/Jul/06 07:32 AM   Updated: 12/Sep/06 03:47 AM
Component/s: Engine
Affects Version/s: 2.0 RC3
Fix Version/s: None


 Description  « Hide
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);


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 27/Jul/06 12:26 PM
Yes, this is a known issue in the optimizer.