Subject [firebird-support] RE: frequent update a table, please give me some advise!
Author Svein Erling Tysvær
>Hi, every body!
> I have a problem. Ask everybody to help me.
> I have a table ,The table's DDL is
> ID integer,
> LastTime timestamp,
> LastReading numeric(12,2),
> Notes varchar(50)
> TableA have about 10,000 rows, use it to record the last time and the last value of every ID(node),
> every row was updated once everyday. After 30 days, perhaps 60 or 80 days. I can find query this
> Table become very slowly, just like FB dead or quer y was freezed.
> After 3-12 hours, the same query(select * from ext_meter ) become very quick.
> with query ext_meter be freezed. The database is running normally. I can query others talbe, execute
> procedures, and these are very quickly. even i can query a part recoreds of ext_meter.
> Please give me some adivses.

Hi Jimmy!

Please tell us more if you want an accurate answer to your problem. The two options that I think of, is:

a) a long running transaction holding up transaction counters. Use gstat to see if the gap between oldest (active) and next transaction is big (if this is the case, you would normally observe that many selects ran slow).

b) If you're using a transaction with lock resolution WAIT and another transaction has uncommitted changes to a record you are trying to modify. Try changing to NO WAIT or set a shorter LOCK TIMEOUT to see if you get an error message rather than things appearing to hang (normally, this should only happen if you UPDATE something, not if you only do SELECT).

Probably, there could be other reasons as well.

A table with only 10000 rows is tiny and shouldn't normally pose any problem. I think it is likely that if you explain what you do, then we can show you a better way to do what you really need.