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

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_segment        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_page 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_protocolmsid_idx  nonclustered located on index_segment                                 protocol_message_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   0                0                    0 Aug 18 2003  3:28AM
 notification_send_idx          nonclustered located on index_segment                                 notification_sent_to_external, notified_on                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            0                0                    0 Aug 18 2003  3:31AM
 notification_messageid_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_threshold optimistic_index_lock 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_protocolmsid_idx  2106828 KB 2122560 KB 15732 KB
 notification_send_idx          3513844 KB 3552756 KB 38912 KB
 notification_messageid_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_idx
        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_idx
        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_idx
        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)



0
Jesus
10/18/2005 2:58:31 AM
sybase.ase.general 8655 articles. 0 followers. Follow

9 Replies
1152 Views

Similar Articles

[PageSpeed] 41
Get it on Google Play
Get it on Apple App Store

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

-bret


0
Bret
10/18/2005 3:39:26 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 ??




0
Jesus
10/18/2005 4:08:49 AM
Jesus M. Salvo Jr. wrote:

> 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 ??


I am trying to find out what does ASE do in the event of updating a column
that is part of an index. The URL below shows what happens with an insert
and delete ... but does not mention what happens to a non-clustered index
in an update on a DOL table:

http://manuals.sybase.com:80/onlinebooks/group-as/asg1251e/databases/%40Generic__BookTextView/21362;pt=21362#X


Also, I have changed the application so that the initial value is now
'1753-01-01 00:00:01' instead of '1753-01-01 00:00:00'.

Now the first few minutes, "set statistics io" show very few logical pages
read. However, as time goes by, the number of logical pages read increases
in small increments.


Table: message_notification_history scan count 1, logical reads: (regular=91
apf=0 total=91), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0

Table: message_notification_history scan count 1, logical reads:
(regular=103 apf=0 total=103), physical reads: (regular=0 apf=0 total=0),
apf IOs used=0
Total writes for this command: 0

Table: message_notification_history scan count 1, logical reads:
(regular=107 apf=0 total=107), physical reads: (regular=0 apf=0 total=0),
apf IOs used=0
Total writes for this command: 0



0
Jesus
10/18/2005 4:34:00 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 ) ??


0
Jesus
10/18/2005 6:43:14 AM
What does optdiag output look like for that table and index?  I would suspect
that there are a lot of "deleted" rows that are still pointed to.  You would
need to "reorg rebuild" the DOL table itself if there is no clustered index
which you could rebuild.

"Jesus M. Salvo Jr." <noone@noone.org> wrote in message
news:43547b38@forums-1-dub...
> Jesus M. Salvo Jr. wrote:
>
> > 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 ??
>
>
> I am trying to find out what does ASE do in the event of updating a column
> that is part of an index. The URL below shows what happens with an insert
> and delete ... but does not mention what happens to a non-clustered index
> in an update on a DOL table:
>
>
http://manuals.sybase.com:80/onlinebooks/group-as/asg1251e/databases/%40Generic_
_BookTextView/21362;pt=21362#X
>
>
> Also, I have changed the application so that the initial value is now
> '1753-01-01 00:00:01' instead of '1753-01-01 00:00:00'.
>
> Now the first few minutes, "set statistics io" show very few logical pages
> read. However, as time goes by, the number of logical pages read increases
> in small increments.
>
>
> Table: message_notification_history scan count 1, logical reads: (regular=91
> apf=0 total=91), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
> Total writes for this command: 0
>
> Table: message_notification_history scan count 1, logical reads:
> (regular=103 apf=0 total=103), physical reads: (regular=0 apf=0 total=0),
> apf IOs used=0
> Total writes for this command: 0
>
> Table: message_notification_history scan count 1, logical reads:
> (regular=107 apf=0 total=107), physical reads: (regular=0 apf=0 total=0),
> apf IOs used=0
> Total writes for this command: 0
>
>
>


0
Sherlock
10/18/2005 6:15:20 PM
Sherlock, Kevin wrote:

> What does optdiag output look like for that table and index?  I would
> suspect
> that there are a lot of "deleted" rows that are still pointed to.  You
> would need to "reorg rebuild" the DOL table itself if there is no
> clustered index which you could rebuild.
> 

