AnsweredAssumed Answered

Up To 30 Million Records - Calculate or Script a Field?

Question asked by jobu on Nov 29, 2018
Latest reply on Nov 30, 2018 by jobu

I have a file that is setup to randomly create 300,000 Events of 2 to 100 participants. I have used scripting to then rank the scores (lowest to highest) which seems to run fairly quickly. Where I have run into performance issues is when I went to mark scores that were ties and then assigning a value based on how many participants were tied at that score.

 

To somewhat easily accomplish the marking of records that were ties and to also give the tie score a value (RankValue) I created a self-relation to the Score table named TieCount where id_Event = id_Event and Score=Score. A calculation field then set to Count(TieCount::ID_Score) made it easy to determine if an individual score was a tie and to set the RankValue which was simply 1 divided by the number of scores tied at that score for that event.

 

This seems to be where my script bogs down and becomes very slow. So I have instead scripted Ties by just running through each record in the event, while sorted by score low to high, by keeping track of the last score I can loop through the records in the event and set ties if the current records score is the same as the last. That misses the first tied score which I then loop backwards through the records and repeat the process. This does not seem to take much time given the amount of records but I am stuck on how to best tackle the RankValue thorugh scripting rather than a calculation.

Attachments

Outcomes