Hi again Sten,

>If I understand you right IB will store a copy of the old version of
>the record I am doing UPDATE on and deletes the copy when I do
>COMMIT.

It could do, but not necessarily. The old version is deleted when it is no
longer of interest, i.e. when ALL transactions that was started prior to
committing your update has finished (i.e. committed or rollbacked - and
this includes transactions that doesn't update anything).

>You suggests that I will run the delete quiery every hour for
>instance. Do you or anyone have a suggestion on a quier that
>doesen't take much CPU power. And what should the index look like to
>optimize performance

I don't quite understand what you mean by quier (soundwise I would guess
you meant choir which doesn't make any sense), but if I suppose you meant
quicker, then you could try doing it even more frequently than every hour.
If you do it every minute, you would delete approximately 400 records at a
time and more frequently than this sounds like exaggerating (though only
your testing will reveal what is most benefitial). As for the index, any
ascending index on the datetimefloat field will do (though it ought to
avoid many duplicates).

>My trend table looks like this and contains about 4 million rows and
>I want to delete all records older than a week.
>
>I'm currently using IBX components and I'm writing my application in
>C++ Builder 6.

OK, that explains IB 6.01, newer Firebird versions may or may not work with
IBX (though I think Fb 1.0 will work for the most part).

>/* Table: TREND */
>
>CREATE TABLE TREND (
> DATETIMEFLOAT DOUBLE PRECISION NOT NULL,
> TAGNR INTEGER NOT NULL,
> VALUE DOUBLE PRECISION,
> CREATOR VARCHAR (8));
>/* Primary keys definition */
>
>ALTER TABLE TREND ADD CONSTRAINT PK_TREND PRIMARY KEY
>(DATETIMEFLOAT, TAGNR);

I'm not certain whether it matters in your case or not, but in general it
is recommended to have a meaningless integer field as primary key. Do you
use NOW or CURRENT_TIMESTAMP to populate the datetimefloat
(current_timestamp will not change throughout your transaction, whereas now
do change - at least that's what I read on this list earlier today)? And
why do you use double precision rather than timestamp for your
datetimefloat field?

HTH,
Set