InsertWithChildren Very Slow

[Eli Black]

Hello,

I noticed that using InsertWithChildren can be very slow when inserting quite a few records. The current project that I'm working on has one parent record with around 40 child records, and calling InsertWithChildren() on the parent takes around four seconds.

I'm new to this codebase, so I could be way off base here, but I found some information here that suggests that this could be caused by each insert being done inside a transaction. If this is what the code is currently doing, do you think it would be okay to make it so that calling InsertWithChildren() does the parent and child inserts inside a single transaction?

[Guillermo Gutiérrez]

That's a lot of time. Can you post some code reproducing the issue?

[Lukas G]

I have the same issue when I insert an "Organization" object into the database with the following function:

await DBAsyncConnection().InsertOrReplaceWithChildrenAsync(organization, recursive: true);

Each organization has several "User" and several "Channel" objects. Therefore, an Organization can contain several hundred child objects. If I use the above function, it takes very long (a few seconds) to finish and therefore blocking other tasks, for example reading from the database.

I assume it's because of this implementation of the functionality (found in the soruce code), which inserts each object individually?

static void InsertChildrenRecursive(this SQLiteConnection conn, object element, bool replace, bool recursive, ISet<object> objectCache = null) {
            if (element == null)
                return;

            objectCache = objectCache ?? new HashSet<object>();
            foreach (var relationshipProperty in element.GetType().GetRelationshipProperties())
            {
                var relationshipAttribute = relationshipProperty.GetAttribute<RelationshipAttribute>();

                // Ignore read-only attributes and process only 'CascadeInsert' attributes
                if (relationshipAttribute.ReadOnly || !relationshipAttribute.IsCascadeInsert)
                    continue;

                var value = relationshipProperty.GetValue(element, null);
                conn.InsertValue(value, replace, recursive, objectCache);
            }
        }

If I do separate inserts (without children) of Organization, Users and Channels it finishes in a blast. Is there any workaround / approach I can use to improve the performance with the InsertOrReplaceWithChildrenAsync call?

Thank you! :)

[Guillermo Gutiérrez]

@LukeG47 , can you post your model declaration and some code reproducing the issue? Have you tried enabling ReadOnly property or disabling CascadeInsert on inverse relationships?

[Lukas G]

Hey Guillermo,

the following is pretty long. so thank you for taking your time. If you see any errors / optimization potential, please let me know. I'm pretty new to this stuff.

Have a great day!

Model declaration:

 [Table("Organization")]
    public class Organization
    {
        [JsonProperty("id")]
        [PrimaryKey]
        public int id { get; set; }

        [JsonProperty("name")]
        public string name { get; set; }
        [JsonProperty("role")]
        public int role { get; set; }
        [JsonProperty("logo")]
        public string logo { get; set; }
        [JsonProperty("subdomain")]
        public string subdomain { get; set; }
        [JsonProperty("inviter_role")]
        public int inviter_role { get; set; }
        [JsonProperty("has_integrations")]
        public bool has_integrations { get; set; }

        [OneToMany(CascadeOperations = CascadeOperation.All)]
        [JsonProperty("channels")]
        public List<Channel> channels { get; set; }

        // Several organizations can have several users
        [ManyToMany(typeof(OrganizationUserRelationship), CascadeOperations = CascadeOperation.All)]
        [JsonProperty("users")]
        public List<User> users { get; set; }

        [OneToOne(CascadeOperations = CascadeOperation.All)]
        [JsonProperty("stats")]
        public OrganizationStats stats { get; set; }

        [JsonProperty("title")]
        public string title { get; set; }
    }

    [Table("OrganizationStats")]
    public class OrganizationStats
    {
        [PrimaryKey, AutoIncrement]
        public int id { get; set; }

        [ForeignKey(typeof(Organization))]
        public int organizationId { get; set; }
        [OneToOne(CascadeOperations = CascadeOperation.CascadeRead)]
        public Organization organization { get; set; }

        [JsonProperty("unreadCount")]
        public int unreadCount { get; set; }
        [JsonProperty("channelCount")]
        public int channelCount { get; set; }
        [JsonProperty("userCount")]
        public int userCount { get; set; }
    }

