| Subject | Re: Slow SELECT/DELETE |
|---|---|
| Author | Adam |
| Post date | 2007-06-04T23:38:40Z |
--- In firebird-support@yahoogroups.com, "pdhpoolplayer1957"
<pdhpoolplayer1957@...> wrote:
garbage collection during your select. There are probably a number of
factors at play.
The way the Firebird 1.5 index structure works makes it expensive to
clean up poor indices (ie, an index with lots of duplicate values). It
is not clear to me whether your datetime field fits this definition.
Firebird 2 will probably be a real help here, because it structures
the index in a manner that it is far less expensive to clean up. The
new ability to select garbage collection mode may also be useful.
It may be less expensive to disable the index before running your
select the first time, committing, then enable the index again.
We are missing some pieces of information though. You mention it takes
1.5 hours to select those records, yet you fail to mention how many
records that is (except that it is less than 30 million). Also, how
are you measuring it? It looks like a simple select statement, meaning
it should return the first record pretty much straight away (unless
there is some grouping, order by, distinct, union etc you are not
telling us). Flamerobin does not (in my observations) do an immediate
fetchall (only those records required to fill the visible section of
the grid).
Adam
<pdhpoolplayer1957@...> wrote:
>If you have just done a bulk delete, Firebird is probably performing
> I have a Firebird database (1.5) that serves as the data store for the
> IPCheck server monitoring application. We upload the log table
> (IPCLOG) from Firebird to MSSQL so we can perform data analysis and
> reporting. The log table typically contains ~30 million records.
> We've been experiencing extremely slow performance on the upload, so we
> decided to attempt to delete older records prior to the upload.
>
> Using the FlameRobin interface to our Firebird instance, I entered a
> simple query:
>
> SELECT datetime FROM IPCLOG WHERE datetime < '05/01/2007'
>
> This query takes over 1.5 hours to run. There is an index on the
> datetime field, and the statistics have been updated. Is there
> anything else I can do to improve this abysmal performance?
garbage collection during your select. There are probably a number of
factors at play.
The way the Firebird 1.5 index structure works makes it expensive to
clean up poor indices (ie, an index with lots of duplicate values). It
is not clear to me whether your datetime field fits this definition.
Firebird 2 will probably be a real help here, because it structures
the index in a manner that it is far less expensive to clean up. The
new ability to select garbage collection mode may also be useful.
It may be less expensive to disable the index before running your
select the first time, committing, then enable the index again.
We are missing some pieces of information though. You mention it takes
1.5 hours to select those records, yet you fail to mention how many
records that is (except that it is less than 30 million). Also, how
are you measuring it? It looks like a simple select statement, meaning
it should return the first record pretty much straight away (unless
there is some grouping, order by, distinct, union etc you are not
telling us). Flamerobin does not (in my observations) do an immediate
fetchall (only those records required to fill the visible section of
the grid).
Adam