SQLite PCL database insertion taking too much time

Question

    namespace App3
    {

        public sealed partial class App : Application
        {
            private TransitionCollection transitions;

            public App()
            {
                this.InitializeComponent();
                this.Suspending += this.OnSuspending;

            }


            public static async Task ReadFile(String file1, String table1)
            {
                using (var connection = new SQLiteConnection("Storage.db"))
                {
                    string a;
                    var file = await StorageFile.GetFileFromApplicationUriAsync(new Uri(file1));
                    var stream = await file.OpenStreamForReadAsync();
                    using (StreamReader sr = new StreamReader(stream))
                    {
                        while (sr.Peek() >= 0)
                        {
                            a = sr.ReadLine();
                            string[] l = a.Split(',');
                            if (table1 == "commercial")
                            {
                                using (var statement = connection.Prepare(@"INSERT INTO " + table1 + " VALUES (\'" + l[0] + "\',\'" + l[1] + "\',\'" + l[2] + "\',\"" + l[3] + "\",\"" + l[4] + "\",\'" + l[5] + "\',\'" + l[6] + "\',\'"
                                    + l[7] + "\',\'" + l[8] + "\',\'" + l[9] + "\',\'" + l[10] + "\'); ON DUPLICATE UPDATE ID=ID"))
                                {
                                    statement.Step();
                                    statement.Reset();
                                    statement.ClearBindings();                   
                                }
                            }
                            else
                            {
                                using (var statement = connection.Prepare(@"INSERT INTO " + table1 + " VALUES (\'" + l[0] + "\',\'" + l[1] + "\',\'" + l[2] + "\',\"" + l[3] + "\",\"" + l[4] + "\",\'" + l[5] + "\',\'" + l[6] + "\',\'"
                                    + l[7] + "\',\'" + l[8] + "\',\'" + l[9] + "\'); ON DUPLICATE UPDATE ID=ID"))
                                {
                                    // Inserts data.
                                    statement.Step();
                                    statement.Reset();
                                    statement.ClearBindings();
                                }
                            }
                        }
                    }
                }
            }


            public void createdb(String table){
                using (var connection = new SQLiteConnection("Storage.db"))
                {


  using (var statement1 = connection.Prepare(@"DROP TABLE IF EXISTS " + table+";"))
            {
                statement1.Step();
                statement1.Reset();
                statement1.ClearBindings();
            }
                    using (var statement = connection.Prepare(@"
                                                   CREATE TABLE IF NOT EXISTS "+table+" (id INT NOT NULL PRIMARY KEY,"
                                                      + "TYPE VARCHAR(255),"
                                                      +"MAKE VARCHAR(255),"
                                                      +"MODEL VARCHAR(255),"
                                                      +"Fitment VARCHAR(255),"
                                                      +"Part VARCHAR(255),"
                                                      +"NRB_No VARCHAR(255),"
                                                      +"Dimension VARCHAR(255),"
                                                      +"No_Off INT,"
                                                      +"Company VARCHAR(255)"
                                                    +");"))
                    {
                        statement.Step();

                    }
                }
            }


            protected async override void OnLaunched(LaunchActivatedEventArgs e)
            {

                createdb("Moped");
                await ReadFile("ms-appx:///Assets/Mopeds.txt", "Moped");

                createdb("Scooter");
                await ReadFile("ms-appx:///Assets/Scooters.txt", "Scooter");

                createdb("Motorcycle");
                await ReadFile("ms-appx:///Assets/Motorcycles.txt", "Motorcycle");

                createdb("Auto");
                await ReadFile("ms-appx:///Assets/3W.txt", "Auto");

                createdb("Passenger");
                await ReadFile("ms-appx:///Assets/cars.txt", "Passenger");

                createdb("Muv");
                await ReadFile("ms-appx:///Assets/Muv.txt", "Muv");

                createdb("Commercial");
                await ReadFile("ms-appx:///Assets/Commercial.txt", "Commercial");

                createdb("Tractor");
                await ReadFile("ms-appx:///Assets/Tractors.txt", "Tractor");

  }

This is my App.xaml.cs i am adding the tables in the onlaunched method. The table is populated using the text files present in the local folder. But it is taking too much time to insert. Each insertion has around 300 rows and 9 entries but it is taking time. Is there a way to speed up? and am I doing correct or is there any other method to play with the database.I am using SQLITE PCL and this is for Windows 8.1 RT

EDIT

public static async Task ReadFile(String file1, String table1)
        {
            using (var connection = new SQLiteConnection("Storage.db"))
            {
                string a;
                var file = await StorageFile.GetFileFromApplicationUriAsync(new Uri(file1));
                var stream = await file.OpenStreamForReadAsync();
                using (StreamReader sr = new StreamReader(stream))
                {
                    while (sr.Peek() >= 0)
                    {
                        a = sr.ReadLine();
                        string[] l = a.Split(',');
                        try
                        {
                            using (var tr = connection.BeginTransaction())
                            {
                                using (SQLiteCommand cmd = connection.CreateCommand(@"DROP TABLE IF EXISTS " + table1 + ";"))
                                {
                                    cmd.ExecuteNonQuery();
                                }

                                if (table1 == "commercial")
                                    {        
                                        using (SQLiteCommand cmd1 = connection.CreateCommand(@"
                                               CREATE TABLE IF NOT EXISTS " + table1 + " (id INT NOT NULL PRIMARY KEY,"
                                                  + "TYPE VARCHAR(255),"
                                                  + "MAKE VARCHAR(255),"
                                                  + "MODEL VARCHAR(255),"
                                                  + "Fitment VARCHAR(255),"
                                                  + "Part VARCHAR(255),"
                                                  + "NRB_No VARCHAR(255),"
                                                  + "Dimension VARCHAR(255),"
                                                  + "No_Off INT,"
                                                  + "Company VARCHAR(255)"
                                                + ");"))
                                        {
                                            cmd1.ExecuteNonQuery();
                                        }

                                        using (SQLiteCommand cmd = connection.CreateCommand(@"INSERT INTO " + table1 + " VALUES (\'" + l[0] + "\',\'" + l[1] + "\',\'" + l[2] + "\',\"" + l[3] + "\",\"" + l[4] + "\",\'" + l[5] + "\',\'" + l[6] + "\',\'"
                                        + l[7] + "\',\'" + l[8] + "\',\'" + l[9] + "\',\'" + l[10] + "\'); ON DUPLICATE UPDATE ID=ID"))
                                        {
                                            cmd.ExecuteNonQuery();
                                        }
                                    }
                                    else
                                    {
                                        using (SQLiteCommand cmd1 = connection.CreateCommand(@"
                                               CREATE TABLE IF NOT EXISTS " + table1 + " (id INT NOT NULL PRIMARY KEY,"
                                                  + "TYPE VARCHAR(255),"
                                                  + "MAKE VARCHAR(255),"
                                                  + "MODEL VARCHAR(255),"
                                                  + "Fitment VARCHAR(255),"
                                                  + "Part VARCHAR(255),"
                                                  + "NRB_No VARCHAR(255),"
                                                  + "Dimension VARCHAR(255),"
                                                  + "No_Off INT,"
                                                  + "Company VARCHAR(255)"
                                                  + "Usage VARCHAR(255)"
                                                + ");"))
                                        {
                                            cmd1.ExecuteNonQuery();
                                        }
                                        using (SQLiteCommand cmd = connection.CreateCommand(@"INSERT INTO " + table1 + " VALUES (\'" + l[0] + "\',\'" + l[1] + "\',\'" + l[2] + "\',\"" + l[3] + "\",\"" + l[4] + "\",\'" + l[5] + "\',\'" + l[6] + "\',\'"
                                            + l[7] + "\',\'" + l[8] + "\',\'" + l[9] + "\'); ON DUPLICATE UPDATE ID=ID"))
                                        {
                                            // Inserts data.
                                            cmd.ExecuteNonQuery();
                                        }
                                    }
                                    tr.Commit();
                            }
                        }
                            catch (SQLiteException ex)
                            {
                               tr.Rollback();
                            }
                        }
                    }
                }
            }

Next time please include only relevant code snippets. – VH-NZZ Jun 28 '14 at 23:11 — VH-NZZ, Jun 28 '14 at 23:11

score 4 · Answer 1 · edited May 23 '17 at 11:57

4

Make sure to wrap your inserts inside an explicitly opened and committed transaction. Otherwise SQLite will wrap each statement inside its own transaction and that's killing the performance. HTH.

EDIT: Also, have a look at this general question regarding SQLite performance.

edited May 23 '17 at 11:57

Community

1
1

answered Jun 28 '14 at 23:10

VH-NZZ

5,248
4
31
47

Thanks, but how to do that ? – brk7777 Jun 28 '14 at 23:12
But I am using SQLite PCL and it doesnot support Open(), BeginTransaction() CreateCommand(), this is the problem in windows 8.1 RT we don't have sqlite so had to use sqlite pcl – brk7777 Jun 28 '14 at 23:25
1

Have you seen https://github.com/oysteinkrog/SQLite.Net-PCL/blob/master/tests/TransactionTest.cs ? – VH-NZZ Jun 28 '14 at 23:28
I have edited as per your suggestion and i have used sqlite now. but getting error with using the error is : Unable to convert implicitly to System.IDisposable when using “Using”. And also error with tr : cannot assign void to an implicitly-typed local variable. Can you please resolve these. – brk7777 Jun 29 '14 at 00:06
The first means that the object you're trying to use in the `using` statement doesn't implement the `IDisposable` interface, which is needing for the `using` statement. The second is trying to assign a method that doesn't return anything (a void method) to a variable. My guess is that you're using the wrong methods. – Christopher Stevenson Jun 29 '14 at 01:18
@brk7777 Are you referring to `using (var connection = new SQLiteConnection("Storage.db"))` ? – VH-NZZ Jun 29 '14 at 18:06
no this one using (SQLiteCommand cmd1 = connection.CreateCommand(..)) – brk7777 Jun 29 '14 at 21:58
@brk7777 Then.. how about _not_ wrapping the `ExecuteNonQuery` call in a `using{...}` block? – VH-NZZ Jun 30 '14 at 07:18

score 3 · Answer 2 · answered Nov 08 '17 at 09:45

I know this is pretty much late but i was also facing the same issue in SQLite.Net-PCL Library. I was inserting the records one by one under transaction that's why it was taking 3-4 minutes to insert 7k records. Now i am using InsertAll function and it has come down to 10 seconds at max. This is my code:

_connection.BeginTransaction();
                    var listPeople = new List<Table_People>();
                    foreach (var item in peopleList.People)
                    {
                        var peopleTable = new Table_People();
                        peopleTable.AccountNumber = item.AccountNumber;
                        peopleTable.FirstName = item.FirstName;
                        peopleTable.LastName = item.LastName;
                        peopleTable.MiddleName = item.MiddleName;
                        peopleTable.PersonID = item.ID;
                        listPeople.Add(peopleTable);
                        //_peopleManager.SaveTask(peopleTable);
                    }
                    _connection.InsertAll(listPeople);
                    _connection.Commit();

Happy coding.

SQLite PCL database insertion taking too much time

2 Answers2