Thank you for your answers.

First of all, I'm using Firebird since version 1.0 in many different projects and was always pleased with the performance and stability.

I don't want to complain about Firebird, I was just a bit surprised to find a performance problem, where I never expected it (and did not understand it), especially compared to other databases.

In my current project, I'm developing a middelware, using an SQL database as persistence. I try to support many different databases and like to keep the code as common as possible. (That's why I try not to add procedures and triggers)

Currently I'm using Derby, Oracle, Firebird and MS SQL, that's why I can easily compare the performance of this DBs in different areas.

I'm using identical indices on all these databases, and none of the other shows any delays on that max() function. (Even with MS SQL and Oracle using MVCC design)

In deed, I'm using the max() function to create IDs for new elements, but since this is done by a synchronized method within the middleware, it just need to be done once on the first connect. But this call for two tables needs up to 60 sec on an 3GHz i7 quadcore, with the database file on a SSD drive. This slows down the first login to the system.

Knowing nothing about the internal structure of an index in Firebird, I still believe, that getting the maximum value from an column with an ascending index should not need a full table scan.

Never mind, using a descending index does speed up the process as you suggested.

I find the following Note in the firebird documentation:

'If you define a descending constraint-enforcing index on a primary or unique key, be sure to make any foreign keys referencing it descending as well.'

Are the following statements correct?

create table a(
id int not null,
primary key (id) using desc index ai_pk
)

create table b(
ref int not null
)

alter table b add constraint bc_fk1 foreign key (ref) references a(id) on delete cascade using desc index bi_fk1


Has an descending index any performance drawbacks when inserting rows with ascending ids ?

Thanks !

Marc

--- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote:
>
> 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
> >
> >
> >
>