Subject Re: Stored procedure execution time
Author esbreidenbach
Sorry for the lack of detail, here is more info:

The stored procedure is run as part of a desktop application. The purpose of the procedure is to update two integer fields in a table. The values for these fields are updated based on a number of other factors which I wont bother you with, but suffice it to say that they change often. Whenever the procedure is run, it would update approximately the same number of records; the time of day does not affect the amount of "work" it has to do.

The user runs the procedure often during the day. A connection to the database is made when the user first logs in, so I am assuming that the initial delay is not due to making a connection.

The firebird server is running in classic mode, with forced writes on. We did this because we have had a few instances of data corruption at this particular installation. I noticed that we have a fair number of firebird "zombie" processes in the task manager, which I assume occurs because one of the clients has disconnected abruptly.

We see regular errors in the log file (a couple of times per day minimum, often around the time they might be closing the desktop application) regarding

INET/inet_error: read errno = 10054

The database also provides data for a web application that is used primarily in the evenings.

--- In, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
> >Hi,
> >I have an application which executes a stored procedure in Firebird 1.5.6 on a fairly
> >regular basis (multiple times per day).
> >
> >Immediately following a backup/restore of the database, the procedure runs fairly quickly
> >(less than a second or two) at any time of day.
> >
> >Gradually, over time (month or so), the first execution of the procedure on any given day
> >starts taking longer and longer (15 seconds up to minutes). However, subsequent executions
> >of the same procedure the rest of the day run just as quickly as before.
> >
> >I have checked, and it does not appear to be any other processes running during the initial
> >execution.
> >
> >Any ideas on what causes this?
> Sorry, but I think you need to describe your problem more thoroughly on the important parts for us to be of any help. We know nothing about your database (we know it is in Fb 1.5.6, but nothing about its size, content and use) and nothing about what your stored procedure does. I'm pretty certain that Firebird doesn't care what time of day it is used, so there must be something different between the first execution and latter executions of the SP that you haven't told us about (I assume the timing you tell us strictly applies to the stored procedure and doesn't include the time it takes to connect/disconnect or start/commit a transaction).
> Typically, slowdowns over time relates to poor handling of transactions, but that would be a general problem, not something that happens only in the morning (though of course, it might be the reason if the first execution involves lots of records whilst subsequent executions involve few records - it would still be a general problem, but you may not notice if something takes 0.1 second as opposed to 0.003 seconds) or only to a particular procedure.
> Looking forward to your new problem description,
> Set