Hi Juan!

You're using a lot of LEFT [OUTER] JOINs. Are you sure that none of
them can be replaced by [INNER] JOINs? Also, those joins that may not
return any records on the right side, may they return many records or
maximum 1?

Set

--- In firebird-support@yahoogroups.com, Juan Jose wrote:
>
> Hi,
>
> I had this query that I couldn't optimize any more, it took 13.75s.
> Because it returs only 237 records, I think it's going tousands of
> times to the tables D,E,F,G unnecesarily.
> Note table A has 70k~ records and table B has 300k~ records.
> If I make a procedure that does exactly the same, I earn almost 90%
> of performance by not visiting the last tables when it is not
> needed. --See the procedure at the end.
>
> -
> Is there a way to write this SQL query to be almost as fast as the
> stored procedure?
>
> Thanks you very much!
>
> Juan Jose.
>
> * * * * * * * * * * * * * * * *
> * * * * * Q U E R Y * * * * * *
> * * * * * * * * * * * * * * * *
>
> SELECT
> G.ORDER_NO
> , F.LINE
> , B.TB_INSPECT_ID
> , D.LOT_NO
> , E.ITEM_ID
> , E.DESCRIPTION
> , F.DUE_DATE
> , B.INSP_DATE
> , B.QC_PASSED_DATE
> , B.CALC_FEET
> , H.CUSTOMER_NAME
> , F.IS_ACTIVE
> FROM TB_INSPECT_GROUP A
> LEFT JOIN TB_INSPECT B
> ON B.INSPECTION_GROUP_ID = A.TB_INSPECT_GROUP_ID
> LEFT JOIN TB_LOTS D
> ON A.LOT_ID = D.TB_LOTS_ID
> LEFT JOIN TB_ITEMS E
> ON D.ITEM_ID = E.TB_ITEMS_ID
> LEFT JOIN TB_SO_DET F
> ON A.SO_DET_ID = F.TB_SO_DET_ID
> LEFT JOIN TB_SO G
> ON F.ORDER_ID = G.TB_SO_ID
> LEFT JOIN TB_CUSTOMERS H
> ON G.CUSTOMER_ID = H.TB_CUSTOMERS_ID
> WHERE
> A.DEST_LOT_ID IS NULL
> AND A.SO_DET_ID IS NOT NULL
> AND B.WRITE_UP_DET_ID IS NULL
> AND B.STATUS IN ('PASSED','USE AS IS')
> Plan:
> ------------------------------------------------
> PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A INDEX (RDB$FOREIGN132),B
> INDEX (RDB$FOREIGN112)),D INDEX (RDB$PRIMARY53)),E INDEX
> (RDB$PRIMARY38)),F INDEX (RDB$PRIMARY57)),G INDEX (TB_SO_IDX)),H
> INDEX (RDB$PRIMARY52))
> PLAN (TB_INSPECT_LENGTHS INDEX (RDB$FOREIGN80))
>
> Enchanced Info:
> +--------------------------+---------+------+
> | Table Name | Index | NI |
> | | reads |reads |
> +--------------------------+---------+------+
> | RDB$FIELDS| 68 | 0 |
> | RDB$INDEX_SEGMENTS| 6 | 0 |
> | RDB$RELATION_FIELDS| 68 | 0 |
> | RDB$RELATIONS| 12 | 0 |
> | RDB$RELATION_CONSTRAINTS| 6 | 0 |
> | TB_ITEMS| 216,742 | 0 |
> | TB_CUSTOMERS| 69,197 | 0 |
> | TB_SO| 69,197 | 0 |
> | TB_LOTS| 216,742 | 0 |
> | TB_SO_DET| 69,197 | 0 |
> | TB_INSPECT| 210,746 | 0 |
> | TB_INSPECT_GROUP| 47,048 | 0 |
> | TB_INSPECT_LENGTHS| 1,137 | 0 |
> | FIB$FIELDS_INFO| 36 | 0 |
> +--------------------------+---------+------+
>
> * * * * * * * * * * * * * * * * * * * * * * * * * *
> * * * * * S T O R E D P R O C E D U R E * * * * * *
> * * * * * * * * * * * * * * * * * * * * * * * * * *
>
>
> CREATE PROCEDURE SP_GET_INV_SO_QC_WUP
> RETURNS (
> ORDER_NO VARCHAR (8),
> LINE VARCHAR (2),
> TB_INSPECT_ID INTEGER,
> LOT_NO VARCHAR (10),
> ITEM_NO VARCHAR (12),
> DESCRIPTION VARCHAR (100),
> DUE_DATE DATE,
> INSP_DATE TIMESTAMP,
> QC_PASSED_DATE TIMESTAMP,
> CALC_FEET BIGINT,
> CUSTOMER_NAME VARCHAR (30),
> IS_ACTIVE VARCHAR (5))
> AS
> DECLARE VARIABLE INSP_GRP_ID INTEGER;
> BEGIN
> for SELECT A.TB_INSPECT_GROUP_ID, B.tb_inspect_id, B.INSP_DATE,
> B.QC_PASSED_DATE, B.CALC_FEET
> FROM TB_INSPECT_GROUP A LEFT JOIN TB_INSPECT B
> ON B.INSPECTION_GROUP_ID = A.TB_INSPECT_GROUP_ID
> WHERE
> A.DEST_LOT_ID IS NULL
> AND A.SO_DET_ID IS NOT NULL
> AND B.WRITE_UP_DET_ID IS NULL
> AND B.STATUS IN ('PASSED','USE AS IS')
> into :INSP_GRP_ID,:tb_inspect_id,:INSP_DATE,
> :QC_PASSED_DATE, :CALC_FEET
> do begin
> SELECT G.ORDER_NO, F.LINE, D.LOT_NO, E.ITEM_ID,
> E.DESCRIPTION, F.DUE_DATE, H.CUSTOMER_NAME,F.IS_ACTIVE
> FROM TB_INSPECT_GROUP A LEFT JOIN TB_LOTS D
> ON A.LOT_ID = D.TB_LOTS_ID
> LEFT JOIN TB_ITEMS E
> ON D.ITEM_ID = E.TB_ITEMS_ID
> LEFT JOIN TB_SO_DET F
> ON A.SO_DET_ID = F.TB_SO_DET_ID
> LEFT JOIN TB_SO G
> ON F.ORDER_ID = G.TB_SO_ID
> LEFT JOIN TB_CUSTOMERS H
> ON G.CUSTOMER_ID = H.TB_CUSTOMERS_ID
> WHERE
> A.TB_INSPECT_GROUP_ID = :insp_grp_id
> INTO
> :ORDER_NO,:LINE,:LOT_NO,:ITEM_NO,
> :DESCRIPTION,:DUE_DATE,:CUSTOMER_NAME,:IS_ACTIVE;
>
> SUSPEND;
>
> end
>
> END
>
>
> Query Time
> ------------------------------------------------
> Prepare : 0
> Execute : 1,625
> Avg fetch time: 56.03 ms
>
> Memory
> ------------------------------------------------
> Current: 2,508,244
> Max : 3,115,332
> Buffers: 2,048
>
> Operations
> ------------------------------------------------
> Read : 7,633
> Writes : 4
> Fetches: 667,096
>
> Plan:
> ------------------------------------------------
>
> Enchanced Info:
> +--------------------------+---------+-----------+
> | Table Name | Index | Non-Index |
> | | reads | reads |
> +--------------------------+---------+-----------+
> | RDB$PROCEDURE_PARAMETERS| 12 | 0 |
> | TB_ITEMS| 237 | 0 |
> | TB_CUSTOMERS| 237 | 0 |
> | TB_SO| 237 | 0 |
> | TB_LOTS| 237 | 0 |
> | TB_SO_DET| 237 | 0 |
> | TB_INSPECT| 210,746 | 0 |
> | TB_INSPECT_GROUP| 47,28? | 0 |
> | TB_INSPECT_LENGTHS| 1,137 | 0 |
> +--------------------------+---------+-----------+