improve sqlite.net performance at insert

Question

i have number of record which i want to store in db.
the schema of database is like this
a person table like this :

CREATE TABLE IF NOT EXISTS Person (id INTEGER PRIMARY KEY, name TEXT)

and a file table like this:

CREATE TABLE IF NOT EXISTS File (id INTEGER PRIMARY KEY, FileName TEXT,FilePath TEXT, PersonID NUMERIC CONSTRAINT person_file REFERENCES [Person]([id])ON DELETE NO ACTION ON UPDATE NO ACTION)

i use this function to do insert in both tables

for a 3700 record this code took 140 seconds on my laptop ,

public long BuildDB(List<DB.Person> persons, List<DB.File> Files, FrmTrain form)
        {
            long result = -1;
            try
            {

                long personID = 0;
                using (SQLiteConnection sqlconnection = new SQLiteConnection("Data Source=" + DbPath + ";Version=3;"))
                {
                    sqlconnection.Open();
                    SQLiteCommand PersonCommand = sqlconnection.CreateCommand();
                    SQLiteParameter personParam = new SQLiteParameter();
                    PersonCommand.CommandText = "INSERT INTO Person('Name') VALUES(?)";
                    PersonCommand.Parameters.Add(personParam);


                    SQLiteCommand FileCommand = sqlconnection.CreateCommand();
                    SQLiteParameter FileParam1 = new SQLiteParameter("@filename");
                    SQLiteParameter FileParam2 = new SQLiteParameter("@filepath");
                    SQLiteParameter FileParam3 = new SQLiteParameter("@personid");

                    FileCommand.CommandText = "INSERT INTO file(FileName,FilePath,PersonID) VALUES(@filename,@filepath,@personid)";
                    FileCommand.Parameters.Add(FileParam1);
                    FileCommand.Parameters.Add(FileParam2);
                    FileCommand.Parameters.Add(FileParam3);
                    for (int i = 0; i < persons.Count; i++)
                    {
                        using (SQLiteTransaction _SQLiteTransaction = sqlconnection.BeginTransaction())
                        {
                            personParam.Value = persons[i].Name;
                            PersonCommand.ExecuteNonQuery();
                            personID = sqlconnection.LastInsertRowId;

                            foreach (var item in Files.Where(f => f.PersonID == personID))
                            {
                                FileParam1.Value = item.FileName;
                                FileParam2.Value = item.FilePath;
                                FileParam3.Value = item.PersonID;
                                FileCommand.ExecuteNonQuery();

                            }
                            _SQLiteTransaction.Commit();
                        }
                        form.Progress();
                    }
                    sqlconnection.Close();
                }
                result = 1;

            }
            catch (Exception e)
            {
                result = 0;
                throw;
            }



            return result;

        }

first i wrote that code without transaction and it took about 500 second i tried to wrap the second ExecuteNonQuery in a transaction but not just improved performance it took 500 second too

is there anyway to improve the performance?

Answer 1

Try to use one SQLiteTransaction and only one commit for all inserted persons.

        try
        {

            long personID = 0;
            using (SQLiteConnection sqlconnection = new SQLiteConnection("Data Source=" + DbPath + ";Version=3;"))
            {
                sqlconnection.Open();
                SQLiteCommand PersonCommand = sqlconnection.CreateCommand();
                SQLiteParameter personParam = new SQLiteParameter();
                PersonCommand.CommandText = "INSERT INTO Person('Name') VALUES(?)";
                PersonCommand.Parameters.Add(personParam);


                SQLiteCommand FileCommand = sqlconnection.CreateCommand();
                SQLiteParameter FileParam1 = new SQLiteParameter("@filename");
                SQLiteParameter FileParam2 = new SQLiteParameter("@filepath");
                SQLiteParameter FileParam3 = new SQLiteParameter("@personid");

                FileCommand.CommandText = "INSERT INTO file(FileName,FilePath,PersonID) VALUES(@filename,@filepath,@personid)";
                FileCommand.Parameters.Add(FileParam1);
                FileCommand.Parameters.Add(FileParam2);
                FileCommand.Parameters.Add(FileParam3);
                using (SQLiteTransaction _SQLiteTransaction = sqlconnection.BeginTransaction())
                {
                    for (int i = 0; i < persons.Count; i++)
                    {
                        personParam.Value = persons[i].Name;
                        PersonCommand.ExecuteNonQuery();
                        personID = sqlconnection.LastInsertRowId;

                        foreach (var item in Files.Where(f => f.PersonID == personID))
                        {
                            FileParam1.Value = item.FileName;
                            FileParam2.Value = item.FilePath;
                            FileParam3.Value = item.PersonID;
                            FileCommand.ExecuteNonQuery();
                        }
                    }
                    _SQLiteTransaction.Commit();
                    form.Progress();
                }
                sqlconnection.Close();
            }
            result = 1;

        }
        catch (Exception e)
        {
            result = 0;
            throw;
        }