pdhpoolplayer1957 wrote:

> 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?
>
> TIA for your time and expertise.

How long will it take _without_ "help" from the index?
If you don't want to drop the index, try this:
SELECT datetime FROM IPCLOG WHERE datetime+0 < '05/01/2007'
(note the "datetime+0").
This will be a table scan, but if the result set includes most of the
table, it might be faster than going through the index. Even though
Firebird should visit "interesting" pages just once (after checking the index).

To keep data transfer over the network out of the picture, you could try
SELECT max(datetime) FROM IPCLOG WHERE datetime < '05/01/2007'
and
SELECT max(datetime) FROM IPCLOG WHERE datetime+0 < '05/01/2007'
If the running times are close, well, we'll have to ponder some more!


--
Aage J.