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