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


************S T A T S******************
Query Time
------------------------------------------------
Prepare : 32
Execute : 13,578
Avg fetch time: 13,578.00 ms

Memory
------------------------------------------------
Current: 2,617,880
Max : 3,115,332
Buffers: 2,048

Operations
------------------------------------------------
Read : 9,023
Writes : 15
Fetches: 5,194,218

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 |
+--------------------------+---------+------+


*********E N D S T A T S**************



* * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * 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 |
+--------------------------+---------+-----------+