Aage, thanks for the help.

but here are the results.....


Ok I tried what you asked. There was no difference. But let me add
something else I found.

If I add a where statement like "where htf.contract_sysid = 2121',
the stats are closer to where there is NOT a order by statement

The stats of that query are very different

These are the stats with a where statement and order by statement

Lookup_codes = 705
HUD_TRACS_FILES = 235
Contracts_HAS_ACCESS = 1

These are the stats without the where stament and with the order by

Lookup_codes = 205,906
HUD_TRACS_FILES = 68,747
Contracts_HAS_ACCESS = 549.


These are the stats without the where statment and without the order
by

Lookup_codes = 117
HUD_TRACS_FILES = 39
Contracts_HAS_ACCESS = 1



While I understand that the where statement will cut the set down, I
guess I just do not understand why it has to go to the LOOKUP_CODES
for every record in the set times 3 (because I am joining to it 3
times) where there is an order by statement. It seems like it would
be close to the same amount of hits to each table with or without a
ORDER BY statement. What am I missing here?|




--- In firebird-support@yahoogroups.com, Aage Johansen
<aagjohan@o...> wrote:

> On Wed, 21 Jan 2004 17:30:26 +0000 (UTC), tractaylor wrote:
>
> > 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.
>
>
> Do you have an index on HTF.DATE_RECEIVED? If so, try to remove it.
> If HTF.DATE_RECEIVED needs to be indexed, try this:
>
> select HTF.DATE_RECEIVED, HTF.DATE_RECEIVED+0
> ...
> order by 2 desc
>
> (or use HTF.DATE_RECEIVED||'' if it is a varchar/char field)
>
>
> --
> Aage J.
>
>
> >
> > //----------------------------------
> > 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
> >