Here is my solution, which has a number of advantages:
- there are no restrictions to the order of ID
- you can search in queries for the entire timestamp or for parts of it
- standard, reliable and proven Caché features are used
- this works very, very fast.
See my article for details: Indexing of non-atomic attributes
Class dc.TSOrder Extends (%Persistent, %Populate)
{
Index Extent [ Extent, Type = bitmap ];
Index iSmartTS On (TS(KEYS), TS(ELEMENTS));
Index iTS On TS;
Property TS As %TimeStamp(MAXVAL = "2016-07-31 23:59:59.999999", MINVAL = "2016-07-01 00:00:00.000000");
Property Data As %String(MAXLEN = 200, MINLEN = 100);
ClassMethod TSBuildValueArray(
value,
ByRef array) As %Status
{
i value="" {
s array(0)=value
}else{
s date=$p(value," ",1),
time=$p(value," ",2),
array("date")=date,
array("time")=time,
array("ddhh")=$p(date,"-",3)_"-"_$p(time,":",1)
}
q $$$OK
}
/// d ##class(dc.TSOrder).Fill()
ClassMethod Fill(N = {30e6})
{
d ..%KillExtent(), ..Populate(N), $system.SQL.TuneTable($classname(),$$$YES), $system.OBJ.Compile($classname(),"cu-d")
}
}Results from SMP:
select distinct null from dc.TSOrder where TS between {ts '2016-07-01 00:00:00.00000'} AND {ts '2016-07-01 23:59:59.999999'}
Performance: 2.339 seconds 2109755 global references 14768692 lines executed
(the number of selected records is 968476, used the normal index)
select distinct null from dc.TSOrder where for some %element(TS) (%key='date' and %value = '2016-07-01')
Performance: 2.269 seconds 1936962 global references 15496098 lines executed
(the number of selected records is 968476, used the "smart" index)
select distinct null from dc.TSOrder where for some %element(TS) (%key='ddhh' and %value = '01-13')
Performance: 0.096 seconds 80488 global references 644270 lines executed
(the number of selected records is 40239, used the "smart" index)- Log in to post comments