vendredi 29 janvier 2016

Performance enhancement on insert large number of rows to Sqlite database using C#

I have object graph contain dictionary and dictionary contain large number of words about 165000 row needed to insert to sqlite 'Word' table and implementing as following code :

public class Dictionary
{
    public Dictionary()
    {
        Words = new List<Word>();
    }
    public int DictionaryId { get; set; }
    public string Name { get; set; }
    public string FromLanguage { get; set; }
    public string ToLanguage { get; set; }
    public int NoOfWords { get; set; }
    public virtual ICollection<Word> Words { get; set; }
}

public class Word
{
    public Word()
    {
        Definitions = new List<Definition>();
        Comments = new List<Comment>();
    }
    public int WordId { get; set; }
    public int DictionaryId { get; set; }
    public Dictionary Dictionary { get; set; }
    public string Text { get; set; }
}

and the following code to insert from words list to word table on database:

using (var transaction = m_dbConnection.BeginTransaction())
        {
            int count = 0;
            m_dbConnection.Open();
            foreach (Word entry in dictionaryInfo.Words)
            {

                string sql6 = @"insert into Word (DictionaryId,Text) 
            values (@DictionaryId, @Text)";
                SQLiteCommand command6 = new SQLiteCommand(sql6, m_dbConnection);
                command6.Parameters.Add("@DictionaryId", DbType.Int32).Value = dictionaryInfo.DictionaryId;
                command6.Parameters.Add("@Text", DbType.String).Value = entry.Text;
                command6.ExecuteNonQuery();
                count++;
                if (count < 1000000)
                {
                    transaction.Commit();
                    count = 0;
                }

            }
        }
        m_dbConnection.Close();

the insert process need about 8 hours to completes. How can improve performance to make the process faster?

Thanks in advance

Aucun commentaire:

Enregistrer un commentaire