At 05:56 PM 26/04/2004 +0200, you wrote:

>I'm performing a very simple query (no joins) using a stored procedure on a
>table with about a million of records. The query retrieves a single record.
>On the table there is an index that is used by the query (i've checked the
>plan) to perform the search.
>I'm experiencing a very slow response time (8/10 minutes) of this query the
>first time it's executed after a long period of idleness of the server
>(i.e., during the night the software it's not used and the delay is
>experieced in the morning). After the first query all the system becomes
>responsive as it usually has been in the past (there were less record in
>the past, but they grow constantly while the delay has beed reported to
>appear suddendly).
>The server is Firebird 1.0.3
>I've performed usual maintenance doing a backup and restore and i've
>recomputed all indexes (this is also done during restore i think).
>Anyone has some ideas on what can i do to solve this problem?

Possibly it's the first thing that happens after some job the day before
performed a lot of deletes or updates on the table. The first login next
day cops all the garbage collection.

If this is the case, could you leave an overnight job running that performs
a select 1 from that_table, ends the transaction, then does a manual sweep?
--------------
Another possibility is that the database file is caught up in a system
backup that has sectors locked, or a file lock. The first is worse,
because it can potentially corrupt the database.
--------------
If you are on XP, I guess you know about the SystemRestore problem...

/heLen