Put here the new ddls then the sql you use and the plan used. How many records it reads to get the new key? Em 16/04/2013 12:01, "skander_sp" <skander...@yahoo.com> escreveu:
> ** > > > well, im not doing tryes in SP but in direct query, and i get different > result, but not an optimistic one... > > Respect to the "compile or not" , my tests say to me it's enought to > disconnect and reconnect again with the front as IBExpert (or exit and run > the program). It's true, if don't do this, some time even not see the > changes in some SP or TRIGGERS > > --- In firebird-support@yahoogroups.com, fabianoaspro@... wrote: > > > > Maybe I'm wrong but in my tests you need to recompile or do a full > > backup/restore cycle. > > Be sure you create the index Decending and that you put desc on both > > statements when using the sql - like the another friend posted. > > Em 16/04/2013 11:32, "skander_sp" <skander_sp@...> escreveu: > > > > > ** > > > > > > > > > Since when is necesary to recompile? > > > Plans are not stored in the SP, the are calculated first time you use, > > > after a connection > > > > > > May be necessary to disconnect and reconnect, but i doubt i need to > > > recompile all my procedures to use new index. > > > > > > At least I read this ages ago, about the core of firebird. > > > Or it's not? > > > > > > --- In firebird-support@yahoogroups.com, fabianoaspro@ wrote: > > > > > > > > If you use this code inside a SP you must recompile it for using > this new > > > > index. > > > > Also is a good task to recompute the selectivity of the others > indexes. > > > > Sorry my bad english. > > > > Em 16/04/2013 10:40, "skander_sp" <skander_sp@> escreveu: > > > > > > > > > ** > > > > > > > > > > > > > > > Tks! > > > > > This is a good solution... (and my first try) > > > > > But don't work, still read the whole table for the yearorden given. > > > > > > > > > > I found a couple of "unoptimized" black-point in my application. > > > > > And what it looks to be nice and wick became an pain in the ass. > > > > > > > > > > --- In firebird-support@yahoogroups.com, fabianoaspro@ wrote: > > > > > > > > > > > > Create a descending index with yearorden and norden in this > sequence. > > > > > Thats > > > > > > it. > > > > > > Em 16/04/2013 04:57, "skander_sp" <skander_sp@> escreveu: > > > > > > > > > > > > > ** > > > > > > > > > > > > > > > > > > > > > Easy (and usual case) > > > > > > > > > > > > > > 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, 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? > > > > > > > > > > > > > > Tks in advance > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > > [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/
