> SQL databases are always in for a surprise about performance of simple > statements. > > 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 ;-) ) > > 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. > > Do I miss something ? Any suggestions ?
You need an DESCENDING index on the primary key column to speed up a MAX operation, but I wonder what you are doing with that value then? Hopefully not incrementing the value by 1 and use that as primary key value then? -- With regards, Thomas Steinmaurer (^TS^) Firebird Technology Evangelist http://www.upscene.com/ http://www.firebirdsql.org/en/firebird-foundation/
