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
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 databas
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, fabian
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 fireb
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
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
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 fireb
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 t
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,
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
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 hu
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
You can design your own index, as you consideration for the pourpose.
I'll try it
--- In firebird-support@yahoogroups.com, fabianoaspro@... wrote:
>
> This index uk_... Is the descending one?
> Give-me the ddl of this index.
> Em 16/04/2013 13:06, "skander_sp" escreveu:
>
> > **
> >
> >
> > the
LOL, nice try,... and really pretty curious, they "seems" works,
but don't do in this way.
May be you must remember in strings "9" is higher than "80"
then for a (tricky) aproach to working way...
CREATE DESCENDING INDEX ix_ordenes_produccion_orden ON ordenes_produccion
COMPUTED BY (cast(eje
Thks, is a great appendix for all the prior messages,
And is definetively the explanation and solution.
Tks very much
--- In firebird-support@yahoogroups.com, Christian Mereles
wrote:
>
> Ok, some querys:
>
> 1 - No index:
>
> select coalesce(max(o.N_ORDEN), 0) + 1 from ORDENES O
> where
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" wrote:
>
> Is not a problem the concurrence, because this specifica case, only a dozen
I'm also quite of this... (developer ;))
And the project is safe in this way, believe me, after more than 8 years with
this sequence, no one customer has reported problem (may be some time has
occurred) and the simplicity and speed deserve this really little risk (if some
error is reported, the
Of course you can use a foot to kill an ant, or a rock, or a nuclear bomb
I'm pretty sure for the actual application and future use, is not a risk, and
even in case of an eventually error, any nuclear missil will be fired by
mistake ;)
It's only a database for an store, with multiple users,
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 wo
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 necesa
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 "com
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, yo
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
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 '20
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
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
I would also strongly advise against this design. It is a design for
failure. Not immediate failure but eventual failure.
On Wed, Apr 17, 2013 at 8:12 AM, chris.waldmann <
christian.waldm...@rte-ag.ch> wrote:
> As a senior software and database developer, this is my experience:
>
> If anything ca
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
w
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 sto
29 matches
Mail list logo
