As a senior software and database developer, this is my experience: If anything can go wrong, it will! also known as "Murphy's law"
Happy coding Christian --- In firebird-support@yahoogroups.com, "skander_sp" <skander_sp@...> wrote: > > Is not a problem the concurrence, because this specifica case, only a dozen > of user working toghether and not probably they insert in the same > milisecond, but thks for the notice. > > And about the optimized index, I finally undestand, but i hope to get a > instant result for last year (actually only used) > > > --- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@> wrote: > > > > On Tue, Apr 16, 2013 at 3:57 AM, skander_sp <skander_sp@> wrote: > > > > > > > > Simple table > > > > > > CREATE TABLE ORDENES ( > > > ID_ORDEN INTEGER NOT NULL /* Primary key - GenId */ > > > YEAR_ORDEN INTEGER NOT NULL /* Year of Orden */, > > > N_ORDEN INTEGER NOT NULL /* Order in the Year */, > > > ... /* no matter */ > > > ); > > > > > > ALTER TABLE ORDENES ADD CONSTRAINT UK_ORDENES UNIQUE (YEAR_ORDEN, > > > N_ORDEN)USING DESCENDING INDEX UK_ORDENES_YEAR_N_ORDEN; > > > > > > > > > Now I need to access in the insert trigger, for next order to be assigned > > > to N_ORDEN in the YEAR_ORDEN using > > > > > > select coalesce(max(o.n_orden),0)+1 > > > from ordenes o > > > where o.year_orden=new.year_orden > > > into new.n_orden > > > > > > IT WORK!!!! nice, > > > > > > Not nice at all. It works single user, but it will fail in production, and > > you will get violations of your unique constraint. Remember that Firebird > > uses MVCC. If two concurrent transactions run the insert trigger they > > will generate the same value for new.n_orden. > > > > > > > > > BUT NOT OPTIMIZED, in the Performance Analisys it read (indexed) all the > > > N_ORDEN in the table, not going to the first (given the descending order > > > of > > > the index) > > > > > > How can optimize the index/query? > > > Or simply is not possible doing more? > > > > > > > > Firebird can use an index to optimize MAX, but only if it's the MAX of the > > whole key - not a partial key match. If you have a descending index on > > year_orden, Firebird will use an index to find the MAX of year_orden by > > walking down the left hand side of the index tree. You're asking it to > > find a specific year first, then the highest value of n_orden and that > > doesn't work. Even if it did, this code doesn't produce good unique > > numbers. > > > > Good luck, > > > > Ann > > > > > > [Non-text portions of this message have been removed] > > >
