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

//----------------------------------

NOTE : I have about 13 columns listed in the select statement but I
did not think they would be necessary for a answer or suggestion. If
you need them let me know.


If you use the query above the stats are as follows :

NOTE : I use IBAdmin to get the stats. I believe the numbers I have
below are the number of times it has to go to that table.

WITH ORDER BY
LOOKUP_CODES 205,989
HUD_TRACS_FILES 68,738
CONTRACTS_HAS_ACCESS 549

Page Reads : 41918
Memory Buffer reads : 964787

PLAN :

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



Without order by
LOOKUP_CODES 117
HUD_TRACS_FILES 39
CONTRACTS_HAS_ACCESS 1

PLAN :

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

Page Reads : 32
Memory Buffer reads : 561




Now, my index on the DATE_RECEIVED column is named
IDX_HTF_DATE_RECEIVED. And it does not seem to matter if I add desc
or not. It seems to take the same amount of time if order by any
field in the query.


Thanks for any info you can give me.

Trac Taylor