Inserting Data from SQL Server to Sqlite

Question

I want to move data from SQL Server to sqlite. What I do is first move the data from SQL Server to a dataset, and then move from there to the sqlite table.

The following is the code that does that. I believe there may be more efficient way

try
{
    using (SqlConnection sqlConnection = new SqlConnection(DataSources.RemoteConnectionString()))
    {
        sqlConnection.Open();

        using (SqlCommand sqlCommand = new SqlCommand("[DB7934_businessmind].[DownloadAreaOfLaw]", sqlConnection))
        {
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@AoLId", aolid);

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            sqlDataAdapter.Fill(aolDataSet.TempAoL);

            if (aolDataSet.TempAoL.Rows.Count > 0)
            {
                using (SQLiteConnection sqliteConnection = new SQLiteConnection(DataSources.LocalConnectionString()))
                {
                    sqliteConnection.Open();

                    using (SQLiteCommand sqliteCommand = new SQLiteCommand(sqliteConnection))
                    {
                        foreach (Models.AoLDataSet.TempAoLRow r in aolDataSet.TempAoL.Rows)
                        {
                            sqliteCommand.CommandText = "INSERT INTO AreaOfLaw(AoLId, AreaOfLawTitle) VALUES(@AoLId, @AreaOfLawText)";
                            sqliteCommand.Parameters.AddWithValue("@AoLId", r.AoLId);
                            sqliteCommand.Parameters.AddWithValue("AreaOfLawText", r.AreaOfLawTitle);
                            sqliteCommand.ExecuteNonQuery();
                        }

                        sqliteCommand.Dispose();
                    }

                    sqliteConnection.Close();
                }
            }
        }

        sqlConnection.Close();
    }
}
catch (Exception ex)
{
    MessageBox.Show("An error has occurred while downloading Areas Of Law from the cloud, the original error is: " + ex.Message, "Area Of Law", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Thank you in advance

There are about 2000 records to download. It takes some few seconds to copy from the sql server database to dataset but takes abut 20 minutes to move them from the dataset to the sqlite table — Bainn, Dec 20 '17 at 11:22
does the tables with the same structure ? what is the stored Procedure code? — Maher Khalil, Dec 20 '17 at 11:23
Yes, the tables have the same structure. Below is the stored procedure in sql server that selects the data that is downloaded to the dataset. Where can I add the code again? — Bainn, Dec 20 '17 at 11:40
It's a simple select statement which is as follows: SELECT AoLId, AreaOfLawTitle From AreaofLaw WHERE AoLId > @AoLId — Bainn, Dec 20 '17 at 11:46
SQLite can be slow but not *that* slow. How do you connect to it? What is the SQLite connection string? Are you using journaling, flushing? Is the file used by other processes? — Panagiotis Kanavos, Dec 20 '17 at 12:08
Are you using WAL mode? Have you entered `PRAGMA journal_mode=WAL;` in the connection string? — Panagiotis Kanavos, Dec 20 '17 at 12:43

cookieMonster · Answer 1 · 2017-12-20T13:03:56.673

Try wrapping all inserts into one transaction:

try
{
    using (SqlConnection sqlConnection = new SqlConnection(DataSources.RemoteConnectionString()))
    {
        sqlConnection.Open();
        using (SqlCommand sqlCommand = new SqlCommand("[DB7934_businessmind].[DownloadAreaOfLaw]", sqlConnection))
        {
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@AoLId", aolid);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            sqlDataAdapter.Fill(aolDataSet.TempAoL);
            if (aolDataSet.TempAoL.Rows.Count > 0)
            {
                using (SQLiteConnection sqliteConnection = new SQLiteConnection(DataSources.LocalConnectionString()))
                {
                    sqliteConnection.Open();

                    using(SqliteTransaction tr = sqliteConnection.BeginTransaction())
                    {
                        using (SQLiteCommand sqliteCommand = new SQLiteCommand(sqliteConnection))
                        {
                            foreach (Models.AoLDataSet.TempAoLRow r in aolDataSet.TempAoL.Rows)
                            {
                                sqliteCommand.CommandText = "INSERT INTO AreaOfLaw(AoLId, AreaOfLawTitle) VALUES(@AoLId, @AreaOfLawText)";
                                sqliteCommand.Parameters.AddWithValue("@AoLId", r.AoLId);
                                sqliteCommand.Parameters.AddWithValue("AreaOfLawText", r.AreaOfLawTitle);
                                sqliteCommand.ExecuteNonQuery();
                            }
                            sqliteCommand.Dispose();
                        }

                        tr.Commit();
                    }
                    sqliteConnection.Close();
                }
            }
        }
        sqlConnection.Close();
    }
}
catch (Exception ex)
{
    MessageBox.Show("An error has occurred while downloading Areas Of Law from the cloud, the original error is: " + ex.Message, "Area Of Law", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

I've came across the same issue with insertion speed using sqlite and this technique solved the problem - insertion speed increased tremendously. I don't know if there are any other options available to beat the problem if this one doesn't fit.

Why would that improve performance instead of *cause* a delay? — Panagiotis Kanavos, Dec 20 '17 at 12:16
@Panagiotis Kanavos, because sqlite does inserts much faster under explicit transaction. MUCH faster. I've dealt with it myself and got into the same trouble - it worked way too slow, after wrapping it all up into transaction it became lightning fast. — cookieMonster, Dec 20 '17 at 12:36
Explain it in the answer then. I also suspect that the transaction improves performance when you use WAL. Even so, 20 minutes for 2000 records is far too slow. — Panagiotis Kanavos, Dec 20 '17 at 12:40
I want to thank all of you who have contributed in solving this problem. The solution was what my good friend offered. Wrapping the inserts with SQLiteTransaction. It worked like magic. over 10,000 records in less than 10 seconds. I mean less than 10 seconds. It took over 30 solid minutes to do this same number. Folks, my profound thanks to you all. — Bainn, Dec 20 '17 at 15:16

Inserting Data from SQL Server to Sqlite

1 Answers1

Linked