> Adding the "order by" clause, as you recommended, seems to have
> 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.
>
> Regards,
>
> Dan.

It won't do a sort if the field is indexed. The where clause will force the
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.
Alan