Please send to me. If you want you can send me the fdb too. In theory it will use the descending index to reach the year and then get the last key ot that year. I have one similar table that have 15.000.000 records and it is instantly. Em 16/04/2013 12:12, "skander_sp" <skander...@yahoo.com> escreveu:
> ** > > > Ok, you can see as you want, i had here values (I can send to you, and > check). > The result is SAME PERFORMANCE in both cases, because a "MAX" over a > indexed field, use the index, then give result without read all table. > > The problem is a TWO FIELDS index like this, never can reduce his > performance to one read, cause the index has the values ordered, and need > to find the year first then the first one (descendant) value. > > Tks anytime, more opinions make me see clear other ways, but no one is > "perfect" one-only-read for all cases (years) > > --- In firebird-support@yahoogroups.com, Christian Mereles <chmereles@...> > wrote: > > > > Check the query, just read ONE REGISTER to return the MAX each year !!!. > > > > select first 1 n_orden + 1 from ordenes > > where year_orden = new.year_orden > > order by year_orden desc, n_orden desc > > into new.n_orden; > > /*----------------------------------------------------------*/ > > > > The query that you use does the same, returns the MAX, but read all > records > > !!!. > > > > select coalesce(max(o.n_orden),0)+1 > > from ordenes o > > where o.year_orden=new.year_orden > > into new.n_orden; > > /*----------------------------------------------------------*/ > > > > I thought that was the problem, read all the records for the MAX. > > > > Regards. > > > > Christian > > > > > > [Non-text portions of this message have been removed] > > > > > [Non-text portions of this message have been removed] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 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 <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/