| Subject | Re: [firebird-support] correlated subquery optimize problem under FB 2 |
|---|---|
| Author | Tomáš Horák |
| Post date | 2007-12-07T08:01:15Z |
I am attaching plan for original query. I know it is not wel defined,
but it is because mostly is defined dynamicly based on selection
criteria user will select.
So any advice how sql could be better write is welcome, but I cannot
optimize totally just this one query I am sending becouse of it's
dynamic character.
ReceivedOrders is main table, it contains links to other tables, so
relationship betwen OtherTable and ReceivedOrders is 1 to 0..n.
Only exception is ReceivedOrders2 table(used in subquery), where are
rows for ReceivedOrders table, so there is relationship
ReceivedOrders to ReceivedOrders2 1 to 0..n
Links are defined as foreign keys, so there are appropriate indexes
there. But I don't want to bother you with too much details, problem is
as I wrote why in FB2 is much more fetches from tables from correlated
subqueries ?
FB15.txt - original query on FB 1.5
FB2.txt - same query against FB 2 (2.0.1.12855) - plan starts from Firms
instead of DocQueues, but there are much more fetches from correlated
subqueries - see ReceivedOrders2
FB2-conditions-to-join.txt - here I moved 2 conditions form WHERE
directly to JOIN = much less fetches from ReceivedOrders2
FB2-change-plan-to-main-table.txt - here I added some hint to JOIN Firms
to force not use table Firm as first in execution plan, still much
fetches from ReceivedOrders2
FB2-use-left-join.txt - here I used LEFT for join Firms, plan started as
in previous case from ReceivedOrders and as result much less fetches
from ReceivedOrders2
Because it is slight hard to analyze such query, I tried to reduced it
to find out what could cause such behaviour.
FB-reduced-query.txt - here is some reduced query and small amout of
fetches from correlated table firms.
FB-reduced-query-move-conditions.txt - same query as above, only I moved
two conditions from begin to end of WHERE section, so in fact I changed
nothing important, but as result there
is much more fetches from correlated table firms.
Seems to me that best choice is to use LEFT JOIN to split main table
ReceivedOrders from rest of joins, but before I will deal with it I
wants to find out something more about this behaviour.
tomas
Leyne, Sean wrote:
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
LEFT JOIN Firms F ON F.ID=A.Firm_ID
JOIN Periods P ON P.ID=A.Period_ID
JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
JOIN Currencies C ON C.ID=A.Currency_ID
JOIN Countries CT ON CT.ID=A.Country_ID
JOIN SecurityUsers SU1 ON SU1.ID=A.CreatedBy_ID
LEFT JOIN SecurityUsers SU2 ON SU2.ID=A.CorrectedBy_ID
LEFT JOIN DocConfirmations DC ON DC.DOCUMENT_ID=A.ID AND DC.DocType='RO'
WHERE
(A.DocQueue_ID IN ('3000000101','K400000101') )
AND ((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
AND
(((0 = (SELECT Count(ID) FROM ReceivedOrders2 where Parent_ID=A.ID)) OR
A.ID IN (SELECT Y.Parent_ID FROM ReceivedOrders2 Y
WHERE Y.Parent_ID=A.ID AND (( (
(Y.Division_ID in (select Bx.ID from Divisions Bx where Bx.ID in ('1000000101','1100000101','2000000101')
and Bx.Hidden = 'N')) or (0 = 1 AND (Y.Division_ID in
(select Bx.ID from Divisions Bx where
Bx.ID in (select B2x.ID from SYS$Divisions2
B2x where B2x.Superior_ID in ('1000000101','1100000101','2000000101')) and
Bx.Hidden = 'N' ) ) ) ) )
AND ( ((Y.RowType=3 and Y.Store_ID IN ('1000000101','1100000101','1200000101','1300000101','1400000101',
'1500000101','3000000101','4000000101')) or (Y.RowType<>3)) )))) )
AND (('N' = 'A') OR (('N' = 'N') AND (A.Revided_ID IS NULL)) )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))
PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))
PLAN JOIN (JOIN (JOIN (JOIN (A INDEX (RECEIVEDORDERSDOCQUEUEFK, RECEIVEDORDERSDOCQUEUEFK), F INDEX (FIRMSPK)), SU1 INDEX (SECURITYUSERSPK), CT INDEX (COUNTRIESPK), C INDEX (CURRENCIESPK), DQ INDEX (DOCQUEUESPK), P INDEX (PERIODSPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))PLAN (BX INDEX (DIVISIONSPK))PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))PLAN (BX INDEX (DIVISIONSPK))PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))PLAN JOIN (JOIN (JOIN (JOIN (A INDEX (RECEIVEDORDERSDOCQUEUEFK, RECEIVEDORDERSDOCQUEUEFK), F INDEX (FIRMSPK)), SU1 INDEX (SECURITYUSERSPK), CT INDEX (COUNTRIESPK), C INDEX (CURRENCIESPK), DQ INDEX (DOCQUEUESPK), P INDEX (PERIODSPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Query Time
------------------------------------------------
Prepare : 10,00 ms
Execute : 941,00 ms
Avg fetch time: 47,05 ms
Memory
------------------------------------------------
Current: 1�217�096
Max : 1�312�104
Buffers: 2�048
Operations
------------------------------------------------
Read : 8�321
Writes : 0
Fetches: 349�820
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| COUNTRIES| 0 | 20 | 0 | 0 | 0 | 0 |
| CURRENCIES| 0 | 20 | 0 | 0 | 0 | 0 |
| DIVISIONS| 0 | 2083 | 0 | 0 | 0 | 0 |
| DOCQUEUES| 0 | 20 | 0 | 0 | 0 | 0 |
| FIRMS| 0 | 2103 | 0 | 0 | 0 | 0 |
| PERIODS| 0 | 20 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS2| 0 | 9653 | 0 | 0 | 0 | 0 |
| SECURITYUSERS| 0 | 40 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
----------
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
JOIN Firms F ON F.ID=A.Firm_ID
JOIN Periods P ON P.ID=A.Period_ID
JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
JOIN Currencies C ON C.ID=A.Currency_ID
JOIN Countries CT ON CT.ID=A.Country_ID
JOIN SecurityUsers SU1 ON SU1.ID=A.CreatedBy_ID
LEFT JOIN SecurityUsers SU2 ON SU2.ID=A.CorrectedBy_ID
LEFT JOIN DocConfirmations DC ON DC.DOCUMENT_ID=A.ID AND DC.DocType='RO'
WHERE
(A.DocQueue_ID IN ('3000000101','K400000101') )
AND ((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
AND
(((0 = (SELECT Count(ID) FROM ReceivedOrders2 where Parent_ID=A.ID)) OR
A.ID IN (SELECT Y.Parent_ID FROM ReceivedOrders2 Y
WHERE Y.Parent_ID=A.ID AND (( (
(Y.Division_ID in (select Bx.ID from Divisions Bx where Bx.ID in ('1000000101','1100000101','2000000101')
and Bx.Hidden = 'N')) or (0 = 1 AND (Y.Division_ID in
(select Bx.ID from Divisions Bx where
Bx.ID in (select B2x.ID from SYS$Divisions2
B2x where B2x.Superior_ID in ('1000000101','1100000101','2000000101')) and
Bx.Hidden = 'N' ) ) ) ) )
AND ( ((Y.RowType=3 and Y.Store_ID IN ('1000000101','1100000101','1200000101','1300000101','1400000101',
'1500000101','3000000101','4000000101')) or (Y.RowType<>3)) )))) )
AND (('N' = 'A') OR (('N' = 'N') AND (A.Revided_ID IS NULL)) )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))
PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))
PLAN JOIN (JOIN (JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK), P INDEX (PERIODSPK), SU1 INDEX (SECURITYUSERSPK), DQ INDEX (DOCQUEUESPK), C INDEX (CURRENCIESPK), CT INDEX (COUNTRIESPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))PLAN (BX INDEX (DIVISIONSPK))PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))PLAN (BX INDEX (DIVISIONSPK))PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))PLAN JOIN (JOIN (JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK), P INDEX (PERIODSPK), SU1 INDEX (SECURITYUSERSPK), DQ INDEX (DOCQUEUESPK), C INDEX (CURRENCIESPK), CT INDEX (COUNTRIESPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Query Time
------------------------------------------------
Prepare : 130,00 ms
Execute : 258�842,00 ms
Avg fetch time: 12�942,10 ms
Memory
------------------------------------------------
Current: 1�121�600
Max : 1�123�260
Buffers: 2�048
Operations
------------------------------------------------
Read : 68�061
Writes : 0
Fetches: 4�127�108
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| COUNTRIES| 0 | 20 | 0 | 0 | 0 | 0 |
| CURRENCIES| 0 | 20 | 0 | 0 | 0 | 0 |
| DIVISIONS| 0 | 147193 | 0 | 0 | 0 | 0 |
| DOCQUEUES| 0 | 20 | 0 | 0 | 0 | 0 |
| FIRMS| 0 | 2063 | 137284 | 0 | 0 | 0 |
| PERIODS| 0 | 20 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS2| 0 | 678507 | 0 | 0 | 0 | 0 |
| SECURITYUSERS| 0 | 40 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
----------
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
JOIN Firms F ON F.ID=A.Firm_ID AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
JOIN Periods P ON P.ID=A.Period_ID
JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
JOIN Currencies C ON C.ID=A.Currency_ID
JOIN Countries CT ON CT.ID=A.Country_ID
JOIN SecurityUsers SU1 ON SU1.ID=A.CreatedBy_ID
LEFT JOIN SecurityUsers SU2 ON SU2.ID=A.CorrectedBy_ID
LEFT JOIN DocConfirmations DC ON DC.DOCUMENT_ID=A.ID AND DC.DocType='RO'
WHERE
(A.DocQueue_ID IN ('3000000101','K400000101') )
AND ((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
--AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
AND
(((0 = (SELECT Count(ID) FROM ReceivedOrders2 where Parent_ID=A.ID)) OR
A.ID IN (SELECT Y.Parent_ID FROM ReceivedOrders2 Y
WHERE Y.Parent_ID=A.ID AND (( (
(Y.Division_ID in (select Bx.ID from Divisions Bx where Bx.ID in ('1000000101','1100000101','2000000101')
and Bx.Hidden = 'N')) or (0 = 1 AND (Y.Division_ID in
(select Bx.ID from Divisions Bx where
Bx.ID in (select B2x.ID from SYS$Divisions2
B2x where B2x.Superior_ID in ('1000000101','1100000101','2000000101')) and
Bx.Hidden = 'N' ) ) ) ) )
AND ( ((Y.RowType=3 and Y.Store_ID IN ('1000000101','1100000101','1200000101','1300000101','1400000101',
'1500000101','3000000101','4000000101')) or (Y.RowType<>3)) )))) )
AND (('N' = 'A') OR (('N' = 'N') AND (A.Revided_ID IS NULL)) )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))
PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))
PLAN JOIN (JOIN (JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK), P INDEX (PERIODSPK), SU1 INDEX (SECURITYUSERSPK), DQ INDEX (DOCQUEUESPK), C INDEX (CURRENCIESPK), CT INDEX (COUNTRIESPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))PLAN (BX INDEX (DIVISIONSPK))PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))PLAN (BX INDEX (DIVISIONSPK))PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))PLAN JOIN (JOIN (JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK), P INDEX (PERIODSPK), SU1 INDEX (SECURITYUSERSPK), DQ INDEX (DOCQUEUESPK), C INDEX (CURRENCIESPK), CT INDEX (COUNTRIESPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Query Time
------------------------------------------------
Prepare : 10,00 ms
Execute : 3�054,00 ms
Avg fetch time: 152,70 ms
Memory
------------------------------------------------
Current: 1�121�668
Max : 1�147�192
Buffers: 2�048
Operations
------------------------------------------------
Read : 24�292
Writes : 0
Fetches: 1�046�428
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| COUNTRIES| 0 | 20 | 0 | 0 | 0 | 0 |
| CURRENCIES| 0 | 20 | 0 | 0 | 0 | 0 |
| DIVISIONS| 0 | 2063 | 0 | 0 | 0 | 0 |
| DOCQUEUES| 0 | 20 | 0 | 0 | 0 | 0 |
| FIRMS| 0 | 2063 | 137284 | 0 | 0 | 0 |
| PERIODS| 0 | 20 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS2| 0 | 9419 | 0 | 0 | 0 | 0 |
| SECURITYUSERS| 0 | 40 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
----------
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
JOIN Firms F ON F.ID=A.Firm_ID||''
JOIN Periods P ON P.ID=A.Period_ID
JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
JOIN Currencies C ON C.ID=A.Currency_ID
JOIN Countries CT ON CT.ID=A.Country_ID
JOIN SecurityUsers SU1 ON SU1.ID=A.CreatedBy_ID
LEFT JOIN SecurityUsers SU2 ON SU2.ID=A.CorrectedBy_ID
LEFT JOIN DocConfirmations DC ON DC.DOCUMENT_ID=A.ID AND DC.DocType='RO'
WHERE
(A.DocQueue_ID IN ('3000000101','K400000101') )
AND ((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
AND
(((0 = (SELECT Count(ID) FROM ReceivedOrders2 where Parent_ID=A.ID)) OR
A.ID IN (SELECT Y.Parent_ID FROM ReceivedOrders2 Y
WHERE Y.Parent_ID=A.ID AND (( (
(Y.Division_ID in (select Bx.ID from Divisions Bx where Bx.ID in ('1000000101','1100000101','2000000101')
and Bx.Hidden = 'N')) or (0 = 1 AND (Y.Division_ID in
(select Bx.ID from Divisions Bx where
Bx.ID in (select B2x.ID from SYS$Divisions2
B2x where B2x.Superior_ID in ('1000000101','1100000101','2000000101')) and
Bx.Hidden = 'N' ) ) ) ) )
AND ( ((Y.RowType=3 and Y.Store_ID IN ('1000000101','1100000101','1200000101','1300000101','1400000101',
'1500000101','3000000101','4000000101')) or (Y.RowType<>3)) )))) )
AND (('N' = 'A') OR (('N' = 'N') AND (A.Revided_ID IS NULL)) )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))
PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))
PLAN JOIN (JOIN (JOIN (A INDEX (RECEIVEDORDERSDOCQUEUEFK, RECEIVEDORDERSDOCQUEUEFK), P INDEX (PERIODSPK), SU1 INDEX (SECURITYUSERSPK), DQ INDEX (DOCQUEUESPK), C INDEX (CURRENCIESPK), CT INDEX (COUNTRIESPK), F INDEX (FIRMSPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))PLAN (BX INDEX (DIVISIONSPK))PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))PLAN (BX INDEX (DIVISIONSPK))PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))PLAN JOIN (JOIN (JOIN (A INDEX (RECEIVEDORDERSDOCQUEUEFK, RECEIVEDORDERSDOCQUEUEFK), P INDEX (PERIODSPK), SU1 INDEX (SECURITYUSERSPK), DQ INDEX (DOCQUEUESPK), C INDEX (CURRENCIESPK), CT INDEX (COUNTRIESPK), F INDEX (FIRMSPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Query Time
------------------------------------------------
Prepare : 20,00 ms
Execute : 7�651,00 ms
Avg fetch time: 382,55 ms
Memory
------------------------------------------------
Current: 1�224�368
Max : 1�318�784
Buffers: 2�048
Operations
------------------------------------------------
Read : 21�231
Writes : 0
Fetches: 3�429�692
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| COUNTRIES| 0 | 20 | 0 | 0 | 0 | 0 |
| CURRENCIES| 0 | 20 | 0 | 0 | 0 | 0 |
| DIVISIONS| 0 | 147193 | 0 | 0 | 0 | 0 |
| DOCQUEUES| 0 | 20 | 0 | 0 | 0 | 0 |
| FIRMS| 0 | 2083 | 0 | 0 | 0 | 0 |
| PERIODS| 0 | 20 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS2| 0 | 678507 | 0 | 0 | 0 | 0 |
| SECURITYUSERS| 0 | 40 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
----------
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
JOIN Firms F ON F.ID=A.Firm_ID
JOIN Periods P ON P.ID=A.Period_ID
JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
JOIN Currencies C ON C.ID=A.Currency_ID
JOIN Countries CT ON CT.ID=A.Country_ID
JOIN SecurityUsers SU1 ON SU1.ID=A.CreatedBy_ID
LEFT JOIN SecurityUsers SU2 ON SU2.ID=A.CorrectedBy_ID
LEFT JOIN DocConfirmations DC ON DC.DOCUMENT_ID=A.ID AND DC.DocType='RO'
WHERE
(A.DocQueue_ID IN ('3000000101','K400000101') )
AND ((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
AND
(((0 = (SELECT Count(ID) FROM ReceivedOrders2 where Parent_ID=A.ID)) OR
A.ID IN (SELECT Y.Parent_ID FROM ReceivedOrders2 Y
WHERE Y.Parent_ID=A.ID AND (( (
(Y.Division_ID in (select Bx.ID from Divisions Bx where Bx.ID in ('1000000101','1100000101','2000000101')
and Bx.Hidden = 'N')) or (0 = 1 AND (Y.Division_ID in
(select Bx.ID from Divisions Bx where
Bx.ID in (select B2x.ID from SYS$Divisions2
B2x where B2x.Superior_ID in ('1000000101','1100000101','2000000101')) and
Bx.Hidden = 'N' ) ) ) ) )
AND ( ((Y.RowType=3 and Y.Store_ID IN ('1000000101','1100000101','1200000101','1300000101','1400000101',
'1500000101','3000000101','4000000101')) or (Y.RowType<>3)) )))) )
AND (('N' = 'A') OR (('N' = 'N') AND (A.Revided_ID IS NULL)) )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK,FIRMSPK,FIRMSFIRMFK))
PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))
PLAN (BX INDEX (DIVISIONSPK,DIVISIONSPK,DIVISIONSPK,DIVISIONSPK))
PLAN (B2X INDEX (SYS$DIVISIONS2_ID,SYS$DIVISIONS2_SUPID,SYS$DIVISIONS2_SUPID,SYS$DIVISIONS2_SUPID))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))
PLAN JOIN (JOIN (JOIN (DQ NATURAL,A INDEX (RECEIVEDORDERSDOCQUEUEFK,RECEIVEDORDERSDOCQUEUEFK,RECEIVEDORDERSDOCQUEUEFK),SU1 INDEX (SECURITYUSERSPK),F INDEX (FIRMSPK),P INDEX (PERIODSPK),C INDEX (CURRENCIESPK),CT INDEX (COUNTRIESPK)),SU2 INDEX (SECURITYUSERSPK)),DC INDEX (DOCCONFIRMATIONS_DC))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK,FIRMSPK,FIRMSFIRMFK))PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))PLAN (BX INDEX (DIVISIONSPK,DIVISIONSPK,DIVISIONSPK,DIVISIONSPK))PLAN (B2X INDEX (SYS$DIVISIONS2_ID,SYS$DIVISIONS2_SUPID,SYS$DIVISIONS2_SUPID,SYS$DIVISIONS2_SUPID))PLAN (BX INDEX (DIVISIONSPK))PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))PLAN JOIN (JOIN (JOIN (DQ NATURAL,A INDEX (RECEIVEDORDERSDOCQUEUEFK,RECEIVEDORDERSDOCQUEUEFK,RECEIVEDORDERSDOCQUEUEFK),SU1 INDEX (SECURITYUSERSPK),F INDEX (FIRMSPK),P INDEX (PERIODSPK),C INDEX (CURRENCIESPK),CT INDEX (COUNTRIESPK)),SU2 INDEX (SECURITYUSERSPK)),DC INDEX (DOCCONFIRMATIONS_DC))
Query Time
------------------------------------------------
Prepare : 10,00 ms
Execute : 6�459,00 ms
Avg fetch time: 322,95 ms
Memory
------------------------------------------------
Current: 2�608�604
Max : 2�748�732
Buffers: 2�048
Operations
------------------------------------------------
Read : 12�512
Writes : 0
Fetches: 1�953�255
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| COUNTRIES| 0 | 20 | 0 | 0 | 0 | 0 |
| CURRENCIES| 0 | 20 | 0 | 0 | 0 | 0 |
| DIVISIONS| 0 | 40 | 0 | 0 | 0 | 0 |
| DOCQUEUES| 0 | 0 | 156 | 0 | 0 | 0 |
| FIRMS| 0 | 249 | 0 | 0 | 0 | 0 |
| PERIODS| 0 | 20 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS2| 0 | 468 | 0 | 0 | 0 | 0 |
| SECURITYUSERS| 0 | 40 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
----------
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
JOIN Firms F ON F.ID=A.Firm_ID
WHERE
(A.Confirmed = 'A' ) AND (A.Closed = 'N' )
and
((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
AND
(A.DocQueue_ID IN ('3000000101','K400000101') )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))
PLAN JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))PLAN JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK))
Query Time
------------------------------------------------
Prepare : 10,00 ms
Execute : 2�905,00 ms
Avg fetch time: 145,25 ms
Memory
------------------------------------------------
Current: 1�190�248
Max : 1�318�784
Buffers: 2�048
Operations
------------------------------------------------
Read : 21�961
Writes : 0
Fetches: 1�002�177
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| FIRMS| 0 | 2043 | 137284 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
----------
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
JOIN Firms F ON F.ID=A.Firm_ID
WHERE
((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
AND
(A.DocQueue_ID IN ('3000000101','K400000101') )
and
(A.Confirmed = 'A' ) AND (A.Closed = 'N' )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))
PLAN JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))PLAN JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK))
Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 4�626,00 ms
Avg fetch time: 231,30 ms
Memory
------------------------------------------------
Current: 1�190�248
Max : 1�318�784
Buffers: 2�048
Operations
------------------------------------------------
Read : 22�430
Writes : 0
Fetches: 1�728�092
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| FIRMS| 0 | 147174 | 137284 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
[Non-text portions of this message have been removed]
but it is because mostly is defined dynamicly based on selection
criteria user will select.
So any advice how sql could be better write is welcome, but I cannot
optimize totally just this one query I am sending becouse of it's
dynamic character.
ReceivedOrders is main table, it contains links to other tables, so
relationship betwen OtherTable and ReceivedOrders is 1 to 0..n.
Only exception is ReceivedOrders2 table(used in subquery), where are
rows for ReceivedOrders table, so there is relationship
ReceivedOrders to ReceivedOrders2 1 to 0..n
Links are defined as foreign keys, so there are appropriate indexes
there. But I don't want to bother you with too much details, problem is
as I wrote why in FB2 is much more fetches from tables from correlated
subqueries ?
FB15.txt - original query on FB 1.5
FB2.txt - same query against FB 2 (2.0.1.12855) - plan starts from Firms
instead of DocQueues, but there are much more fetches from correlated
subqueries - see ReceivedOrders2
FB2-conditions-to-join.txt - here I moved 2 conditions form WHERE
directly to JOIN = much less fetches from ReceivedOrders2
FB2-change-plan-to-main-table.txt - here I added some hint to JOIN Firms
to force not use table Firm as first in execution plan, still much
fetches from ReceivedOrders2
FB2-use-left-join.txt - here I used LEFT for join Firms, plan started as
in previous case from ReceivedOrders and as result much less fetches
from ReceivedOrders2
Because it is slight hard to analyze such query, I tried to reduced it
to find out what could cause such behaviour.
FB-reduced-query.txt - here is some reduced query and small amout of
fetches from correlated table firms.
FB-reduced-query-move-conditions.txt - same query as above, only I moved
two conditions from begin to end of WHERE section, so in fact I changed
nothing important, but as result there
is much more fetches from correlated table firms.
Seems to me that best choice is to use LEFT JOIN to split main table
ReceivedOrders from rest of joins, but before I will deal with it I
wants to find out something more about this behaviour.
tomas
Leyne, Sean wrote:
>----------
>
>
> > My query is quite more complex, but for illustratin - conditions
> > (A.Confirmed = 'A' ) AND (A.Closed = 'N' ) reduced 147 000 records
> from
> > Table1 A into something arround 2000 records.
> >
> ========================================================================
> ==
> > =======================================
> > SELECT A.ID FROM
> > Table1 A
> > JOIN Table2 B ON B.ID=A.Firm_ID
> > WHERE
> >
> > A.Firm_ID IN (SELECT ID FROM Table2 WHERE ID='xxxxxxxxxx' OR
> > Firm_ID='xxxxxxxxxx')
>
> In both of your example the above criteria is useless since you already
> have " JOIN Table2 B ON B.ID=A.Firm_ID" which binds the results to only
> those records with A.Firm_ID = B.ID.
>
> Accordingly, your criteria:
>
> A.Firm_ID IN (SELECT ID FROM Table2 WHERE ID='xxxxxxxxxx' OR
> Firm_ID='xxxxxxxxxx')
>
> will never have any effect except to needlessly slow down your
> statement.
>
> As for improving the SQL performance
>
> > AND (A.DocQueue_ID IN ('3000000101','K400000101') )
> > AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
>
> What is the relationship between these fields?
>
> Do you commonly query based on these fields? If so, do you use a field
> or group of fields in these queries?
>
> Do you have any indexes on these fields? If so, what are they?
>
> Please provide the PLAN which the engine has generated for the queries.
>
> I suspect that you need a couple of well defined indexes, which would
> substantially help performance.
>
> Sean
>
>
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
LEFT JOIN Firms F ON F.ID=A.Firm_ID
JOIN Periods P ON P.ID=A.Period_ID
JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
JOIN Currencies C ON C.ID=A.Currency_ID
JOIN Countries CT ON CT.ID=A.Country_ID
JOIN SecurityUsers SU1 ON SU1.ID=A.CreatedBy_ID
LEFT JOIN SecurityUsers SU2 ON SU2.ID=A.CorrectedBy_ID
LEFT JOIN DocConfirmations DC ON DC.DOCUMENT_ID=A.ID AND DC.DocType='RO'
WHERE
(A.DocQueue_ID IN ('3000000101','K400000101') )
AND ((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
AND
(((0 = (SELECT Count(ID) FROM ReceivedOrders2 where Parent_ID=A.ID)) OR
A.ID IN (SELECT Y.Parent_ID FROM ReceivedOrders2 Y
WHERE Y.Parent_ID=A.ID AND (( (
(Y.Division_ID in (select Bx.ID from Divisions Bx where Bx.ID in ('1000000101','1100000101','2000000101')
and Bx.Hidden = 'N')) or (0 = 1 AND (Y.Division_ID in
(select Bx.ID from Divisions Bx where
Bx.ID in (select B2x.ID from SYS$Divisions2
B2x where B2x.Superior_ID in ('1000000101','1100000101','2000000101')) and
Bx.Hidden = 'N' ) ) ) ) )
AND ( ((Y.RowType=3 and Y.Store_ID IN ('1000000101','1100000101','1200000101','1300000101','1400000101',
'1500000101','3000000101','4000000101')) or (Y.RowType<>3)) )))) )
AND (('N' = 'A') OR (('N' = 'N') AND (A.Revided_ID IS NULL)) )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))
PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))
PLAN JOIN (JOIN (JOIN (JOIN (A INDEX (RECEIVEDORDERSDOCQUEUEFK, RECEIVEDORDERSDOCQUEUEFK), F INDEX (FIRMSPK)), SU1 INDEX (SECURITYUSERSPK), CT INDEX (COUNTRIESPK), C INDEX (CURRENCIESPK), DQ INDEX (DOCQUEUESPK), P INDEX (PERIODSPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))PLAN (BX INDEX (DIVISIONSPK))PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))PLAN (BX INDEX (DIVISIONSPK))PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))PLAN JOIN (JOIN (JOIN (JOIN (A INDEX (RECEIVEDORDERSDOCQUEUEFK, RECEIVEDORDERSDOCQUEUEFK), F INDEX (FIRMSPK)), SU1 INDEX (SECURITYUSERSPK), CT INDEX (COUNTRIESPK), C INDEX (CURRENCIESPK), DQ INDEX (DOCQUEUESPK), P INDEX (PERIODSPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Query Time
------------------------------------------------
Prepare : 10,00 ms
Execute : 941,00 ms
Avg fetch time: 47,05 ms
Memory
------------------------------------------------
Current: 1�217�096
Max : 1�312�104
Buffers: 2�048
Operations
------------------------------------------------
Read : 8�321
Writes : 0
Fetches: 349�820
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| COUNTRIES| 0 | 20 | 0 | 0 | 0 | 0 |
| CURRENCIES| 0 | 20 | 0 | 0 | 0 | 0 |
| DIVISIONS| 0 | 2083 | 0 | 0 | 0 | 0 |
| DOCQUEUES| 0 | 20 | 0 | 0 | 0 | 0 |
| FIRMS| 0 | 2103 | 0 | 0 | 0 | 0 |
| PERIODS| 0 | 20 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS2| 0 | 9653 | 0 | 0 | 0 | 0 |
| SECURITYUSERS| 0 | 40 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
----------
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
JOIN Firms F ON F.ID=A.Firm_ID
JOIN Periods P ON P.ID=A.Period_ID
JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
JOIN Currencies C ON C.ID=A.Currency_ID
JOIN Countries CT ON CT.ID=A.Country_ID
JOIN SecurityUsers SU1 ON SU1.ID=A.CreatedBy_ID
LEFT JOIN SecurityUsers SU2 ON SU2.ID=A.CorrectedBy_ID
LEFT JOIN DocConfirmations DC ON DC.DOCUMENT_ID=A.ID AND DC.DocType='RO'
WHERE
(A.DocQueue_ID IN ('3000000101','K400000101') )
AND ((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
AND
(((0 = (SELECT Count(ID) FROM ReceivedOrders2 where Parent_ID=A.ID)) OR
A.ID IN (SELECT Y.Parent_ID FROM ReceivedOrders2 Y
WHERE Y.Parent_ID=A.ID AND (( (
(Y.Division_ID in (select Bx.ID from Divisions Bx where Bx.ID in ('1000000101','1100000101','2000000101')
and Bx.Hidden = 'N')) or (0 = 1 AND (Y.Division_ID in
(select Bx.ID from Divisions Bx where
Bx.ID in (select B2x.ID from SYS$Divisions2
B2x where B2x.Superior_ID in ('1000000101','1100000101','2000000101')) and
Bx.Hidden = 'N' ) ) ) ) )
AND ( ((Y.RowType=3 and Y.Store_ID IN ('1000000101','1100000101','1200000101','1300000101','1400000101',
'1500000101','3000000101','4000000101')) or (Y.RowType<>3)) )))) )
AND (('N' = 'A') OR (('N' = 'N') AND (A.Revided_ID IS NULL)) )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))
PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))
PLAN JOIN (JOIN (JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK), P INDEX (PERIODSPK), SU1 INDEX (SECURITYUSERSPK), DQ INDEX (DOCQUEUESPK), C INDEX (CURRENCIESPK), CT INDEX (COUNTRIESPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))PLAN (BX INDEX (DIVISIONSPK))PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))PLAN (BX INDEX (DIVISIONSPK))PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))PLAN JOIN (JOIN (JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK), P INDEX (PERIODSPK), SU1 INDEX (SECURITYUSERSPK), DQ INDEX (DOCQUEUESPK), C INDEX (CURRENCIESPK), CT INDEX (COUNTRIESPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Query Time
------------------------------------------------
Prepare : 130,00 ms
Execute : 258�842,00 ms
Avg fetch time: 12�942,10 ms
Memory
------------------------------------------------
Current: 1�121�600
Max : 1�123�260
Buffers: 2�048
Operations
------------------------------------------------
Read : 68�061
Writes : 0
Fetches: 4�127�108
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| COUNTRIES| 0 | 20 | 0 | 0 | 0 | 0 |
| CURRENCIES| 0 | 20 | 0 | 0 | 0 | 0 |
| DIVISIONS| 0 | 147193 | 0 | 0 | 0 | 0 |
| DOCQUEUES| 0 | 20 | 0 | 0 | 0 | 0 |
| FIRMS| 0 | 2063 | 137284 | 0 | 0 | 0 |
| PERIODS| 0 | 20 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS2| 0 | 678507 | 0 | 0 | 0 | 0 |
| SECURITYUSERS| 0 | 40 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
----------
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
JOIN Firms F ON F.ID=A.Firm_ID AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
JOIN Periods P ON P.ID=A.Period_ID
JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
JOIN Currencies C ON C.ID=A.Currency_ID
JOIN Countries CT ON CT.ID=A.Country_ID
JOIN SecurityUsers SU1 ON SU1.ID=A.CreatedBy_ID
LEFT JOIN SecurityUsers SU2 ON SU2.ID=A.CorrectedBy_ID
LEFT JOIN DocConfirmations DC ON DC.DOCUMENT_ID=A.ID AND DC.DocType='RO'
WHERE
(A.DocQueue_ID IN ('3000000101','K400000101') )
AND ((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
--AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
AND
(((0 = (SELECT Count(ID) FROM ReceivedOrders2 where Parent_ID=A.ID)) OR
A.ID IN (SELECT Y.Parent_ID FROM ReceivedOrders2 Y
WHERE Y.Parent_ID=A.ID AND (( (
(Y.Division_ID in (select Bx.ID from Divisions Bx where Bx.ID in ('1000000101','1100000101','2000000101')
and Bx.Hidden = 'N')) or (0 = 1 AND (Y.Division_ID in
(select Bx.ID from Divisions Bx where
Bx.ID in (select B2x.ID from SYS$Divisions2
B2x where B2x.Superior_ID in ('1000000101','1100000101','2000000101')) and
Bx.Hidden = 'N' ) ) ) ) )
AND ( ((Y.RowType=3 and Y.Store_ID IN ('1000000101','1100000101','1200000101','1300000101','1400000101',
'1500000101','3000000101','4000000101')) or (Y.RowType<>3)) )))) )
AND (('N' = 'A') OR (('N' = 'N') AND (A.Revided_ID IS NULL)) )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))
PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))
PLAN JOIN (JOIN (JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK), P INDEX (PERIODSPK), SU1 INDEX (SECURITYUSERSPK), DQ INDEX (DOCQUEUESPK), C INDEX (CURRENCIESPK), CT INDEX (COUNTRIESPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))PLAN (BX INDEX (DIVISIONSPK))PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))PLAN (BX INDEX (DIVISIONSPK))PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))PLAN JOIN (JOIN (JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK), P INDEX (PERIODSPK), SU1 INDEX (SECURITYUSERSPK), DQ INDEX (DOCQUEUESPK), C INDEX (CURRENCIESPK), CT INDEX (COUNTRIESPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Query Time
------------------------------------------------
Prepare : 10,00 ms
Execute : 3�054,00 ms
Avg fetch time: 152,70 ms
Memory
------------------------------------------------
Current: 1�121�668
Max : 1�147�192
Buffers: 2�048
Operations
------------------------------------------------
Read : 24�292
Writes : 0
Fetches: 1�046�428
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| COUNTRIES| 0 | 20 | 0 | 0 | 0 | 0 |
| CURRENCIES| 0 | 20 | 0 | 0 | 0 | 0 |
| DIVISIONS| 0 | 2063 | 0 | 0 | 0 | 0 |
| DOCQUEUES| 0 | 20 | 0 | 0 | 0 | 0 |
| FIRMS| 0 | 2063 | 137284 | 0 | 0 | 0 |
| PERIODS| 0 | 20 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS2| 0 | 9419 | 0 | 0 | 0 | 0 |
| SECURITYUSERS| 0 | 40 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
----------
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
JOIN Firms F ON F.ID=A.Firm_ID||''
JOIN Periods P ON P.ID=A.Period_ID
JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
JOIN Currencies C ON C.ID=A.Currency_ID
JOIN Countries CT ON CT.ID=A.Country_ID
JOIN SecurityUsers SU1 ON SU1.ID=A.CreatedBy_ID
LEFT JOIN SecurityUsers SU2 ON SU2.ID=A.CorrectedBy_ID
LEFT JOIN DocConfirmations DC ON DC.DOCUMENT_ID=A.ID AND DC.DocType='RO'
WHERE
(A.DocQueue_ID IN ('3000000101','K400000101') )
AND ((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
AND
(((0 = (SELECT Count(ID) FROM ReceivedOrders2 where Parent_ID=A.ID)) OR
A.ID IN (SELECT Y.Parent_ID FROM ReceivedOrders2 Y
WHERE Y.Parent_ID=A.ID AND (( (
(Y.Division_ID in (select Bx.ID from Divisions Bx where Bx.ID in ('1000000101','1100000101','2000000101')
and Bx.Hidden = 'N')) or (0 = 1 AND (Y.Division_ID in
(select Bx.ID from Divisions Bx where
Bx.ID in (select B2x.ID from SYS$Divisions2
B2x where B2x.Superior_ID in ('1000000101','1100000101','2000000101')) and
Bx.Hidden = 'N' ) ) ) ) )
AND ( ((Y.RowType=3 and Y.Store_ID IN ('1000000101','1100000101','1200000101','1300000101','1400000101',
'1500000101','3000000101','4000000101')) or (Y.RowType<>3)) )))) )
AND (('N' = 'A') OR (('N' = 'N') AND (A.Revided_ID IS NULL)) )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))
PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))
PLAN JOIN (JOIN (JOIN (A INDEX (RECEIVEDORDERSDOCQUEUEFK, RECEIVEDORDERSDOCQUEUEFK), P INDEX (PERIODSPK), SU1 INDEX (SECURITYUSERSPK), DQ INDEX (DOCQUEUESPK), C INDEX (CURRENCIESPK), CT INDEX (COUNTRIESPK), F INDEX (FIRMSPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))PLAN (BX INDEX (DIVISIONSPK))PLAN (B2X INDEX (SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID, SYS$DIVISIONS2_IDSUPID))PLAN (BX INDEX (DIVISIONSPK))PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))PLAN JOIN (JOIN (JOIN (A INDEX (RECEIVEDORDERSDOCQUEUEFK, RECEIVEDORDERSDOCQUEUEFK), P INDEX (PERIODSPK), SU1 INDEX (SECURITYUSERSPK), DQ INDEX (DOCQUEUESPK), C INDEX (CURRENCIESPK), CT INDEX (COUNTRIESPK), F INDEX (FIRMSPK)), SU2 INDEX (SECURITYUSERSPK)), DC INDEX (DOCCONFIRMATIONS_DC))
Query Time
------------------------------------------------
Prepare : 20,00 ms
Execute : 7�651,00 ms
Avg fetch time: 382,55 ms
Memory
------------------------------------------------
Current: 1�224�368
Max : 1�318�784
Buffers: 2�048
Operations
------------------------------------------------
Read : 21�231
Writes : 0
Fetches: 3�429�692
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| COUNTRIES| 0 | 20 | 0 | 0 | 0 | 0 |
| CURRENCIES| 0 | 20 | 0 | 0 | 0 | 0 |
| DIVISIONS| 0 | 147193 | 0 | 0 | 0 | 0 |
| DOCQUEUES| 0 | 20 | 0 | 0 | 0 | 0 |
| FIRMS| 0 | 2083 | 0 | 0 | 0 | 0 |
| PERIODS| 0 | 20 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS2| 0 | 678507 | 0 | 0 | 0 | 0 |
| SECURITYUSERS| 0 | 40 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
----------
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
JOIN Firms F ON F.ID=A.Firm_ID
JOIN Periods P ON P.ID=A.Period_ID
JOIN DocQueues DQ ON DQ.ID=A.DocQueue_ID
JOIN Currencies C ON C.ID=A.Currency_ID
JOIN Countries CT ON CT.ID=A.Country_ID
JOIN SecurityUsers SU1 ON SU1.ID=A.CreatedBy_ID
LEFT JOIN SecurityUsers SU2 ON SU2.ID=A.CorrectedBy_ID
LEFT JOIN DocConfirmations DC ON DC.DOCUMENT_ID=A.ID AND DC.DocType='RO'
WHERE
(A.DocQueue_ID IN ('3000000101','K400000101') )
AND ((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
AND (A.Confirmed = 'A' ) AND (A.Closed = 'N' )
AND
(((0 = (SELECT Count(ID) FROM ReceivedOrders2 where Parent_ID=A.ID)) OR
A.ID IN (SELECT Y.Parent_ID FROM ReceivedOrders2 Y
WHERE Y.Parent_ID=A.ID AND (( (
(Y.Division_ID in (select Bx.ID from Divisions Bx where Bx.ID in ('1000000101','1100000101','2000000101')
and Bx.Hidden = 'N')) or (0 = 1 AND (Y.Division_ID in
(select Bx.ID from Divisions Bx where
Bx.ID in (select B2x.ID from SYS$Divisions2
B2x where B2x.Superior_ID in ('1000000101','1100000101','2000000101')) and
Bx.Hidden = 'N' ) ) ) ) )
AND ( ((Y.RowType=3 and Y.Store_ID IN ('1000000101','1100000101','1200000101','1300000101','1400000101',
'1500000101','3000000101','4000000101')) or (Y.RowType<>3)) )))) )
AND (('N' = 'A') OR (('N' = 'N') AND (A.Revided_ID IS NULL)) )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK,FIRMSPK,FIRMSFIRMFK))
PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))
PLAN (BX INDEX (DIVISIONSPK,DIVISIONSPK,DIVISIONSPK,DIVISIONSPK))
PLAN (B2X INDEX (SYS$DIVISIONS2_ID,SYS$DIVISIONS2_SUPID,SYS$DIVISIONS2_SUPID,SYS$DIVISIONS2_SUPID))
PLAN (BX INDEX (DIVISIONSPK))
PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))
PLAN JOIN (JOIN (JOIN (DQ NATURAL,A INDEX (RECEIVEDORDERSDOCQUEUEFK,RECEIVEDORDERSDOCQUEUEFK,RECEIVEDORDERSDOCQUEUEFK),SU1 INDEX (SECURITYUSERSPK),F INDEX (FIRMSPK),P INDEX (PERIODSPK),C INDEX (CURRENCIESPK),CT INDEX (COUNTRIESPK)),SU2 INDEX (SECURITYUSERSPK)),DC INDEX (DOCCONFIRMATIONS_DC))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK,FIRMSPK,FIRMSFIRMFK))PLAN (RECEIVEDORDERS2 INDEX (RECEIVEDORDERS2PARENTFK))PLAN (BX INDEX (DIVISIONSPK,DIVISIONSPK,DIVISIONSPK,DIVISIONSPK))PLAN (B2X INDEX (SYS$DIVISIONS2_ID,SYS$DIVISIONS2_SUPID,SYS$DIVISIONS2_SUPID,SYS$DIVISIONS2_SUPID))PLAN (BX INDEX (DIVISIONSPK))PLAN (Y INDEX (RECEIVEDORDERS2PARENTFK))PLAN JOIN (JOIN (JOIN (DQ NATURAL,A INDEX (RECEIVEDORDERSDOCQUEUEFK,RECEIVEDORDERSDOCQUEUEFK,RECEIVEDORDERSDOCQUEUEFK),SU1 INDEX (SECURITYUSERSPK),F INDEX (FIRMSPK),P INDEX (PERIODSPK),C INDEX (CURRENCIESPK),CT INDEX (COUNTRIESPK)),SU2 INDEX (SECURITYUSERSPK)),DC INDEX (DOCCONFIRMATIONS_DC))
Query Time
------------------------------------------------
Prepare : 10,00 ms
Execute : 6�459,00 ms
Avg fetch time: 322,95 ms
Memory
------------------------------------------------
Current: 2�608�604
Max : 2�748�732
Buffers: 2�048
Operations
------------------------------------------------
Read : 12�512
Writes : 0
Fetches: 1�953�255
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| COUNTRIES| 0 | 20 | 0 | 0 | 0 | 0 |
| CURRENCIES| 0 | 20 | 0 | 0 | 0 | 0 |
| DIVISIONS| 0 | 40 | 0 | 0 | 0 | 0 |
| DOCQUEUES| 0 | 0 | 156 | 0 | 0 | 0 |
| FIRMS| 0 | 249 | 0 | 0 | 0 | 0 |
| PERIODS| 0 | 20 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
| RECEIVEDORDERS2| 0 | 468 | 0 | 0 | 0 | 0 |
| SECURITYUSERS| 0 | 40 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
----------
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
JOIN Firms F ON F.ID=A.Firm_ID
WHERE
(A.Confirmed = 'A' ) AND (A.Closed = 'N' )
and
((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
AND
(A.DocQueue_ID IN ('3000000101','K400000101') )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))
PLAN JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))PLAN JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK))
Query Time
------------------------------------------------
Prepare : 10,00 ms
Execute : 2�905,00 ms
Avg fetch time: 145,25 ms
Memory
------------------------------------------------
Current: 1�190�248
Max : 1�318�784
Buffers: 2�048
Operations
------------------------------------------------
Read : 21�961
Writes : 0
Fetches: 1�002�177
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| FIRMS| 0 | 2043 | 137284 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
----------
Query
------------------------------------------------
SELECT A.ID FROM
ReceivedOrders A
JOIN Firms F ON F.ID=A.Firm_ID
WHERE
((A.Firm_ID IN (SELECT ID FROM Firms WHERE ID='DTJ3000101' OR Firm_ID='DTJ3000101')) )
AND
(A.DocQueue_ID IN ('3000000101','K400000101') )
and
(A.Confirmed = 'A' ) AND (A.Closed = 'N' )
Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))
PLAN JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK))
Adapted Plan
------------------------------------------------
PLAN (FIRMS INDEX (FIRMSPK))PLAN JOIN (F NATURAL, A INDEX (RECEIVEDORDERSFIRMFK))
Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 4�626,00 ms
Avg fetch time: 231,30 ms
Memory
------------------------------------------------
Current: 1�190�248
Max : 1�318�784
Buffers: 2�048
Operations
------------------------------------------------
Read : 22�430
Writes : 0
Fetches: 1�728�092
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| FIRMS| 0 | 147174 | 137284 | 0 | 0 | 0 |
| RECEIVEDORDERS| 0 | 147174 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
[Non-text portions of this message have been removed]