There are more than 10 million FileVersion(s) in data and assuming 2 Flag(s) for each gives us ~20 million events in data model.
The requirement is to get the latest Flag for each FileVersion and then show a count of FileVersion(s) by Flag. So the output is something like this:
Flag Count Other columns Flag1 11,232 ... Flag2 67,764 ... ...
We are using query similar to this (execution time ~600sec): |tstats latest(Flag) as Flag where datamodel=xxx by Source, IpAddress, FileName, FileVersion |stats count by Flag, Source, IpAddress, FileName
The problem is that tstats is taking long time due to high data cardinality. We even tried using prestats="t" but it does not help much (~10% performance increase).
Another caveat is that new Flag for FileVersion can flow in at any time and we need to show the counts based on latest Flag, so creating summary index is not feasible (we will have to run the summary index generating search very frequently and scan full index)
Is there any way we can improve the performance of the query or any better way to achieve the requirement.