CODEWITHSUNDEEP
c#multithreadingsqlite
PNG
PNG

Reputation: 113

Performance issue to Insert record in SQLite database using Multithreading

I have 10000 records to insert in SQLite database Using Multithreading.

private void btnThread_Click(object sender, EventArgs e)
        {
                Thread th1 = new Thread(new ThreadStart(Save1));
                Thread th2 = new Thread(new ThreadStart(Save2));
                Thread th3 = new Thread(new ThreadStart(Save3));
                Thread th4 = new Thread(new ThreadStart(Save4));
                Thread th5 = new Thread(new ThreadStart(Save5));

                Thread th6 = new Thread(new ThreadStart(Save6));
                Thread th7 = new Thread(new ThreadStart(Save7));
                Thread th8 = new Thread(new ThreadStart(Save8));
                Thread th9 = new Thread(new ThreadStart(Save9));
                Thread th10 = new Thread(new ThreadStart(Save10));

                th1.Start();
                th2.Start();
                th3.Start();
                th4.Start();
                th5.Start();

                th6.Start();
                th7.Start();
                th8.Start();
                th9.Start();
                th10.Start();
      }

At above code In each thread call a function to save record like below

private void Save1()
        {
            for(int i = 0; i < 1000; i++)
            {

                using(SQLiteConnection sqliteConn = new SQLiteConnection("Data Source='" + dbPath + "'"))
                {
                    sqliteConn.Open();
                    string date = DateTime.Now.ToString();

                    string sqlInsert = "insert into PatientDetail (Name, Age, Date, PhoneNumber, Email, PatientSex, Status ) values ('Patient Name1', 35,'" + date + "','9856235674','[email protected]','M',1)";
                    SQLiteCommand command = new SQLiteCommand(sqlInsert, sqliteConn);
                    command.ExecuteNonQuery();
                    sqliteConn.Close();
                }
            }
        }

At above logic record inserted in database properly but it was take >=25 minute to insert 10000 records.

When I checked around 300 to 400 record insert in one minute.

I also use Transaction to insert record but no performance improve

Is there any way by which I can able to improve performance ?

How SQLite work internally in Multithreading?

Upvotes: 2

Views: 520

Answers (1)

Jesper
Jesper

Reputation: 7615

This article on SQLite.org describes SQLite's thread safety mechanisms and modes. By default, SQLite makes sure to serialize all operations (serialize as in not do more than one thing at once, not as in serializing objects), so it is thread safe, but this mode can be disengaged.

Inserting 1000 records is best done within a single transaction, and even if you don't do it within a single transaction, it will probably be faster by not opening a new connection each time. But if your code attempts to simulate the performance of inserting 1000 records one by one from separate pieces of code and not batching them, the performance you get in your test is representative of that.

Having ten threads compete to do this insertion is part of the problem but not the whole problem. If you have 10 threads each inserting 1000 inserts one by one, that's 10000 operations that SQLite has to serialize. Introducing some kind of batching would solve that, and would also let you use transactions which would be a huge performance boost on its own.

Upvotes: 1

Related Questions