[Table("Channel")]
    public class Channel
    {

        [PrimaryKey]
        [JsonProperty("id")]
        public int id { get; set; }

        [ForeignKey(typeof(Organization))]
        public int organizationId { get; set; }
        [ManyToOne(CascadeOperations = CascadeOperation.CascadeRead)]
        public Organization organization { get; set; }

        // Serialized users to store them to the database, because lists can not stored otherwise
        // Ignore the list in SQLite
        [Ignore]
        [JsonProperty("users")]
        public List<int> users
        {
            get
            {
                if (usersBlobbed != null)
                {
                    return JsonConvert.DeserializeObject<List<int>>(usersBlobbed);
                }
                else
                {
                    return null;
                }
            }
            set
            {
                usersBlobbed = JsonConvert.SerializeObject(value);
            }
        }

        // Store this instead
        public string usersBlobbed { get; set; }

        [JsonProperty("pin")]
        public int? pin { get; set; }
        [JsonProperty("creator")]
        public int creator { get; set; }
        [JsonProperty("first_message_time")]
        public double? first_message_time { get; set; }
        [JsonProperty("abbr")]
        public string abbr { get; set; }
        [JsonProperty("mentioned")]
        public int mentioned { get; set; }
        [JsonProperty("is_public")]
        public bool is_public { get; set; }
        [JsonProperty("latest_message_time")]
        public double? latest_message_time { get; set; }
        [JsonProperty("name")]
        public string name { get; set; }
        [JsonProperty("created")]
        public int created { get; set; }
        [JsonProperty("description")]
        public string description { get; set; }
        [JsonProperty("slug")]
        public string slug { get; set; }
        [JsonProperty("color")]
        public string color { get; set; }
        [JsonProperty("unread")]
        public int unread { get; set; }

        [JsonProperty("type")]
        public string type { get; set; }

        // Serialized invited users to store them to the database, because lists can not stored otherwise
        // Ignore the list in SQLite
        [Ignore]
        [JsonProperty("invited")]
        public List<string> invited
        {
            get
            {
                return JsonConvert.DeserializeObject<List<string>>(invitedUsersBlobbed);
            }
            set
            {
                invitedUsersBlobbed = JsonConvert.SerializeObject(value);
            }
        }
        // Store this instead
        public string invitedUsersBlobbed { get; set; }

        public string icon { get; set; } 
    }

[Table("User")]
    public class User
    {
        [PrimaryKey]
        public int id { get; set; }

        // Several users can be part of many organizations
        [ManyToMany(typeof(OrganizationUserRelationship), CascadeOperations = CascadeOperation.All)]
        public List<Organization> organizationList { get; set; }

        [JsonProperty("username")]
        public string username { get; set; }
        [JsonProperty("phone_number")]
        public string phone_number { get; set; }
        [JsonProperty("what_i_do")]
        public string what_i_do { get; set; }
        [JsonProperty("active")]
        public bool active { get; set; }
        [JsonProperty("status")]
        public int status { get; set; }
        [JsonProperty("displayName")]
        public string displayName { get; set; }
        [JsonProperty("firstName")]
        public string firstName { get; set; }
        [JsonProperty("title")]
        public string title { get; set; }
        [JsonProperty("lastName")]
        public string lastName { get; set; }
        [JsonProperty("role")]
        public int role { get; set; }
        [JsonProperty("avatar")]
        public string avatar { get; set; }
        [JsonProperty("skype_username")]
        public string skype_username { get; set; }
        [JsonProperty("is_only_invited")]
        public bool is_only_invited { get; set; }
        [JsonProperty("email")]
        public string email { get; set; }
        [JsonProperty("pm")]
        public int? pm { get; set; }
    }

    // Because we have a many-to-many relationship between users and organizations,
    // we need to use this OrganizationUserRelationship intermediate entity
    [Table("OrganizationUserRelationship")]
    public class OrganizationUserRelationship
    {
        [ForeignKey(typeof(Organization))]
        public int organizationId { get; set; }

        [ForeignKey(typeof(User))]
        public int userId { get; set; }
    }

Maybe the problem lies with this relation?

 // Several users can be part of many organizations
        [ManyToMany(typeof(OrganizationUserRelationship), CascadeOperations = CascadeOperation.All)]
        public List<Organization> organizationList { get; set; }

To reproduce: I have an Organization object, which contains an array of Users and an array of Channels (Organization object was created by parsing a JSON response from the web backend).

I then save it with the DatabaseHelperClass:

public class DatabaseHelperClass
    {
        public static string DB_PATH = Path.Combine(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite")); //DataBase Name

        private SQLiteConnectionWithLock _lockConn;
        public SQLiteAsyncConnection sqliteAsyncConnection;

        public SQLiteConnectionWithLock LockConn()
        {
            if (_lockConn == null)
            {
                // TODO: Look into WAL mode for database, which should enable reads (several) + writes (a single one) at the same time
                _lockConn = new SQLiteConnectionWithLock(new SQLitePlatformWinRT(), new SQLiteConnectionString(DB_PATH, storeDateTimeAsTicks: false));
                _lockConn.BusyTimeout = TimeSpan.FromSeconds(20);
            }

            return _lockConn;
        }

        private SQLiteAsyncConnection DBAsyncConnection()
        {
            if (sqliteAsyncConnection == null)
            {
                sqliteAsyncConnection = new SQLiteAsyncConnection(LockConn);
            }

            return sqliteAsyncConnection;
        }

        public async void InsertAsync(Organization organization)
        {
            await DBAsyncConnection().InsertOrReplaceWithChildrenAsync(organization, recursive: true);

        }
}

[Martin Kuckert]

Does wrapping the `InsertOrReplaceWithChildrenAsync in a transaction boost things up?

[Dmitry Ponomarenko]

It does. Wrapping InsertAll with RunInTransactionAsync() reduce execution time dramatically. Should it be implemented inside extensions library or at least be mentioned in documentation?