ASE Database forum - Bug with a datetime of '1753-01-01 00:00:00' ??

Author

Bug with a datetime of '1753-01-01 00:00:00' ??

Jesus M. Salvo Jr.

2005-10-27, 8:21 am

1> select @@version
2> go

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-
Adaptive Server Enterprise/12.5.3/EBF 12330 ESD#1/P/Sun_svr4/OS 5.8/ase1253/1900/32-bit/FBO/Tue Jan 25 07:02:08 2005

(1 row affected)

For the life of me, I could not figure out why a query with a specific datetime value of '1753-01-01 00:00:00' takes a looong time.
But if I specify any other datetime value, even just adding 1 ms second ( i.e.: '1753-01-01 00:00:00.01' ), then the query is very snappy.
In both cases ... the query plan is the same ... and returns zero rows.

I have tried reorg rebuild on the index, update index statistics, etc... with the same results.
I tried replicating the problem on a smaller table on a test environment, but I cannot replicate it.

It is as if there is something wrong with '1753-01-01 00:00:00'. Judge for yourself below:

Here are the details:

1> sp_help message_notification
_history
2> go
Name Owner Object_type
------------------------------ ------------------------------ --------------------------------
message_notification
_history dbo user table

(1 row affected)
Data_located_on_segm
ent When_created
------------------------------ --------------------------
default Aug 14 2003 3:10PM
Column_name Type Length Prec Scale Nulls Default_name Rule_name
Access_Rule_name Identity
------------------------------ ------------------------------ ----------- ---- ----- ----- ------------------------------ -----------
------------------- ------------------------------ --------
message_notification
_historyid numeric 9 18 0 0 NULL NULL
NULL 0
message_id numeric 9 18 0 0 NULL NULL
NULL 0
protocol_message_id varchar 100 NULL NULL 1 NULL NULL
NULL 0
notified_on datetime 8 NULL NULL 0 NULL NULL
NULL 0
notification varchar 1000 NULL NULL 0 NULL NULL
NULL 0
done_date varchar 100 NULL NULL 1 NULL NULL
NULL 0
notification_sent_to
_external datetime 8 NULL NULL 1 NULL NULL
NULL 0

index_name index_description index_keys
index_max_rows_per_p
age index_fillfactor index_reservepa
gegap index_created
------------------------------ -------------------------------------------------------------------- ---------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ------
---------- -------------------- -------------------
message_notification
_pk nonclustered, unique located on index_segment message_notification
_historyid

0 0 0 Aug 18 2003 3:23AM
notification_protoco
lmsid_idx nonclustered located on index_segment protocol_message_id

0 0 0 Aug 18 2003 3:28AM
notification_send_id
x nonclustered located on index_segment notification_sent_to
_external, notified_on

0 0 0 Aug 18 2003 3:31AM
notification_message
id_idx nonclustered located on index_segment message_id

0 0 0 Aug 18 2003 10:02AM

(4 rows affected)
No defined keys for this object.
Object is not partitioned.
Lock scheme Datarows

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
------------ -------------- ---------- ----------------- ------------
0 0 0 0 0

(1 row affected)
concurrency_opt_thre
shold optimistic_index_loc
k dealloc_first_txtpg
------------------------- --------------------- -------------------
15 0 0
(return status = 0)

1> sp_spaceused message_notification
_history, 1
2> go
index_name size reserved unused
------------------------------ ---------- ---------- ----------
message_notification
_history 0 KB 13686980 K 3376 KB
message_notification
_pk 2089556 KB 2115244 KB 25688 KB
notification_protoco
lmsid_idx 2106828 KB 2122560 KB 15732 KB
notification_send_id
x 3513844 KB 3552756 KB 38912 KB
notification_message
id_idx 1552664 KB 1573644 KB 20980 KB

(1 row affected)
name rowtotal reserved data index_size unused
------------------------------ ----------- --------------- --------------- --------------- ---------------
message_notification
_history 117012849 23051184 KB 13683604 KB 9262892 KB 104688 KB
(return status = 0)

====================
===============
1) If I specify a query like this, it is very quick:

1> select count(1)
from message_notification
_history readpast
where
notification_sent_to
_external = '2005-10-18 11:56:00'2> 3> 4>
5> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is EXECUTE.
Executing a previously cached statement.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.

