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?

Why is personid in your file table of type numeric while the pk in the person table is integer? — rene, Dec 18 '16 at 12:33
@rene those sql were generated by sqlitemanager plus that doesn't really matter cause sqlite is a data type less kind of database — Khashayar Nourian, Dec 18 '16 at 12:38
@mybirthname already tried that but did not improve the performance — Khashayar Nourian, Dec 18 '16 at 12:39
maybe this has more tips: http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite?rq=1 I would try to create and dispose the connection per person, so don't reuse for the next transaction. And that `form.Progress` call doesn't do much magic I hope? — rene, Dec 18 '16 at 12:53
@rene form.progress() just update a progress bar in my windows form — Khashayar Nourian, Dec 18 '16 at 13:12
@rene tried to do this per person not for whole batch but result were much worse — Khashayar Nourian, Dec 18 '16 at 13:13
@CL. great help with changing my connection string to this` using (SQLiteConnection sqlconnection = new SQLiteConnection("Data Source=" + DbPath + ";Version=3; Synchronous=off;Journal Mode = MEMORY;"))` and wrapping all my insert into single transaction ive succeeded to do 3700 insert in 1 sec — Khashayar Nourian, Dec 18 '16 at 14:08
These changes to the connection string might destroy your data. — CL., Dec 18 '16 at 14:09
@CL. with removing those two option those inserts took 2 second — Khashayar Nourian, Dec 18 '16 at 14:12
If you do not care whether the data is written correctly, not writing it at all is even faster. — CL., Dec 18 '16 at 14:18
@CL. good point and thanks for your help should i post an answer to this question? — Khashayar Nourian, Dec 18 '16 at 14:23

score 0 · Accepted Answer · answered May 07 '17 at 11:23

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;
        }

improve sqlite.net performance at insert

1 Answers1