> I'm trying to understand why there is such a large difference in runtime
> between these 2 situations.
> firebird 2.1.5
>
> run a script to create 130000 records in a table, then use it for a while.
> Which means
> updates to some fields within that table.
>
> run "select * from table", takes around 4.8 seconds
>
> do a backup & restore of the database
>
> run "select * from table", takes around 0.3 seconds
>
> t
> he plan shows a natural scan in both cases, no other database usage

If you are using an Firebird architecture with cooperative garbage
collection, e.g. Classic in Firebird 2.1 or Classic/SuperClassic in
Firebird 2.5, then the difference in execution time might be garbage
collecting out-dated record versions.

MON$RECORD_STATS might give you a clue if a statement is actively
involved in garbage collection with Classic/SuperClassic. Due to the
nature of the MON$ tables being an activity snapshot for a certain
point-in-time, you may miss the statement.

The Trace API will give you this information for continuous statement
execution, but as you might know, the Trace API has been added in
Firebird 2.5, which aren't using.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.