One minute is ridiculous, Lester! My guess (unlike Arno, that's my
standard way of handling the optimizer) is that Firebird use the primary
key for finding which records match the TICKET_ID, then gets all of
these, sort by TRANSACT and only then select the first or second of the
potential result set. That is something completely different from being
able to just use the primary key to locate the correct record
immediately! I doubt it is common to have one part of the primary key as
the only part of the where clause and the other part as the order by
clause, so I'm not surprised the optimizer doesn't help you much here!

Dimitrys solution sounds brilliant to me (even though I've never solved
anything this way). Another option is to try

SELECT T.PREVIOUS FROM TRANSACTIONS T
WHERE T.TICKET_ID = :TICKET_ID
AND NOT EXISTS(
SELECT * FROM TRANSACTIONS T2
JOIN TRANSACTIONS T3 ON T2.TICKET_ID = T3.TICKET_ID
WHERE T2.TICKET_ID = T.TICKET_ID
AND T.TRANSACT > T2.TRANSACT
AND T2.TRANSACT > T3.TRANSACT)

(remove T3 if you don't want to skip 1)

and look at the plan to try to understand what's going on (this latter
select would benefit from an index on TRANSACT, I think your original
query could do that too unless you followed Dimitrys advice, but ain't
100% certain).

What's your execution time like now? Subsecond?
Set

Lester Caine wrote:

> I think I already know the answer, but I'd like a bit more detail on why=20
> there was such a difference.
>
> TICKET - primary key INTEGER from GENERATOR
> TRANSACTIONS ( detail for TICKET ) - primary key INTEGER, TIMESTAMP
>
> I need the first and second TRANSACTIONS entry for each ticket, so I was=20
> using
> SELECT FIRST 1 (SKIP 1) PREVIOUS FROM TRANSACTIONS WHERE TICKET_ID =3D=20
> TICKET_ID ORDER BY TRANSACT.
> Which was showing that the plan used the primary key index.
>
> On 500,000 transactions this was starting to get to slow, approaching=20
> minutes for a months worth of stats :(
> We took advantage of the fact that we can run as many copies of Firebird=20
> as we like, and switched the long term report generation to a copy of=20
> Firebird on the backup computer. They do not need todays info for last=20
> weeks results.
>
> However the time taken was concerning when other reports on the same=20
> data take seconds.
>
> Tidying up the data to give more access to the details, I've added a=20
> TRANSACT_NO SMALLINT to transactions in addition to the TRANSACT=20
> TIMESTAMP - which is simply populated by 'CURRENT_TIMESTAMP' when a=20
> detail record is created. The only write activity on TRANSACTIONS is to=20
> insert a record via a trigger from TICKET updates.
>
> So now I have
> TRANSACTIONS ( detail for TICKET ) - primary key INTEGER, SMALLINT
> and
> SELECT PREVIOUS FROM TRANSACTIONS WHERE TICKET_ID =3D TICKET_ID AND=20
> TRANSACT_NO =3D 0 (or 1)
>
> And the PLAN is showing the same primary key index being used.
>
> So why does the TIMESTAMP key take so much longer to use than the=20
> SMALLINT. Is it just that you can't get direct to the data with the=20
> first and have to FIRST/ORDER. The majority of TICKET entries only have=20
> 2 transactions, and once the first value is found the second takes no=20
> time ( setting the second select to 0 does not noticeably affect the=20
> time of the first method which is what I did expect ).
>
> Can someone who understands the engine please fill in a little more=20
> explanation here?
>
> --=20
> Lester Caine - G8HFL