HMmm ... the HK GC is still running on that index even afer almost 12 hours that I noticed it.

1) Is it possible that the "negative" deleted rowcount ( See below ) 
in optdiag will cause this ?

2) Since the HK GC seems to be running on the index 
( based on sp_sysmon 'cache wizard' showing high physical read/sec ), 
how can a "reorg rebuild" on the DOL table itself fix it ? ... 
What about a "reorg compact" or "reorg reclaim_space" on the table ?


Here's the optdiag output that was done yesterday, 
after a reorg rebuild of the index and after an update index statistics:


OptDiag/12.5.3/EBF 12330 ESD#1/P/Sun_svr4/OS 5.8/ase1253/1900/32-bit/OPT/Tue Jan 25 00:39:30 2005
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

Server name:                            "syddbshared"

Specified database:                     "sgmaster"
Specified table owner:                  "dbo"
Specified table:                        "message_notification_history"
Specified column:                       not specified

Table owner:                            "dbo"
Table name:                             "message_notification_history"

Statistics for table:                   "message_notification_history"

     Data page count:                   3421523
     Empty data page count:             0
     Data row count:                    117040561.0000000000000000
     Forwarded row count:               -3134.0000000000000000
     Deleted row count:                 -34.0000000000000000
     Data page CR count:                429342.0000000000000000
     OAM + allocation page count:       37531
     First extent data pages:           91295
     Data row size:                     88.2233554570997001

  Derived statistics:
     Data page cluster ratio:           0.9995153044863230
     Space utilization:                 0.7451530431202396
     Large I/O efficiency:              0.9966186040364353


< ... snip.... >

Statistics for index:                   "notification_send_idx" (nonclustered)
Index column list:                      "notification_sent_to_external", "notified_on"
     Leaf count:                        876547
     Empty leaf page count:             114557
     Data page CR count:                33375304.0000000000000000
     Index page CR count:               96037.0000000000000000
     Data row CR count:                 70259980.0000000000000000
     First extent leaf pages:           48
     Leaf row size:                     23.7317588064490650
     Index height:                      3

  Derived statistics:
     Data page cluster ratio:           0.5281909295309782
     Index page cluster ratio:          0.9989914219670344
     Data row cluster ratio:            0.4117376895238360
     Space utilization:                 0.7824130575163538
     Large I/O efficiency:              0.9929894485863244

< ... snip.... >

0
Jesus
10/18/2005 9:03:27 PM
Does the table have a clustered (placement) index?  If not, create one and drop
it.  If you don't want to do that, try updating the stats on the heap itself to
see if the negative number disappear:

update statistics message_notification_history message_notification_history

