Hi Lawrence,

> We have a 5GB Database FB2.5 Win2008 Server 4 Core M/C, 143 tables, problem with table INV 48K Records

Which Firebird architecture?

> When everyone logs off and then log on, access to INV is slow for the last 7K Records. If we fetch all, it takes forever, but eventually can log off and log on again and all is ok. Same is OK if we set all indexes ACTIVE (takes 4 hours for this table), or do a backup. Also if we sweep. this takes the same or more time.
>
> Questions:
>
> If we backup and restore the database is like new? data exported and then reloaded into a copied schema? This seems to work.
>
> Can sweep occur if the database has connections, but no activity? If it did, then we would not get all the sweep operations stacked up.
>
> Why does sweep (or the slow backup) take so long and if so, why is there no cpu load? I would have thought that the CPU would have been busy.
>
> In the app - one transaction and all datasets/queries/procedures are commitretaining.

Evil. ;-)

> And, and why always this table - treated much the same as other tables.
>
> Are there special settings for the database connection? and how can we know that this might happen (so we could backup and restore before the last user logged out)?

This all sounds like the usual problem with inproper client transaction
management, possibly additionally with cooperative GC or sweeping taking
place.

- Run gstat -h when the database is slow
- Use the MON$ tables to identifiy long-running transactions
- Use the Trace API to identify problematic stuff.

I won't do shameless product plugs, which would ease some work, here
now. ;-)



--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/