🐛 Rules sidebar takes a long time to load sometimes

[BrookJeynes]

Cc: @JackDevAU @Aibono1225 @adamcogan

Hi Team,

Describe the Bug

Sometimes the latest rules widget seems to take forever to load (sometimes longer than a minute!)

To Reproduce

Steps to reproduce the behavior:

1. Go to ssw.com.au/rules
2. See long load time for latest rules widget (this doesn't seem to happen everytime)

Expected Behavior

The latest rules widget loads fast consistently

Tasks

• [ ] Investigate
• [ ] Add logs when it takes a long time to load (maybe more than 15 seconds?)
• [ ] Fix

Thanks!

[fenix2222]

Checked getlatestrules query. The main performance hit happens when user is not logged in because username is not passed in for filtering:

var filteredRules = rules
            .Where(r => string.IsNullOrEmpty(githubUsername) || r.GitHubUsername == githubUsername ||
                        r.CreatedBy == githubUsername || r.UpdatedBy == githubUsername)
            .GroupBy(r => r.RuleGuid)
            .Select(group => group.First())
            .DistinctBy(r => r.RuleGuid)
            .OrderByDescending(r => r.UpdatedAt)
            .Skip(skip)
            .Take(take);

Because of the ordering/paging combination, it has to get the entire dataset before performing skip and take. This makes it slow, subsequent runs are faster as query plan is optimised. Some possible improvements:

1. Add index on UpdatedAt column so that sorting is much faster
2. We could rewrite this query using common table expressions