On 27/03/07, Milan Babuskov <milanb@...> wrote:

> Nick Upson wrote:
> > This works but I want/need something more efficient, the table
> > TBLCONNECTIOLOG can be very large (say 1, 000, 000) records and this
> > process could need to delete 95%.
> >
> > connectionlogid is the PK, generator supplied on insert
> >
> > This is the SP I have.
> >
> > select maxrecordcount from TBLSYSTEM into :maxrecordcount;
> >
> > select first 1 skip (:maxrecordcount) connectionlogid
> > from TBLCONNECTIONLOG order by OCCURREDAT desc into :id;
> >
> > delete from TBLCONNECTIONLOG where connectionlogid < :id;
>
> If you have indexes on OCCURREDAT and CONNECTIONLOGID it should be very
> quick. log2(1.000.000) is about 30, so...
>
>
> --
> Milan Babuskov

connectionlogid is the PK but that is currently the only index on the table