Hello kragh.thomas@...,

Thursday, August 25, 2016, 8:00:28 AM, you wrote:

> On my website i have a tabel where i log some web requests - about 90.000/hour.
> Every hour this SQL is executed in a stored procedure:

> delete from requests where created < dateadd(-7 day to current_timestamp);

1. Try this instead:

- if you don't care about parts of days --
delete from requests where created < (current_date - 7)

- if you do care about parts of days --
delete from requests where created < (current_timestamp - 7)

Reason: a straight comparison with the context variable should be
faster than repeated calls to the function.

(Also, if the CREATED column is DATE and not TIMESTAMP, you don't need
the extra cost of comparing it with a TIMESTAMP.)

2. Does your stored procedure do something else besides delete
request records? If not, why not just call the query dynamically?
And, if it does do something else, is it something expensive like
returning a dataset or updating a log file?

> This results in about 90.000 deleted rows.

> However, after the statement has started, i see the number of
> attachents/statements spike at 150-200 attachments for about 20-30
> seconds. This results in connection pool full exceptions on the web
> server, and it looks like the database server is not executing other
> statements for that small period of time.
>

Knowing your Firebird model could be material here. 90,000 delete
stubs is a big heap of garbage for collection, which will hit the
first thread or process that touches the table after the commit
completes UNLESS your server model is Superserver AND you have
configured GCPolicy to "background". Under all other conditions,
garbage collection is "cooperative", although in Superserver the default
is BOTH cooperative and background ("mixed"). Normally you won't
notice the difference; but committing a process that has deleted
90,000 rows in a single transaction will certainly be noticed.

> Please note that the writing of the row in the web request tabel,
> is done by a background thread.

To Firebird, it is just another connection.

In answer to your later comment, you might like to think about
setting that index inactive to test whether it is useful enough to
justify the extra overhead of index garbage collection afterwards.

Helen