Find last in a list of objects
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 ?
{
Property Subject As %String;
Property Author As %String;
Property Change As %TimeStamp;
}
and
{
Property Title As %String;
Property Changes As List Of Rick.ChangeItem ;
/// where & how create the index ?
}
Comments
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:
{
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.
{
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
}
oh.. yes .. Auditing and temporal are two different use cases
Versioning for example. Temporal queries and so on.
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.
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?
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