SQLite PCL database insertion taking too much time

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

Answers


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.


Need Your Help

why am I getting one line of numbers?

python

num = int(input('Enter a number:'))

graph conversion - what is the name of it?

graph terminology graph-theory transformation

I have a graph and here's a part of it (nodes are located at the intersections of edges; the outer rectangle is not part of the graph):

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.