Windows Phone 8.1 (RT) app with SQLite Db which contains 67k elements. Works slowly

Question

I developed an application for the "Windows Phone 8.1", there I am working with a database comprising 4700 items. On the "Windows Store App 8.1/10 (Desktop/Tablet)" app is works fast, but "Windows Phone 8.1" can be seen that the "UI" works slowly and lagging. Here the analysis of the performance profiler.

When application is starting the method "OnLaunching" I load the data from the sqlite repository to the static property "IEnumerable" in a static class.

await Task.Run(async () =>
    {
        if (InstanceMessageService.AllHolidays == null)
            {
                InstanceMessageService.AllHolidays = _repository.GetAll();
                InstanceMessageService.AllCountries = await Task.Run(() =>
                    InstanceMessageService.AllHolidays.GroupBy(holiday => holiday.Country)
                        .Select(x => x.First().Country)
                        .Select(s => new Country()
                        {
                            Title = s,
                            FirstLetter = s[0].ToString()
                        }).ToList()
                    );

                    if (!InstanceMessageService.AllHolidays.Any() || !InstanceMessageService.AllCountries.Any())
                    {
                        throw new Exception("Cannot load SQLite data"); 
                    }
                }
            });

Here InstanceMessageService

public static class InstanceMessageService
{
    public static IEnumerable<Holiday> AllHolidays { get; set; } 
    public static IEnumerable<Country> AllCountries { get; set; }
    ...
}

Now 4700 holiday items contains in static properties (in memory) and 171 items of countries too. It's is not good way I think.

This is my ViewModel code for CalendarsPage. CalendarsPage provides holidays data for Today, Tomorrow or concrete date selected from the calendar control.

public class CalendarPageViewModel : INotifyPropertyChanged
    
        public IEnumerable<Holiday> Holidays
        {
            get { return _holidays; }
            set
            {
                _holidays = value;
                OnPropertyChanged("Holidays");
            }
        }

        public ObservableCollection<Holiday> TodayHolidays
        {
            get { return _todayHolidays; }
            set
            {
                _todayHolidays = value;
                OnPropertyChanged("TodayHolidays");
            }
        }

        public ObservableCollection<Holiday> TomorrowHolidays
        {
            get { return _tomorrowHolidays; }
            set
            {
                _tomorrowHolidays = value;
                OnPropertyChanged("TomorrowHolidays");
            }
        }

        public ObservableCollection<Holiday> SelectedDateHoliday
        {
            get { return _selectedDateHoliday; }
            set
            {
                _selectedDateHoliday = value;
                OnPropertyChanged("SelectedDateHoliday");
            }
        }

        public CalendarPageViewModel()
        {
            Task task = InitCollections();
            InitCommands();
        }
     
       **// Slowly magic here**

        private async Task InitCollections()
        {
            Holidays = InstanceMessageService.AllHolidays;
            TomorrowHolidays = new ObservableCollection<Holiday>(await Task.Run(() => Holidays.Where(holiday => holiday.Date == DateTime.Now.AddDays(1).Date.ToString("MMM d")).Select(holiday => holiday)));
            TodayHolidays = new ObservableCollection<Holiday>(await Task.Run(() => Holidays.Where(holiday => holiday.Date == DateTime.Now.Date.ToString("MMM d")).Select(holiday => holiday)));
        }

        public event PropertyChangedEventHandler PropertyChanged;

        [NotifyPropertyChangedInvocator]
        protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
        {
            if (PropertyChanged != null) PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
    }

Repository class code

public class HolidaysRepository : IRepository<Holiday>
    {
        private readonly IDatabaseConnection _databaseConnection;
        public HolidaysRepository(IDatabaseConnection databaseConnection)
        {
            _databaseConnection = databaseConnection;
        }

        public List<Holiday> GetAll()
        {
            return _databaseConnection.Connection.Table<Holiday>().ToList();
        }

        public void AddOrUpdate(Holiday entity)
        {
            if (_databaseConnection.Connection.Table<Holiday>().FirstOrDefault(item => item.Id == entity.Id) !=
                null)
            {
                Update(entity);
            }
            else
            {
                Add(entity);
            }
        }

        public void Add(Holiday entity)
        {
            _databaseConnection.Connection.Insert(entity);
        }

        public void Update(Holiday entity)
        {
            _databaseConnection.Connection.Update(entity);
        }

        public void Delete(Holiday entity)
        {
            _databaseConnection.Connection.Delete(entity);
        }

        public void Clean()
        {
            _databaseConnection.Connection.DeleteAll<Holiday>();
        }
    }

And it may be important, in the application I use "Ninject" as "ServiceLocator".

How can I increase the speed of the application? How to reduce the amount of RAM used? If 4700 elements makes the application slowly and UI works slowly, then 67,000 items will not have enough RAM i think.

Answer 1

When loading lots of data takes more time than you'd like, the simplest solution is normally to load less data.

Do you really need to load all the holidays in the database?
Can you not just load the specific day(s) you need? For instance, just load any holidays for today and tomorrow by default and then lookup any specific other days the user selects.

If you don't need all the holidays but really must have ALL the countries loaded then a separate stored list of these would probably be faster than loading all the holidays just to extract the countries.

When querying data in a database you should make sure that you have appropriate indexes to make the lookups fast.

It may also be worth looking at the indexes you have on the data so you can search it from the DB directly and not load it all into a List which you presumably then search.