On Fri, Feb 3, 2012 at 10:25 AM, <ruchbah@...> wrote:

>
> SQL databases are always in for a surprise about performance of simple
> statements.

Particularly true when you switch between record locking and MVCC.

>
> I have a table with the column 'ID' as BIGINT unique primary key.
>
> The table has about 4000000 entries, the ID counts up without gaps.
>
> A simple
>
>        select max(ID) from T
>
> takes about 14 seconds complete.
>
> Execution plan:
>
>        PLAN (T NATURAL)
>
> So Firebird seems to do plan a full table scan. (Hallo, anyone at home?
> I have an unique index on that column ;-) )

Yes, there is someone at home. The problem is that in an MVCC system,
the MAX value in the index may belong to a record that's not
appropriate for your transaction. Firebird indexes cannot be
traversed backward and can change between reads.

> Some databases can do so much better here: The same statement on a 100%
> identical Derby database completes immediately, as does on an Oracle 10g XE.

Right. And neither of those has index entries for transactions with
different snapshots of the database.

If you actually need to find the MAX often, create a descending index.
If you're using the MAX to figure out what the next id should be, use
generators (aka sequences).

Good luck,

Ann

>
> Do I miss something ? Any suggestions ?
>
> Best regards
>
> Marc
>
>
>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>