|Subject||RE: Re: [firebird-support] Join with first detail record|
> Adding the "order by" clause, as you recommended, seems to haveIt won't do a sort if the field is indexed. The where clause will force the
> slowed down the select (testing on a small test database, not the
> live database), and I suspect that Firebird is doing an
> additional sort, although I know of no way of confirming this
> inside an SP. The "field2" clause is actually an integer
> representation of a date, and all detail records were inserted in
> date order (the application inserts data in batches, by a single
> process, and no other process ever inserts records into these
> tables). I also created an index for the "masterid" field, and
> that is the index used if I don't include the order by clause.
> Thus, I am very tempted to leave the "order by" out, and let it
> use the masterid index. As long as I never get records out of
> date order, I should be fine. If they do get out of date order,
> I will, of course, get unexpected and unpredictable results....
> However, the actual detail table currently has upwards of 16
> million rows (expected to grow to several hundred million over
> the next year), with the average master record having tens of
> thousands of detail records, so I really really don't want
> Firebird trying to do a sort of many thousands of detail records
> for every row of the master table if I don't have to.
> Unless you or someone else sees something dreadfully wrong with
> this, I'm going to continue down this path, as this seems like a
> much better solution than the multiple sub-selects or my original
> solution involving two separate selects.
server to go straight to the smaller subset of records you want, then sort
this subset only. The masterid field is a foreign key - if you've defined it
as such it will already have an index - don't create another one - this may
slow things dow if you do.
No order by clause will sooner or later deliver unexpected results - no
question of that - no matter how you think the records are added. Next time
you backup and restore, the records may not be in the same natural order.