At 05:31 PM 21/01/2004 +0000, you wrote:

>Hey Everyone!
>
>Let me start by saying I am using Firebird 1.5 RC8.
>
>I have a question about one of my sql statements in my applications.
>The sql will be below. Basically, I have this query that runs fast
>normally. But as soon as I put a order by clause, it takes lots of
>time. I do have a index of the order by column I am using. Let me
>show you the sql and the plans that are being used. I also have the
>stats that I will show below.
>
>//----------------------------------
>SQL QUERY:
>
>SELECT HTF.CONTRACT_SYSID
>
>FROM HUD_TRACS_FILES HTF
>inner Join CONTRACTS_HAS_ACCESS CHA on HTF.CONTRACT_SYSID =
>CHA.CONTRACT_SYSID and CHA.User_Sysid = 1007 /* UserKey */
>inner join Lookup_Codes SubsidyType on HTF.SUBSIDY_TYPE =
>SubsidyType.Code_Id
>inner join Lookup_Codes LC on HTF.File_Type = LC.Code_Id
>inner join Lookup_Codes LC1 on HTF.MADE_BY = LC1.Code_ID
>
>order by HTF.DATE_RECEIVED DESC
>
>//----------------------------------

SELECT
HTF.CONTRACT_SYSID,
...
FROM HUD_TRACS_FILES HTF
inner Join CONTRACTS_HAS_ACCESS CHA
on HTF.CONTRACT_SYSID = CHA.CONTRACT_SYSID
/* and CHA.User_Sysid = 1007 DON'T PUT THIS HERE */
inner join Lookup_Codes SubsidyType
on HTF.SUBSIDY_TYPE = SubsidyType.Code_Id
inner join Lookup_Codes LC
on HTF.File_Type = LC.Code_Id
inner join Lookup_Codes LC1
on HTF.MADE_BY = LC1.Code_ID

WHERE CHA.User_Sysid = 1007

order by HTF.DATE_RECEIVED DESC

1. Move the search filter out of the join clause and into a WHERE clause
where it belongs.

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.

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"

----------------------------------------------------------------

Try this:

1. Fix the index on DATE_RECEIVED
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.
3. Move that filter so it is in the WHERE clause.

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

*or*
Ploy B: Drop any foreign keys on Lookup_Codes that you have in the 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);

/heLen