Performance issues when using Sqlite.Net in UWP

Question

I am using SQLite with SQLite.Net but every insertion is taking almost 15-20 secs. Though I have a complex table as given below.

namespace Models
{
    //SQLite Table AssessmentQuestions
    [Table("AssessmentQuestions")]
    public class AssessmentQuestion : INotifyPropertyChanged
    {
        //Autogenerate
        private int _id;
        [PrimaryKey, AutoIncrement]
        [JsonIgnore]
        public int Id
        {
            get
            {
                return _id;
            }
            set
            {
                this._id = value;
                OnPropertyChanged(nameof(Id));
            }
        }
        private int _month;
        public int Month
        {
            get
            {
                return _month;
            }
            set
            {
                this._month = value;
                OnPropertyChanged(nameof(_month));
            }
        }

        //Some other ids as varibles here too

        [OneToMany(CascadeOperations = CascadeOperation.All)]
        public List<Facility> QuestionFacilityModel { get; set; }

        public event PropertyChangedEventHandler PropertyChanged;
        private void OnPropertyChanged(string propertyName)
        {
            this.PropertyChanged?.Invoke(this,
                new PropertyChangedEventArgs(propertyName));
        }
    }

    //SQLite Table Facility
    [Table("Facilities")]
    public class Facility : INotifyPropertyChanged
    {
        //Autogenerate
        private int _id;
        [PrimaryKey, AutoIncrement]
        [JsonIgnore]
        public int Id
        {
            get
            {
                return _id;
            }
            set
            {
                this._id = value;
                OnPropertyChanged(nameof(Id));
            }
        }

        private int _assessmentScheduleId;
        public int AssessmentScheduleId
        {
            get
            {
                return _assessmentScheduleId;
            }
            set
            {
                this._assessmentScheduleId = value;
                OnPropertyChanged(nameof(_assessmentScheduleId));
            }
        }

        //Some other ids as varibles here too

        [ForeignKey(typeof(AssessmentQuestion))]
        [JsonIgnore]
        public int AssessmentQuestionId { get; set; }

        [ManyToOne(CascadeOperations = CascadeOperation.All)]
        [JsonIgnore]
        public AssessmentQuestion AssessmentQuestion { get; set; }

        [OneToMany(CascadeOperations = CascadeOperation.All)]
        public ObservableCollection<SubFacility> SubFacility { get; set; }

        public event PropertyChangedEventHandler PropertyChanged;
        private void OnPropertyChanged(string propertyName)
        {
            this.PropertyChanged?.Invoke(this,
                new PropertyChangedEventArgs(propertyName));
        }
    }

    //SQLite Table Sub Facility
    [Table("SubFacilities")]
    public class SubFacility : INotifyPropertyChanged
    {
        //Autogenerate
        private int _id;
        [PrimaryKey, AutoIncrement]
        [JsonIgnore]
        public int Id
        {
            get
            {
                return _id;
            }
            set
            {
                this._id = value;
                OnPropertyChanged(nameof(Id));
            }
        }

        //Some other ids as varibles here too

        [ForeignKey(typeof(Facility))]
        [JsonIgnore]
        public int FacilityId { get; set; }

        [ManyToOne(CascadeOperations = CascadeOperation.All)]
        [JsonIgnore]
        public Facility Facility { get; set; }

        [OneToMany(CascadeOperations = CascadeOperation.All)]
        public List<Questions> Questions { get; set; }

        public event PropertyChangedEventHandler PropertyChanged;
        private void OnPropertyChanged(string propertyName)
        {
            this.PropertyChanged?.Invoke(this,
                new PropertyChangedEventArgs(propertyName));
        }
    }

    //SQLite Table Questions
    [Table("Questions")]
    public class Questions : INotifyPropertyChanged
    {
        public Questions()
        {
            AssessmentImages = new string[0];
        }

        //Autogenerate
        private int _id;
        [PrimaryKey, AutoIncrement]
        [JsonIgnore]
        public int Id
        {
            get
            {
                return _id;
            }
            set
            {
                this._id = value;
                OnPropertyChanged(nameof(_id));
            }
        }
        private int _assessmentScheduleId;
        public int AssessmentScheduleId
        {
            get
            {
                return _assessmentScheduleId;
            }
            set
            {
                this._assessmentScheduleId = value;
                OnPropertyChanged(nameof(_assessmentScheduleId));
            }
        }
              //Some other ids as varibles here too

        [SQLite.Net.Attributes.Ignore]
        public virtual string[] AssessmentImages { get; set; }

        private bool _imageExists;
        public bool ImageExists
        {
            get
            {
                return _imageExists;
            }
            set
            {
                this._imageExists = value;
                OnPropertyChanged(nameof(_imageExists));
            }
        }