"Jesus M. Salvo Jr." <noone@noone.org> wrote in message
news:4355623d$2@forums-2-dub...
> Sherlock, Kevin wrote:
>
> > What does optdiag output look like for that table and index?  I would
> > suspect
> > that there are a lot of "deleted" rows that are still pointed to.  You
> > would need to "reorg rebuild" the DOL table itself if there is no
> > clustered index which you could rebuild.
> >
>
> HMmm ... the HK GC is still running on that index even afer almost 12 hours
that I noticed it.
>
> 1) Is it possible that the "negative" deleted rowcount ( See below )
> in optdiag will cause this ?
>
> 2) Since the HK GC seems to be running on the index
> ( based on sp_sysmon 'cache wizard' showing high physical read/sec ),
> how can a "reorg rebuild" on the DOL table itself fix it ? ...
> What about a "reorg compact" or "reorg reclaim_space" on the table ?
>
>
> Here's the optdiag output that was done yesterday,
> after a reorg rebuild of the index and after an update index statistics:
>
>
> OptDiag/12.5.3/EBF 12330 ESD#1/P/Sun_svr4/OS 5.8/ase1253/1900/32-bit/OPT/Tue
Jan 25 00:39:30 2005
> 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
>
> Server name:                            "syddbshared"
>
> Specified database:                     "sgmaster"
> Specified table owner:                  "dbo"
> Specified table:                        "message_notification_history"
> Specified column:                       not specified
>
> Table owner:                            "dbo"
> Table name:                             "message_notification_history"
>
> Statistics for table:                   "message_notification_history"
>
>      Data page count:                   3421523
>      Empty data page count:             0
>      Data row count:                    117040561.0000000000000000
>      Forwarded row count:               -3134.0000000000000000
>      Deleted row count:                 -34.0000000000000000
>      Data page CR count:                429342.0000000000000000
>      OAM + allocation page count:       37531
>      First extent data pages:           91295
>      Data row size:                     88.2233554570997001
>
>   Derived statistics:
>      Data page cluster ratio:           0.9995153044863230
>      Space utilization:                 0.7451530431202396
>      Large I/O efficiency:              0.9966186040364353
>
>
> < ... snip.... >
>
> Statistics for index:                   "notification_send_idx" (nonclustered)
> Index column list:                      "notification_sent_to_external",
"notified_on"
>      Leaf count:                        876547
>      Empty leaf page count:             114557
>      Data page CR count:                33375304.0000000000000000
>      Index page CR count:               96037.0000000000000000
>      Data row CR count:                 70259980.0000000000000000
>      First extent leaf pages:           48
>      Leaf row size:                     23.7317588064490650
>      Index height:                      3
>
>   Derived statistics:
>      Data page cluster ratio:           0.5281909295309782
>      Index page cluster ratio:          0.9989914219670344
>      Data row cluster ratio:            0.4117376895238360
>      Space utilization:                 0.7824130575163538
>      Large I/O efficiency:              0.9929894485863244
>
> < ... snip.... >
>


0
Sherlock
10/18/2005 10:04:11 PM
Sherlock, Kevin wrote:

> Does the table have a clustered (placement) index?  If not, create one and
> drop
> it.  If you don't want to do that, try updating the stats on the heap
> itself to see if the negative number disappear:
> 
> update statistics message_notification_history
> message_notification_history
> 

Hi Kevin,

Thanks again for your reply.


1) So negative values in systabstats does affect the behaviour of HK GC ?

2) How is that syntax above different from simply doing:

        update statistics message_notification_history




0
Jesus
10/18/2005 11:49:12 PM
See below:

"Jesus M. Salvo Jr." <noone@noone.org> wrote in message
news:435589f7@forums-1-dub...
> Sherlock, Kevin wrote:
>
> > Does the table have a clustered (placement) index?  If not, create one and
> > drop
> > it.  If you don't want to do that, try updating the stats on the heap
> > itself to see if the negative number disappear:
> >
> > update statistics message_notification_history
> > message_notification_history
> >
>
> Hi Kevin,
>
> Thanks again for your reply.
>
>
> 1) So negative values in systabstats does affect the behaviour of HK GC ?

I don't know, but you're mixing threads here.  I was only addressing the
possible reasons for so many IO's when searching on "1753-01-01 00:00:00".


> 2) How is that syntax above different from simply doing:
>
>         update statistics message_notification_history

By specifying the heap as the "index", you only generate statistics for the heap
and not all of the other indexes.  So, it runs faster, and it specifically asks
for stats on the heap table (index 0 if you will).  I only suggested that syntax
so your updated stats would run faster.   The above syntax should give you the
same results for the heap (table), but it also generates histograms and index
stats for the rest of the indexes.


0
Sherlock
10/19/2005 4:20:43 PM
Reply:

Similar Artilces:

can not convert '01/01/1900 00:00:00' to a Timestamp
I would like to do this INSERT INTO ita_badgeage VALUES ( '01/01/1900 00:00:00') I have got this error "cannot convert 01/01/1900 00:00:00 to a Timestamp" i try this but I have got the same problem : set option PUBLIC.timestamp_format = 'DD/MM/YYYY HH:NN:ss'; thank for your help evincent@itlink.fr No you change the date_order, not the date_format. Date_format is for DISPLAY purposes only. set option date_order='MDY' If this is via ODBC you must issue this AFTER connecting: set temporary option date_order='MDY' -- ...

