Hi Svein,

All relations are done in FOREIGN KEYS, I tried some INNER JOINS
where I thought it could help, and some RIGHT JOINS where it could
help too, but It didn't at all.

The 237 records that I need are :
********MINIMUM SQL***********
SELECT B.TB_INSPECT_ID
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')
********END MINIMUM SQL*********
Here table A is visited 69,197 times
and table B is visited 205,865 times
and 237 records are returned in about two seconds.

******MODIFIED VERSION OF MINIMUM SQL************
SELECT B.TB_INSPECT_ID
FROM TB_INSPECT_GROUP A LEFT JOIN TB_INSPECT B
ON (B.INSPECTION_GROUP_ID = A.TB_INSPECT_GROUP_ID) and
(A.DEST_LOT_ID IS NULL AND A.SO_DET_ID IS NOT NULL) <<--
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')
******MODIFIED VERSION OF MINIMUM SQL************
Here table A is visited 67,159 times
and table B is visited 47,048 times
and the same 237 records are returnes in only one second!

I suppose that because the WHERE CLAUSE only applies to the first
two tables, the other five tables (D,E,F,G & H) should only be
visited 237 times. Am I wrong? why?

I think that the problem is that the WHERE CLAUSE is not being
applied BEFORE THE JOIN (Are ther some technical limitations?). To
prove this, I've got an improve by putting part of the CONDITION
(A.DEST_LOT_ID IS NULL AND A.SO_DET_ID IS NOT NULL --Line 16) on the
ON clause of the JOIN. See new version at the end!



**********NEW QUERY VERSION*******
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) AND
(A.DEST_LOT_ID IS NULL AND A.SO_DET_ID IS NOT NULL) <<--Line 16
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')
********END NEW QUERY VERSION*******


I think, that this is a problem of the SQL optimizer for the
development group. Should I post it there?

I remember a document that explains how a Select SQL is parsed and
executed. Could someone provide me the link?

I'm still looking a way to improve this query. The problem is that I
have a lot of this kind of issues and I prefer to know how to make
efficient queries without having to do stored procedure for each
case.

Thanks

Juan Jose.