> On 25/07/2014 06:46, [email protected] [firebird-support] wrote: >> >> Hi, >> >> 1. I've deleted 500000 records from TABLE A. I've commited transaction. >> 2. I made SELECT * FROM TABLE A. Transaction was starting for a very >> long time. >> >> I just wanted to make sure that there is no way to prevent this >> behaviour? I mean, I can not delay somehow clearing of this garbage? >> >> As far as I understand this article: >> http://www.firebirdsql.org/manual/gfix-housekeeping.html >> >> >> there is no such possibility becasue: >> >> >> "Whenever a subsequent transaction reaches garbage from a /committed/ >> transaction, that garbage is automatically cleared out." >> > So it appears. Essentially part of the cost of doing the deletion is > palmed off onto some later user, who is faced with unpredictable delays > in consequence. For example an overnight housekeeping job can result in > the first end user transaction of the day taking orders of magnitude > longer than usual (and timing out and crashing, if its timeouts are set > to something reasonable). > > You can avoid this cost being passed onto the poor user who through no > fault of his own is the first to follow the housekeeping by including > the "SELECT * FROM TABLEA" as part of the cleanup job, thus putting the > cost back with its owner. In other words you can't delay the garbage > collection, but you can make it happen earlier so that it's predictable.
SELECT COUNT(*) might be better because all pages are read and only a single value is transferred to the client. And the whole only works as advertised when old record versions are no longer "interesting" to other concurrent transactions. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