Urgent: series_date = ' 0000-00-00 00:00:00' ?
Hi, I just upgraded one of my Bugzilla servers from 2.20 to 2.20.1 and now I can't create charts anymore. In the apache error log I see: chart.cgi: Use of uninitialized value in concatenation (.) or string at Bugzilla/Chart.pm line 237 It seems the $datefrom is the problem on line 221 [...] my $datefrom = $dbh->selectrow_array("SELECT MIN(series_date) " . "FROM series_data " . "WHERE series_id IN ($series_ids)"); [-> $datefrom = '0000-00-00 00:00:00' ] $...

Converting '2008-03-17T00:00:00-04:00:00' to a DateTime object
I'm dealing with some Yahoo API's and the documentation says: To obtain one day's worth of data in US Eastern Standard Time (EST), specify: startDate: 2007-01-15T00:00:00-05:00 endDate: 2007-01-15T00:00:00-05:00So i get the server's TimeZone information likeDim ts As TimeSpan = TimeZone.CurrentTimeZone.GetUtcOffset(i_StartDate)ts = TimeZone.CurrentTimeZone.GetUtcOffset(i_StartDate)which results in "-04:00:00"and March 13th, 2008 is the day i am starting at, so how do i put the date and timezone information together in .NET (VB or C# is fine) so i can creat...

display DB default (1900-01-01 00:00:00.000) datetime in gridview as blank
Hi there and thanks in advance for any answer. I have a db table where I store production task items. In that table I have a field which is a datetime. I'm currently displaying the production tasks on a gridview. My problem is that when the manager does not specify a the datetime when he first fills out the order form, on the gridview I get the infamous 1900-01-01 00:00:00.000 db default time, and thats just nice.  Is there a way to simply display blank?? Or is there a way to input blank or null to the db?? Adam How are you inserting the records into the database if nothing i...

NULL DATE IS NOT NULL BUT 01/01/00 00:00
The following works if I'm in design mode, but it doesn't in the release version. I got a column field with a mask as mm/dd/yy hh:mm, when the value is null, it shows 00/00/00 00:00 which is what I want, but when user wants to delete an existing date, let's say 06/05/02 12:00, instead of going back to 00/00/00 00:00 it goes to 01/01/00 00:00. I am using PB 8 Ent and W2K operating system, database is in SQL2000. If I look at the value in the database it got 01/01/00 instad of <NULL>. try check 'blank is null' @ datawindow field setting... & allow ed...

3.0.7 Issue
This is a section of my 01.00.00.SqlDataProvider script for a custom module I made: CREATE PROCEDURE {databaseOwner}{objectQualifier}GetEmpAppsModules @EmpAppModuleID int AS SELECT t.[EmpAppModuleID], t.[ModuleID], t.[CreatedByUserID], t.[CreatedDate], [CreatedByUserName] = u.FirstName + ' ' + u.LastName FROM {databaseOwner}[{objectQualifier}EmpAppsModules] t LEFT OUTER JOIN {databaseOwner}[{objectQualifier}Users] u ON t.CreatedByUserID = u.UserID WHERE t.[EmpAppModuleID] = @EmpAppModuleID When I upload the PA zip file through Host/Module...

Set dateTime variable to 01/01/1900 00:00
Just a very simple question and I know most of you may be able to answer this. I want to set the value for a datetime variable to 01/01/1900 00:00 (the format of this is dd/MM/yyyy hh:mm). I try to do this using the following code: Dim strDate as String = "01/01/1900 00:00"Dim ciCultureInfo As New CultureInfo("en-GB") Dim dtDate as DateTime = Convert.ToDateTime(strDate, ciCultureInfo) I get the error: String was not recognized as valid date time. Can someone help me out on this?  Basically, I just need to set the value for the dtDate variable to 01/01/1900 00:0...

Cast from string "00/00/00" to type 'Date' is not valid
hi, I am trying to solve this proble, for over 2 days and still.. i couldnt solve it  i am getting this error: Run-time exception thrown : System.InvalidCastException - Cast from string "00/00/00" to type 'Date' is not valid. how do i put "00/00/00" or even a NULL value ??  DATEE is diffine as datetime type in the data basenewNode = xmlTos.CreateElement("DATEE") newNode.InnerText = ="00/00/00" 'If Not IsDate(newNode.InnerText) Then ' newNode.InnerText = DBNull.Value 'System.DBNull.Value.ToString 'End If If n...

BOClean Update File released- FILEDATE: 05/10/05
FILEDATE: 05/10/05 - 00:39:01 (US EDT) (04:39:01 GMT/UTC) Fifteen new nasties today for a total of 6377 UNIQUE trojans (36,911 trojans, worms, rootkits, adware, spyware, keyloggers, "dialers" and other malware in total, including all variants) covered in today's update for BOClean 4.12. To UPDATE your BOClean 4.12, doubleclick on your BOClean traybar icon and select "check for update" to have BOClean 4.12 automatically collect and install your update for you. BOClean 4.12 is designed to perform an autoupdate if left configured to do so. If you h...

Problem with a Delete statement error: Syntax error (missing operator) in query expression '(([FechaTramite]>=7/18/2005 12:00:00 AM) AND ([FechaTramite]<=7/26/2005 12:00:00 AM))'.
Hi All:I have a simple page with two Calendars and I want to delete all the rows in the DB beteewn two dates selected from the calendars.My code is the following:  protected void Button1_Click(object sender, EventArgs e) { String qry; string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("/mensajeria/mensajeria.mdb;"); qry = "DELETE FROM [envios] WHERE (([FechaTramite]>=" + Calendar1.SelectedDate + ") AND ([FechaTramite]<=" + Calendar2.SelectedDate + "))"; TextBox1.Text = Calendar1.SelectedDate.ToString(); TextBox2.Text = Calenda...

Setting a valid default DateTime value (It doesn' t accept "00/00/00")
Hi,I am sending a date interval to the DB so that the user can search orders by specifying such interval. Somebody knows how can I pass a valid default DateTime value to the DB in case the user has not specified a date?For example: Sub search_orders(Sender As Object, e As EventArgs) Dim fromDate As DateTime If fDate.checked = True Then  fromDate = f_date.Text Else  fromDate = "00/00/00" End If  Dim toDate As DateTime If tDate.checked = True Then  toDate = t_date.Text Else  toDate = "00/00/00"  End If  Response.Re...

Date format of 0000-00-00 or 00:00:00
I am converting a MySQL database to SQL server 2000. I noticed that in one of the database fields in a table, they set a default value for the field as 0000-00-00. Is this okay for a default value for SQL Server 2005 ? How about a default value of 00:00:00 ? Will '00:00:00' is ok, while '0000-00-00' is invalid. A quick test in SQL Server: create table testDate (id int,t smalldatetime default '00:00:00') insert into testDate(id) select 1 create table testDate1 (id int,t smalldatetime default '0000-00-00') insert into testDate1(id) select 1 The 2nd insert will fail because '0000-00-00'...

PB4 and 00/00/00
I have an application developed in PB4. On some of my dates, I would like to display 00/00/00. When I do this though, it is displayed and stored as 01/01/00. How can I obtain the desired results? Thank You, P.J. Borandi pborandi@nb.net I had the same problem. The workaround is to use four digit dates. It will clear out 00/00/0000 with no problem. Brian CPD Mark A. Berardi wrote in message ... >I have an application developed in PB4. On some of my dates, I would like >to display 00/00/00. When I do this though, it is displayed and stored as >01/01/00....

Setting time to 00:00:00 in a datetime
Hei! I do an insert like this: INSERT into <some table> (today, ....) select getdate() .... The field today is of type datetime. The result is 07.01.2004 15:24:34 But I want the result to be 07:01:2004 00:00:00 How can I do that? Kjell Pettersen "Kjell Pettersen" <kjell@gpsgroup.cc> wrote in message news:3ffc1c40$1@forums-2-dub... > Hei! > > I do an insert like this: INSERT into <some table> (today, ....) select > getdate() .... > > The field today is of type datetime. > > The result is 07.01.2004 15:24:34 ...

Web resources about - Bug with a datetime of '1753-01-01 00:00:00' ?? - sybase.ase.general

Resources last updated: 12/5/2015 3:38:50 PM