Subject Re: [firebird-support] Re: MAX(ID) Performance
Author Thomas Steinmaurer
>> You need an DESCENDING index on ID.
> Ok, this trick is solved my problem for now, but I have same ordering details by ID field where I musn't use DESC indexing.
>> Btw, if you need that to do something like MAX(ID) + 1 for implementing
>> an auto-increment thingy, then you'd better try generators in Firebird.
>> Aka sequences in Oracle.
> I'm using generators in firebird trigered by on insert event triger in database, but when I need to know ne ID value on client for a new 100 records and when I have about 50 clients I cant fetch new generator value because of performance, so I fetch last ID (or actual generator value) and internal generate new values and than set up new value to generator (it mean that I define new value on client)!

If you need to know the PK value based on a generator in your client
application, then:

* Fetch the next generator value with GEN_ID(<gen_name>, 1) in your
client application and use that value in your insert statement. Your
existing before insert trigger needs to take care, that the generator
value isn't incremented again, in case the PK field is set, OR

* Use the INSERT INTO ... RETURNING ... stuff, which allows you to
return any field value in the same context, e.g. PK values set by a
before insert trigger, without re-querying MAX(ID) or the "current"
generator value with GEN(<gen_name>, 0), which isn't safe in a
multi-user environment anyway.

Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
My blog: