Find last in a list of objects

Primary tabs

I have a class to track data changes.
As it becomes slower and slower to find last changes I think I need an index.
The 2 classes are just simplified reality.
HowTo ?

Class Rick.ChangeItem Extends %SerialObject
{
Property Subject As %String;

Property Author As %String;

Property Change As %TimeStamp;
}

and

Class DC.listItem Extends (%Persistent)
{
Property Title As %String;

Property Changes As List Of Rick.ChangeItem ;

/// where & how create the index ?
}
  • 0
  • 0
  • 600
  • 13

Replies

I see 2 options

#1) 

add the ChangeItem holding the last change in addition to your list of Objects.
and add the appiopriate indices.
like this:

Class Rick.ChangeList Extends %Persistent
{
  Property Title As %String;
  Property Changes As list Of Rick.ChangeItem;
/// Last change added to list
  Property Last As Rick.ChangeItem;
  Index xlast On Last.Change;
}

 

This looks simple but it requires that any program that adds a change also 
updates the "Last" property.
If this isn't feasible you may use %OnBeforeAddToSaveSet() or similar to insert you "Last" object during %Save().
It requires more space on disk and is not redundant.
It doesn't generate entries for existent records.This requires a special update exercise.

#2)

Add the property to be indexed as SqlComputed and index it.
This is not so speedy but gives you the flexibility not just check the last element
and has no impact or dependency on any existing code using it.

Class Rick.ChangeList Extends %Persistent
{
  Property Title As %String;
  Property Changes As list Of Rick.ChangeItem;
  Property lastDate As %TimeStamp [ Calculated, SqlComputed ,
          SqlComputeCode = { set {*} = ##class(Rick.ChangeList).ChangeDate({ID}) }];

Index xdate On lastDate [ Data = lastText ];
}
ClassMethod ChangeDate(ID As %Integer)
{
  set obj=##class(Rick.ChangeList).%OpenId(ID)    
     ,item=obj.Changes.GetAt(obj.Changes.Count())
   quit item.Change
}

Solution #2) looks better.
It is invisible to the rest of the application

thx

As mentioned.
This example is extracted from an application that runs since >15 yrs. It used to be kind of audit. But is surrounded by 1200..2000 classes, routines, ...
When the original in production was written  then SQL was kind of a dirty word.
not so dirty because only 3 letters , ...  wink

I talked to Rick on the phone:

he implemented proposal #2

 
 

 

is this for the the purpose of auditing datachange ? then why not using the built in auditing function by turning on the audit database for the appropriate events ? Now  the SQL Auditing is also available to monitor even data access.

DC.serItem was a mistake during copy from original.  Sorry.

SQL is rarely used for a few cases. 99.*% is object access.
Index is sometimes even scanned in  MUMPS style directly by $O() or $Q()

Class Rick.ChangeItem Extends %SerialObject ...

but

Property Changes As List Of DC.serItem;

It's also interesting how used ( insert, update, select ) DC.listItem in your app? via object  or/and SQL ( dirty ;) access?

 

OP said:

I have a class to track data changes.

As it becomes slower and slower to find last changes I think I need an index.

Both cases that I provided can be reasoned to be about "last changes".  Your solution #2 works nicely for

Get last X modified subjects

But it won't help to:

Get last X changes

Some ideas for solution #2:

  • Remove object access to improve speed (but that's the task for OP as it's related to global storage)
  • Make lastDate property Triggered computed (add SqlComputeOnChange = (%%INSERT, %%UPDATE) remove Calculated) instead of always computed to calculate last item only when adding  new change.

Hmmm....

I tried, and the storage map changed when I removed CALCULATED.
But this was my mayor reason to implement option #2 not to change global storage structure.

I do not like reviewing through ~60+ man years of code (quite MUMPSy, partial with direct global access) just for that.
And YES: it's slow but terminal input is much slower.

  • Remove object access to improve speed (but that's the task for OP as it's related to global storage)
​OK.  in benchmark quality:
instead of: 
  Property lastDate As %TimeStamp [ Calculated, SqlComputed ,
          SqlComputeCode = { set {*} = ##class(Rick.ChangeList).ChangeDate({ID}) }];

ClassMethod ChangeDate(ID As %Integer)
{
  set obj=##class(Rick.ChangeList).%OpenId(ID)    
     ,item=obj.Changes.GetAt(obj.Changes.Count())
   quit item.Change
}
only this:
  Property lastDate As %TimeStamp [ Calculated, SqlComputed ,
          SqlComputeCode = { set {*} = $lg($lg($lg({Changes},*)),3) }];

 

When talking about speed, one of the most important questions is what exactly do you need to speed up? You need to determine:

  • what queries are the most popular
  • what queries take the most time

to fix it. Some ways to fix performance are (easy to hard):

  • Add indices
  • Change classes
  • Change application architecture

Because in your case I see at least two request types which require different actions to speed them up:

  • Get last X changes
  • Get last X modified subjects