Re: [firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
Ok, some querys: 1 - No index: select coalesce(max(o.N_ORDEN), 0) + 1 from ORDENES O where O.EJER=2012 --into new.n_orden https://dl.dropboxusercontent.com/u/15932768/sin_indice.jpg 2 - With index, uses multiple records: create descending index ordenes_idx1 on ordenes (ejer,n_orden); select coalesce(max(o.N_ORDEN), 0) + 1 from ORDENES O where O.EJER=2012 https://dl.dropboxusercontent.com/u/15932768/usa_indice_muchos.jpg 3 - With index, use only 1 record: create descending index ordenes_idx1 on ordenes (ejer,n_orden); select first 1 coalesce(o.n_orden,0) + 1 from ordenes o where o.ejer=2012 order by o.ejer desc, o.n_orden desc --into new.n_orden; https://dl.dropboxusercontent.com/u/15932768/usa_indice_uno.jpg Regards. Christian. [Non-text portions of this message have been removed]
Re: [firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
On Tue, Apr 16, 2013 at 10:50 AM, 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. The persistence of plans for stored procedures depends to some extent on the architecture you use. For classic and superClassic, each new connection causes procedures to be reoptimized. For SuperServer, the easiest reliable way to force procedures to be reoptimized is to stop and restart the server. Procedure plans are not stored in the database, but in the servers memory. New server, new plans. Good luck Ann > > [Non-text portions of this message have been removed]
Re: [firebird-support] AGAIN (i know, but different) Max optimized... or not?
On Tue, Apr 16, 2013 at 3:57 AM, 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]
Re: [firebird-support] NBackup freezes OIT ?
On Tue, Apr 16, 2013 at 4:48 AM, remk_1 wrote: > > I just made some nbackup tests (FB 2.5.2 SS, Windows XP) and noticed that > whenever I use it to restore a database, it's Oldest transaction (OIT) gets > stuck. It remains at the same value until next database sweep. Is it a bug > or a feature ? > > The Oldest Interesting Transaction (OIT) is the oldest transaction in the system that is not committed. Ordinarily, that's a transaction that rolled back and could not be cleaned up automatically. Only sweep can change the OIT.The Oldest Active Transaction should move freely, unless you've got a long running transaction. The OAT blocks garbage collection and causes performance problems. The OIT is much less interesting. It does require that every transaction have access to a bitmap of transaction states between the OIT and the Next Transaction, but that's much less of an issue than it was when machines had memory measured in megabyte rather than gigs. So, neither a bug nor a features. Just an artifact. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
Try this: CREATE TABLE ORDENES ( ID_ORDEN INTEGER NOT NULL, FECHA DATE NOT NULL, EJER INTEGER, N_ORDEN INTEGER ); CREATE DESCENDING INDEX ORDENES_IDX1 ON ORDENES COMPUTED BY (ejer||';'||n_orden); select first 1 (ejer||';'||n_orden), n_orden from ordenes where (ejer||';'||n_orden) like '2013;%' order by 1 desc Just one indexes read! Em 16/04/2013 13:40, escreveu: > This index uk_... Is the descending one? > Give-me the ddl of this index. > Em 16/04/2013 13:06, "skander_sp" escreveu: > >> ** >> >> >> the plan is PLAN (O INDEX (UK_ORDENES_PRODUCCION_N_ORDEN)) >> and the readed rows it depend of the year... >> really i love to minimize the read for the last one year (the most used) >> but the only year read only a row is other with the higher N_Orden, all the >> rest read between some dozens and several hundreds. >> >> --- In firebird-support@yahoogroups.com, fabianoaspro@... wrote: >> > >> > 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" 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" 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" 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" 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? >>
Re: [firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
This index uk_... Is the descending one? Give-me the ddl of this index. Em 16/04/2013 13:06, "skander_sp" escreveu: > ** > > > the plan is PLAN (O INDEX (UK_ORDENES_PRODUCCION_N_ORDEN)) > and the readed rows it depend of the year... > really i love to minimize the read for the last one year (the most used) > but the only year read only a row is other with the higher N_Orden, all the > rest read between some dozens and several hundreds. > > --- In firebird-support@yahoogroups.com, fabianoaspro@... wrote: > > > > 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" 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" 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" 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" 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] > > > > > > > > > > > > > > > > > > > [
[firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
the plan is PLAN (O INDEX (UK_ORDENES_PRODUCCION_N_ORDEN)) and the readed rows it depend of the year... really i love to minimize the read for the last one year (the most used) but the only year read only a row is other with the higher N_Orden, all the rest read between some dozens and several hundreds. --- In firebird-support@yahoogroups.com, fabianoaspro@... wrote: > > 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" 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" 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" 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" 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] >
Re: Re[2]: [firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
Thk for clarify it for me! Em 16/04/2013 12:33, "Dmitry Kuzmenko" escreveu: > ** > > > Hello, fabianoaspro! > > Tuesday, April 16, 2013, 5:46:48 PM, you wrote: > > fgc> If you use this code inside a SP you must recompile it for using this > new > fgc> index. > > Just FYI - procedures does not store query plans, so they never need > to be recompiled. Procedure may not use new index because when it > was run, server computed plan, and still use it, while procedure > is being "in use", i.e. in server metadata cache. > When last user who run procedure disconnects, procedure's metatada > being unloaded from cache. And new run will create new plan for this > procedure. > > And, doing backup/restore never recompile any procedure/trigger/view. > > p.s. All I said is true for all InterBase (from 5.0) versions and for > all Firebird versions. > > -- > Dmitry Kuzmenko, www.ib-aid.com > > > [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/
Re[2]: [firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
Hello, fabianoaspro! Tuesday, April 16, 2013, 5:46:48 PM, you wrote: fgc> If you use this code inside a SP you must recompile it for using this new fgc> index. Just FYI - procedures does not store query plans, so they never need to be recompiled. Procedure may not use new index because when it was run, server computed plan, and still use it, while procedure is being "in use", i.e. in server metadata cache. When last user who run procedure disconnects, procedure's metatada being unloaded from cache. And new run will create new plan for this procedure. And, doing backup/restore never recompile any procedure/trigger/view. p.s. All I said is true for all InterBase (from 5.0) versions and for all Firebird versions. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
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" 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 > 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/
[firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
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 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] >
Re: [firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
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" 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" 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" 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" 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/
[firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
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" 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" 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" 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] >
Re: [firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
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" 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" 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" 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] ++ 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/
[firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
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]
Re: [firebird-support] Re: Another Unoptimized question
Yes, I also do this. Em 16/04/2013 11:35, "skander_sp" escreveu: > ** > > > So sad... > Tks. > > At least I know now I must avoid as many as i can... > > --- In firebird-support@yahoogroups.com, fabianoaspro@... wrote: > > > > Subselects never get optimized in the way you posted. > > Bad but truth. > > Em 16/04/2013 11:09, "skander_sp" escreveu: > > > > > ** > > > > > > > > > > > > May be obvious, but til now, i don't see how much unoptimized works the > > > "field in subselect" syntax. > > > > > > I'm using it in some procedure, just right til now, after check they > work > > > in the worse unoptimized mode... > > > > > > example: > > > > > > select * from Table1 T1 > > > where T1.id in (select T2.id > > > from Table2 T2 > > > where T2.Customer=:customer) > > > > > > of course T2.id is a Primary key and T1.id is a Foreing key > > > > > > I think they resolve getting the value(s) in the subselect, and then > > > applying to the main where, but the performace analisys show he run > all the > > > T1 table from begin to end and then check with the subselect > > > > > > of course I can solve (in procedure) with a FOR subselect to get every > > > value and check after not the nicest way, and pretty ugly and less > > > "human readable", but by far much more quick and optimized. > > > > > > for select T2.id > > > from Table2 T2 > > > where T2.Customer=:customer > > > into :id do > > > select * from Table1 T1 > > > where T1.id=:id > > > > > > There is some way to get a good result using "field in subselect"? some > > > trick to get an optimized result using this syntax? > > > > > > > > > > > > > > > [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/
[firebird-support] Re: Another Unoptimized question
So sad... Tks. At least I know now I must avoid as many as i can... --- In firebird-support@yahoogroups.com, fabianoaspro@... wrote: > > Subselects never get optimized in the way you posted. > Bad but truth. > Em 16/04/2013 11:09, "skander_sp" escreveu: > > > ** > > > > > > > > May be obvious, but til now, i don't see how much unoptimized works the > > "field in subselect" syntax. > > > > I'm using it in some procedure, just right til now, after check they work > > in the worse unoptimized mode... > > > > example: > > > > select * from Table1 T1 > > where T1.id in (select T2.id > > from Table2 T2 > > where T2.Customer=:customer) > > > > of course T2.id is a Primary key and T1.id is a Foreing key > > > > I think they resolve getting the value(s) in the subselect, and then > > applying to the main where, but the performace analisys show he run all the > > T1 table from begin to end and then check with the subselect > > > > of course I can solve (in procedure) with a FOR subselect to get every > > value and check after not the nicest way, and pretty ugly and less > > "human readable", but by far much more quick and optimized. > > > > for select T2.id > > from Table2 T2 > > where T2.Customer=:customer > > into :id do > > select * from Table1 T1 > > where T1.id=:id > > > > There is some way to get a good result using "field in subselect"? some > > trick to get an optimized result using this syntax? > > > > > > > > > [Non-text portions of this message have been removed] >
[firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
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" 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" 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] >
[firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
In a database with 2 dozens of tables with this kind of numerators, and everyone with new triggers, new queries, and new fields... not easy, in the way of speed up the running... The problem is the same, because the index is the same, a double field (year+orden) thks , i see not much to do, if speed keep going down, i'm afraid i'll make changes you proposes. Tks again. --- In firebird-support@yahoogroups.com, Christian Mereles wrote: > > Ok, I understand. While the second table "is not as complex" but if you want > to take the index optimally try something like: > > select first 1 n_orden from ordenes > where year_orden = new.year_orden > order by* year_orden desc, n_orden desc* > into new.n_orden; > > Regards. > > Christian > > > [Non-text portions of this message have been removed] >
Re: [firebird-support] Another Unoptimized question
Subselects never get optimized in the way you posted. Bad but truth. Em 16/04/2013 11:09, "skander_sp" escreveu: > ** > > > > May be obvious, but til now, i don't see how much unoptimized works the > "field in subselect" syntax. > > I'm using it in some procedure, just right til now, after check they work > in the worse unoptimized mode... > > example: > > select * from Table1 T1 > where T1.id in (select T2.id > from Table2 T2 > where T2.Customer=:customer) > > of course T2.id is a Primary key and T1.id is a Foreing key > > I think they resolve getting the value(s) in the subselect, and then > applying to the main where, but the performace analisys show he run all the > T1 table from begin to end and then check with the subselect > > of course I can solve (in procedure) with a FOR subselect to get every > value and check after not the nicest way, and pretty ugly and less > "human readable", but by far much more quick and optimized. > > for select T2.id > from Table2 T2 > where T2.Customer=:customer > into :id do > select * from Table1 T1 > where T1.id=:id > > There is some way to get a good result using "field in subselect"? some > trick to get an optimized result using this syntax? > > > [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/
[firebird-support] Another Unoptimized question
May be obvious, but til now, i don't see how much unoptimized works the "field in subselect" syntax. I'm using it in some procedure, just right til now, after check they work in the worse unoptimized mode... example: select * from Table1 T1 where T1.id in (select T2.id from Table2 T2 where T2.Customer=:customer) of course T2.id is a Primary key and T1.id is a Foreing key I think they resolve getting the value(s) in the subselect, and then applying to the main where, but the performace analisys show he run all the T1 table from begin to end and then check with the subselect of course I can solve (in procedure) with a FOR subselect to get every value and check after not the nicest way, and pretty ugly and less "human readable", but by far much more quick and optimized. for select T2.id from Table2 T2 where T2.Customer=:customer into :id do select * from Table1 T1 where T1.id=:id There is some way to get a good result using "field in subselect"? some trick to get an optimized result using this syntax?
[firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
Ok, I understand. While the second table "is not as complex" but if you want to take the index optimally try something like: select first 1 n_orden from ordenes where year_orden = new.year_orden order by* year_orden desc, n_orden desc* into new.n_orden; Regards. Christian [Non-text portions of this message have been removed]
[firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
Not so easy. I no need a UNIQUE value (this is the reason of a PK), i need a second "human" sequence, rising from 1 for every record in these year. With a Unique quey index (or a simplex index) with TWO fields, looks never can go to the last value, always run through some values. --- In firebird-support@yahoogroups.com, "Parzival" wrote: > > Hello, > > > > are you actually looking for an unqiue key or number? Like an unique order > number? If yes, is a generator an option? > > > > If it's not go for the recommendation of fabianoaspro and put ROWS 1 in the > select statement. This gives you only one record. > > > > Niko > > > > Von: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] Im Auftrag von skander_sp > Gesendet: Dienstag, 16. April 2013 09:58 > An: firebird-support@yahoogroups.com > Betreff: [firebird-support] AGAIN (i know, but different) Max optimized... > or not? > > > > > > 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] >
Re: [firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
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" 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" 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] ++ 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/
[firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
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" 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] >
[firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
Thks for the answer, but not the way i'm looking for The MAX in a Descendent index (or only a field) is a good way, don't force anyway. The problem is this not only a field, but two. I can't use a second table, because my point is optimize the index and query, not add complexity to the database --- In firebird-support@yahoogroups.com, Christian Mereles wrote: > > The MAX is forcing through all the records. > > select coalesce(max(o.n_orden),0)+1 > from ordenes o > where o.year_orden=new.year_orden > into new.n_orden > > > You could create an intermediate table to keep N_ORDEN: > > CREATE TABLE YEAR_ORDEN ( > YEAR_ORDEN INTEGER NOT NULL, > N_ORDEN SMALLINT NOT NULL, > > PRIMARY KEY (YEAR_ORDEN, N_ORDEN) > ); > > > CREATE OR ALTER TRIGGER MANTENER_NORDEN FOR ORDENES > ACTIVE BEFORE INSERT POSITION 0 > AS > begin > /* N_ORDEN, de tabla intermedia y uso optimo del indice */ > select Y.n_orden from YEAR_ORDEN Y >where Y.year_orden = new.year_orden > into new.n_orden; > > if (new.n_orden is null) > then new.n_orden = 1; > > update or insert into year_orden (year_orden, n_orden) > values (new.year_orden, new.n_orden + 1) > matching (year_orden); > > end > > > Regards. > > Christian > > > [Non-text portions of this message have been removed] >
AW: [firebird-support] AGAIN (i know, but different) Max optimized... or not?
Hello, are you actually looking for an unqiue key or number? Like an unique order number? If yes, is a generator an option? If it's not go for the recommendation of fabianoaspro and put ROWS 1 in the select statement. This gives you only one record. Niko Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Im Auftrag von skander_sp Gesendet: Dienstag, 16. April 2013 09:58 An: firebird-support@yahoogroups.com Betreff: [firebird-support] AGAIN (i know, but different) Max optimized... or not? 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]
Re: [firebird-support] AGAIN (i know, but different) Max optimized... or not?
Create a descending index with yearorden and norden in this sequence. Thats it. Em 16/04/2013 04:57, "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] ++ 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/
[firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
The MAX is forcing through all the records. select coalesce(max(o.n_orden),0)+1 from ordenes o where o.year_orden=new.year_orden into new.n_orden You could create an intermediate table to keep N_ORDEN: CREATE TABLE YEAR_ORDEN ( YEAR_ORDEN INTEGER NOT NULL, N_ORDEN SMALLINT NOT NULL, PRIMARY KEY (YEAR_ORDEN, N_ORDEN) ); CREATE OR ALTER TRIGGER MANTENER_NORDEN FOR ORDENES ACTIVE BEFORE INSERT POSITION 0 AS begin /* N_ORDEN, de tabla intermedia y uso optimo del indice */ select Y.n_orden from YEAR_ORDEN Y where Y.year_orden = new.year_orden into new.n_orden; if (new.n_orden is null) then new.n_orden = 1; update or insert into year_orden (year_orden, n_orden) values (new.year_orden, new.n_orden + 1) matching (year_orden); end Regards. Christian [Non-text portions of this message have been removed]
[firebird-support] NBackup freezes OIT ?
Hi all, I just made some nbackup tests (FB 2.5.2 SS, Windows XP) and noticed that whenever I use it to restore a database, it's Oldest transaction (OIT) gets stuck. It remains at the same value until next database sweep. Is it a bug or a feature ? Regards, Michal
[firebird-support] AGAIN (i know, but different) Max optimized... or not?
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
