--SNIP---

> 1. Move the search filter out of the join clause and into a WHERE

clause

> where it belongs.
>

I did this, thank you for the info.

> 2. Make sure your index on DATE_RECEIVED is a DESC index (you can

have

> both ASC and DESC indexes on the same column. Firebird does *not*

invert

> ASC indexes for use in descending sorts, and vice versa.

I did this, again thanks for the info.

>
> 3. My educated guess about the speed is that you have indexes of

low

> selectivity on the Lookup_Codes foreign keys. This wrecks the

geometry of

> the indexes and makes them incrementally slower to use than natural

order

> and MERGE joining. To check this, run gstat on that table's

indexes (on

> the server) and pipe the output to a file so you can paste the

relevant

> stats here if need be. Assuming you're on Windows:
>
> gstat -index "c:\path_to_your\database.fdb" -t HUD_TRACS_FILES
> -user SYSDBA -password icuryy4me
> > "c:\path_to_somewhere\gstat.index.txt"
> ----------------------------------------------------------------
>

Thanks for the command line statement. I pasted the stats right
below.

I do think the indexes are the problem.


--------PASTE---------------------

Database "c:\development\ca\nc data\ca_ipms_nc1.gdb"

Database header page information:
Flags 0
Checksum 12345
Generation 2664985
Page size 4096
ODS version 10.0
Oldest transaction 2659129
Oldest active 2664973
Oldest snapshot 2664973
Next transaction 2664978
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Nov 21, 2003 11:04:00
Attributes force write

Variable header data:
*END*


Database file sequence:
File c:\development\ca\nc data\ca_ipms_nc1.gdb is the only file

Database log page information:
Creation date
Log flags: 2
No write ahead log

Next log page: 0

Variable log data:
Control Point 1:
File name:
Partition offset: 0 Seqno: 0 Offset: 0
Control Point 2:
File name:
Partition offset: 0 Seqno: 0 Offset: 0
Current File:
File name:
Partition offset: 0 Seqno: 0 Offset: 0
*END*

Analyzing database pages ...

HUD_TRACS_FILES (175)

Index IDX_CONTRACT_SYSID_HTF (2)
Depth: 2, leaf buckets: 108, nodes: 69430
Average data length: 0.00, total dup: 68880, max dup: 809
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 6
60 - 79% = 0
80 - 99% = 101

Index IDX_DATE_RECEIVED_HTF (5)
Depth: 2, leaf buckets: 266, nodes: 69430
Average data length: 1.00, total dup: 27949, max dup: 5
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 265
60 - 79% = 1
80 - 99% = 0

Index IDX_HTF_DATE_RECEIVED (1)
Depth: 2, leaf buckets: 132, nodes: 69430
Average data length: 1.00, total dup: 27949, max dup: 5
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 131

Index IDX_HTF_DATE_RECEIVED_DESC (4)
Depth: 2, leaf buckets: 132, nodes: 69430
Average data length: 1.00, total dup: 27949, max dup: 5
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 131

Index IDX_HTF_DATE_TIMESTAMP (7)
Depth: 2, leaf buckets: 105, nodes: 69430
Average data length: 0.00, total dup: 69419, max dup: 69417
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 2
60 - 79% = 0
80 - 99% = 102

Index IDX_HTF_FILE_TYPE_AND_KEY (6)
Depth: 2, leaf buckets: 248, nodes: 69430
Average data length: 8.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 248

Index IDX_HTF_MADE_BY_AND_KEY (9)
Depth: 2, leaf buckets: 249, nodes: 69430
Average data length: 8.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 249

Index IDX_HTF_SUBSIDY_TYPE_AND_KEY (8)
Depth: 2, leaf buckets: 247, nodes: 69430
Average data length: 8.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 246

Index IDX_PROPERTY_SYSID_HTF (3)
Depth: 2, leaf buckets: 108, nodes: 69430
Average data length: 0.00, total dup: 68886, max dup: 809
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 6
60 - 79% = 0
80 - 99% = 101

Index RDB$PRIMARY49 (0)
Depth: 2, leaf buckets: 292, nodes: 69430
Average data length: 6.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 157
60 - 79% = 0
80 - 99% = 134


-----END OF PASTE-----------------------------

> Try this:
>
> 1. Fix the index on DATE_RECEIVED

Did this.

> 2. If you created any duplicate indexes for the existing PK or FK

indexes

> (as erroneously suggested by Aurimenes, DROP THEM. Never duplicate

these

> indexes.

I tried this, but I knew you are not supposed to do this, so I took
it off after I saw it did not help.

> 3. Move that filter so it is in the WHERE clause.
>

Did this.

> That will fix what's currently broken but, even if it helps speed a

little,

> it won't make much difference if the real killer is bad index

geometry. If

> those FK indexes show big numbers in Total Dup and Max Dup, there's

your

> problem. Try out these alternatives:
>
> 4.
> Ploy A: Force the optimizer to ignore the PK of LOOKUP_CODES by

modifying

> the WHERE clause:
>
> WHERE CHA.User_Sysid = 1007
> AND HTF.SubsidyType + 1 = HTF.SubsidyType + 1
> AND HTF.FileType + 1 = HTF.FileType + 1
> AND HTF.Made_By + 1 = HTF.Made_By + 1
>

This is REALLY BAD. A WHOLE LOT worse!

> *or*
> Ploy B: Drop any foreign keys on Lookup_Codes that you have in the

table.


There are no foreign keys in the HUD_TRACS_TABLE.

>
> *and/or*
> Ploy C: Create a highly selective index for each of the lookup

columns,

> involving the column + the PK in each case, in that order...let's

say your

> PK is P_Key:
>
> create index ix_SubsidyType on HUD_TRACS_FILES (SubsidyType, P_Key);
> create index ix_FileType on HUD_TRACS_FILES (FileType, P_Key);
> create index ix_Made_By on HUD_TRACS_FILES (Made_by, P_Key);
>

I did this and the plan does not show them being used. here is the
plain below. And you can tell I added them because they are in the
gstat that I pasted in. The new ones that you asked me to add end
with the word Key.... Ex. IDX_HTF_SUBSIDY_TYPE_AND_KEY

Here is the plan:

PLAN SORT (JOIN (JOIN (JOIN (JOIN (CHA INDEX (IDX_CHA_USER),HTF INDEX
(IDX_CONTRACT_SYSID_HTF)),LC INDEX (RDB$PRIMARY62)),LC1 INDEX
(RDB$PRIMARY62)),SUBSIDYTYPE INDEX (RDB$PRIMARY62)))


What do you think???

Thanks for all your help. I have faith that you will be able to
educate me even more. ;-)

Thanks

Trac

P.S.
On a side note : I do plan to buy your book when it comes out!