At 07:03 PM 27/09/2005 -0400, you wrote:

>I have a table with 17 million rows, running on a dual-Xeon
>hyperthread-enabled W2K3 server, with Firebird 1.5 classic and a
>dialect 1 database.
>
>The table has a single primary key, and a single foreign key. No
>other indices. The table baiscally looks like this:
>
>create table SKU_PRICE (
> sku_price_id integer not null,
> sku_id integer not null,
> start_date date not null,
> end_date date not null,
> price numeric(9,2),
> ins_date date,
> upd_date date);
>
>SKU_PRICE_ID is the primary key. SKU_ID is a foreign key.
>
>The only triggers are a before insert which sets the primary key from
>a generator, and a before update that sets UPD_DATE to 'now'.
>
>I am running the following query:
>
>update
> sku_price
>set
> end_date = (end_date + 1) - (1/24/60/60)
>
>which basically adds 23 hours 59 minutes and 59 seconds to end date to
>deal with some queries that include time instead of just date.
>
>The query has been running for nearly four hours now. I see no
>appreciable disk or cpu activity. I'm wondering what the problem
>could be, why this is taking so long?

Do you know yet whether it is taking a long time, or is hanging up? Is it
possible that your application is swallowing an exception somewhere, that
precludes the statement from completing?

There are a few ponderables here...

If the server is allowing you to have a DATE type that is really a
timestamp, then this is not just a dialect 1 database, but an old ODS
(on-disk structure), i.e. an IB 5.6 or lower database structure.

Your current database structure would be ODS 10 if you are using a version
of it that has been restored under Firebird. In that case, all of those old
ODS 9 DATE columns would appear as TIMESTAMP now.

Neither situation is optimal in Fb 1.5, since you won't be getting the
benefits of changes in the optimizer. In the case of an ODS 9 database,
the "benefits" might well work against performance.

If you are trying to do this with a DB tool, is it a tool that correctly
detects the ODS and dialect of the database; or is it perhaps one that
tries to perform some kind of conversion on your SQL to make it "valid"?

All that past, is it possible that END_DATE is null in a lot of
cases? This would be possible in a non-nullable column if you had added
this column to a table that already had data, and forgot to go through and
write a default date into that column... In that case, the engine could
not perform a calculation on the column and it would also encounter a
problem when trying to write NULL back into the column - though I would
expect to see an exception here, not a hang-up...which probably gets us
back to application code swallowing an exception and precluding the
operation from continuing...

./heLen