Oldest Record discrepancy?

Question

I was trying to determine the oldest record in a table and am having conflicting results.

Scenario 1

SELECT TOP 10 * FROM Ens_Util.Log ORDER BY TimeLogged ASC

SELECT TOP 10 text, TimeLogged, TraceCat, Type FROM Ens_Util.Log ORDER BY TimeLogged ASC

Text                            TimeLogged          TraceCat    Type
Email sent via mailserv:25      2016-05-08 01:00:01 (null)      4
[Requested record not found]    2016-05-08 01:01:13 (null)      2
[Requested record not found]    2016-05-08 01:04:39 (null)      2
[Requested record not found]    2016-05-08 01:04:53 (null)      2
[Requested record not found]    2016-05-08 01:05:22 (null)      2
[Requested record not found]    2016-05-08 01:05:45 (null)      2
[Requested record not found]    2016-05-08 01:05:58 (null)      2
[Requested record not found]    2016-05-08 01:05:58 (null)      2
[Requested record not found]    2016-05-08 01:06:08 (null)      2
[Requested record not found]    2016-05-08 01:06:15 (null)      2

Scenario 2a

SELECT TOP 10 TimeLogged FROM Ens_Util.Log ORDER BY TimeLogged ASC

TimeLogged
2015-10-10 16:30:46
2015-10-10 21:15:07
2015-10-11 22:08:18
2015-10-12 21:57:00
2015-10-13 21:39:27
2015-10-14 23:40:15
2015-10-15 23:28:10
2015-10-16 23:36:52
2015-10-17 23:10:04
2015-10-18 22:32:49

Scenario 2b

SELECT MIN(TimeLogged) FROM Ens_Util.Log

2015-10-10 16:30:46

Question

What is happening here? Why the discrepancy? What is the date of the oldest record? 2016-05-08 or 2015-10-10?

I would re-index and rebuild the stats then try it again – Peter4499 Jun 07 '16 at 22:15 — Peter4499, Jun 07 '16 at 22:15

score 5 · Accepted Answer · answered Jun 08 '16 at 07:49

5

More looks like your indices are incorrect, while in the different queries uses different indices. Just call this two commands, to purge all index, and rebuild it again, it may take a long time depends on how many logs you have.

do ##class(Ens.Util.Log).%PurgeIndices()
do ##class(Ens.Util.Log).%BuildIndices()

answered Jun 08 '16 at 07:49

DAiMor

3,185
16
24

Unfortunately I don't have the ability to do this but I have put in a request to our host. I'll update with the outcome. – Jun 08 '16 at 13:45
If you have an access, you may try to do it through [System Portal management](http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GOBJ_persother#GOBJ_persother_rls_existrbld) – DAiMor Jun 08 '16 at 14:06
"We can confirm what you have reported on that instance. We are investigating the cause of this at this time and will update you by tomorrow with our findings." – Jun 08 '16 at 16:12
"We have decided to rebuild the indices for the Ens_Util.Log table and it has cleared up the index entries pointing to non-existing log messages. " – Jun 08 '16 at 20:26

score 1 · Answer 2 · answered Jun 07 '16 at 23:01

A few things I would try:

checked that TimeLogged is really a datatime field
try to cast as string and sort (sometime implicit casts confuse sorting)
try to cast as date

worst case scenario :

try to order by Year(TimeLogged), Month(TimeLogged), Day(TimeLogged), Hour(TimeLogged), Minute(TimeLogged), Second(TimeLogged)

also, just in case :

try to select Year(TimeLogged), Month(TimeLogged), Day(TimeLogged), Hour(TimeLogged), Minute(TimeLogged), Second(TimeLogged) to see if you can extract correct values from TimeLogged

Oldest Record discrepancy?

2 Answers2