        [ForeignKey(typeof(SubFacility))]
        [JsonIgnore]
        public int SubFacilityId { get; set; }

        [ManyToOne(CascadeOperations = CascadeOperation.All)]
        [JsonIgnore]
        public SubFacility SubFacility { get; set; }

        [OneToMany(CascadeOperations = CascadeOperation.All)]
        public List<ControlTypes> ControlTypes { get; set; }

        public event PropertyChangedEventHandler PropertyChanged;
        private void OnPropertyChanged(string propertyName)
        {
            this.PropertyChanged?.Invoke(this,
                new PropertyChangedEventArgs(propertyName));
        }
    }

    //SQLite Table ControlTypes
    [Table("ControlTypess")]
    public class ControlTypes
    {
        //Autogenerate
        private int _id;
        [PrimaryKey AutoIncrement]
        [JsonIgnore]
        public int Id
        {
            get
            {
                return _id;
            }
            set
            {
                this._id = value;
                OnPropertyChanged(nameof(_id));
            }
        }
        private string _answerValue;
        public string AnswerValue
        {
            get
            {
                return _answerValue;
            }
            set
            {
                this._answerValue = value;
                OnPropertyChanged(nameof(_answerValue));
            }
        }
        private int _questionValueId;
        public int QuestionValueId
        {
            get
            {
                return _questionValueId;
            }
            set
            {
                this._questionValueId = value;
                OnPropertyChanged(nameof(_questionValueId));
            }
        }
        private string _questionValueName;
        public string QuestionValueName
        {
            get
            {
                return _questionValueName;
            }
            set
            {
                this._questionValueName = value;
                OnPropertyChanged(nameof(_questionValueName));
            }
        }
        private string _questionTypeId;
        public string QuestionTypeId
        {
            get
            {
                return _questionTypeId;
            }
            set
            {
                this._questionTypeId = value;
                OnPropertyChanged(nameof(_questionTypeId));
            }
        }
        private int _score;
        public int Score
        {
            get
            {
                return _score;
            }
            set
            {
                this._score = value;
                OnPropertyChanged(nameof(_score));
            }
        }
        private string _assessmentDetailId;
        public string AssessmentDetailId
        {
            get
            {
                return _assessmentDetailId;
            }
            set
            {
                this._assessmentDetailId = value;
                OnPropertyChanged(nameof(_assessmentDetailId));
            }
        }
        private string _assessmentAnswerId;
        public string AssessmentAnswerId
        {
            get
            {
                return _assessmentAnswerId;
            }
            set
            {
                this._assessmentAnswerId = value;
                OnPropertyChanged(nameof(_assessmentAnswerId));
            }
        }

        [ForeignKey(typeof(Questions))]
        [JsonIgnore]
        public int QuestionsId { get; set; }

        [ManyToOne(CascadeOperations = CascadeOperation.All)]
        [JsonIgnore]
        public Questions Questions { get; set; }

        public event PropertyChangedEventHandler PropertyChanged;
        private void OnPropertyChanged(string propertyName)
        {
            this.PropertyChanged?.Invoke(this,
                new PropertyChangedEventArgs(propertyName));
        }
    }

    public class StringOrArrayConverter : JsonConverter
    {
        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
        {
            switch (reader.TokenType)
            {
                case JsonToken.String:
                case JsonToken.Null:
                    return reader.Value;
                case JsonToken.StartArray:
                    reader.Read();
                    if (reader.TokenType != JsonToken.EndArray)
                        throw new JsonReaderException("Empty array expected.");
                    return "";
            }
            throw new JsonReaderException("Expected string or null or empty array.");
        }

        public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
        {
            //var item = (string[])value;
            //writer.WriteValue(item);
            serializer.Serialize(writer, value);
        }

        public override bool CanConvert(Type objectType)
        {
            return objectType == typeof(string);
        }
    }
}

Where my sql connection is

var platform = new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT();
var connectionWithLock = new SQLiteConnectionWithLock(platform, _connectionParameters);
var connection = new SQLiteAsyncConnection(() => connectionWithLock);
SQLite.Net.Async.SQLiteAsyncConnection database = connection;

And a insertion looks like

database.InsertWithChildrenAsync(assessmentQuestion,true).ConfigureAwait(false);

I am new to sqlite so I would like to know how is the performance so bad. I have also used SQLiteNetExtensionsAsync.Extensions. I have tried using without the async calls and had the same performance. Since I'm doing quite a few number of insertions 30-40 in a loop this is taking a lot of time. How can I improve this.

Also note one insertion can have multiple nested insertions since the object is complex but on an average one insertion is equivalent to 35-45 individual insertions of different child objects. And this nesting seems to be causing the problem.

Answer 1

Calling this method in a transaction should make it faster. It's a recommendation of SQLite itself : http://www.sqlite.org/faq.html#q19

Regards