FROM TABLE
message_notification
_history
Nested iteration.
Index : notification_send_id
x
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
notification_sent_to
_external ASC
Using I/O Size 4 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

STEP 2
The type of query is SELECT.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.

-----------
0
Table: message_notification
_history scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.

(1 row affected)

====================
===============
2) If I change the datetime to be '1753-01-01 00:00', then it is slow, and has a lot of I/O ...
even though it uses the same execution plan as the previous query !!

1> select count(1)
from message_notification
_history readpast
where
notification_sent_to
_external = '1753-01-01 00:00'2> 3> 4>
5> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is EXECUTE.
Executing a previously cached statement.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.

FROM TABLE
message_notification
_history
Nested iteration.
Index : notification_send_id
x
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
notification_sent_to
_external ASC
Using I/O Size 4 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

STEP 2
The type of query is SELECT.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.

-----------
0
Table: message_notification
_history scan count 1, logical reads: (regular=66060 apf=0 total=66060), physical reads: (regular=52732 apf
=0 total=52732), apf IOs used=0
Total writes for this command: 302

Execution Time 98.
SQL Server cpu time: 9800 ms. SQL Server elapsed time: 99130 ms.

====================
===============
3) Now if I add just 1 millisecond, '1753-01-01 00:00.01', then it is snappy again:

1>
select count(1)
from message_notification
_history readpast
where
notification_sent_to
_external = '1753-01-01 00:00:00.01' 2> 3> 4> 5>
6> go

QUERY PLAN FOR STATEMENT 1 (at line 2).

STEP 1
The type of query is EXECUTE.
Executing a previously cached statement.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.

FROM TABLE
message_notification
_history
Nested iteration.
Index : notification_send_id
x
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
notification_sent_to
_external ASC
Using I/O Size 4 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

STEP 2
The type of query is SELECT.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.

-----------
0
Table: message_notification
_history scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.

(1 row affected)

Bret Halford

2005-10-27, 8:21 am

How many rows in the table? (I suspect this has something to do with this
being the minimum legal value for datetime).

-bret

Jesus M. Salvo Jr.

2005-10-27, 8:21 am

Jesus M. Salvo Jr. wrote:

> 1> select @@version
> 2> go
>
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

> Adaptive Server Enterprise/12.5.3/EBF 12330 ESD#1/P/Sun_svr4/OS
> 5.8/ase1253/1900/32-bit/FBO/Tue Jan 25 07:02:08 2005
>
> (1 row affected)
>
>
> For the life of me, I could not figure out why a query with a specific
> datetime value of '1753-01-01 00:00:00' takes a looong time. But if I
> specify any other datetime value, even just adding 1 ms second ( i.e.:
> '1753-01-01 00:00:00.01' ), then the query is very snappy. In both cases
> ... the query plan is the same ... and returns zero rows.
>
> I have tried reorg rebuild on the index, update index statistics, etc...
> with the same results. I tried replicating the problem on a smaller table
> on a test environment, but I cannot replicate it.
>
> It is as if there is something wrong with '1753-01-01 00:00:00'.

After further investigation, the application insert rows into the table with
an initial value for the " notification_sent_to
_external" as '1753-01-01
00:00:00'.

Once the rows are processed, they are then updated to the current datetime
( getdate() ).

So it seems that these update on the column is causing the leaf pages on
indexes to be moved to a different intermediate page(s) .. but can it fully
explain the slowness that I am seeing ??

Jesus M. Salvo Jr.

2005-10-27, 8:21 am

Bret Halford wrote:

> How many rows in the table? (I suspect this has something to do with this
> being the minimum legal value for datetime).
>
> -bret

About 117 Million rows.

However, I stated later on this thread that it is probably due to the
application inserting rows into this table with a value of '1753-01-01
00:00:00' ... and then later on updating that column to the current
datetime ( via getdate() ) after the row has been processed.

Thus, '1753-01-01 00:00:00' is a way for the application to find out which
rows are not yet processed.

Can anyone explain to me what effect does an update has on a non-clustered
index on a DOL table .... where the column update is part of the index
( composite or otherwise ) ??

Sponsored Links