SQLite Bulk Insert

I was trying to insert a lot of records into an SQLite database and it was taking a long time. Lots amounted to over 137000 in one table. It was taking hours to complete. I decided it must be possible to do the insertion faster with some dynamic SQL. The approach was simple and probably lends itself to some basic abstraction, but I didn't go that far. I could probably use reflection to remove some of the icky repetitive code in it, but this was ok for my immediate needs.

First up, a basic abstract class to inherit. I include some basic convenience functions in it although it might not be great style.

public abstract class bulksert { public abstract string ValueTuple(); public abstract string TablePrefix(); public static string QuotedString(string s) { return "'" + s.Replace("'", "''") + "'"; } public static string NullOrQuoted(string s) { if (s == null) return "null"; else return QuotedString(s); } public static string NullOrNumber(int? i) { if (i.HasValue) return i.Value.ToString(); else return "null"; } public static string NullOrDate(DateTime? dt) { if (dt.HasValue) return QuotedString(dt.Value.ToString("yyyy-mm-dd HH:MM")); else return "null"; } public static string NullOrBit(bool? bit) { if (bit.HasValue) { if (bit.Value) return "1"; else return "0"; } else return "null"; } }

Next up, inherit the class in the table definitions you will use.

public class Versification : bulksert { public Versification() { } public Versification(GreekMorphNTRow grow) { BookIndex = grow.Book; Chapter = grow.Chapter; Verse = grow.Verse; WordIndex = grow.WordIndex; Text = grow.Text; Word = grow.Word; NormalizedWord = grow.Lemma; if (grow.AnalyticId.HasValue) AnalyticId = grow.AnalyticId.Value; } [PrimaryKey, AutoIncrement] public int Id { get; set; } public int BookIndex { get; set; } public int Chapter { get; set; } public int Verse { get; set; } public int WordIndex { get; set; } [MaxLength(50)] public string Text { get; set; } [MaxLength(50)] public string Word { get; set; } [MaxLength(50)] public string NormalizedWord { get; set; } [ForeignKey(typeof(AnalyticLookup))] public int AnalyticId { get; set; } public override string TablePrefix() { return "insert into Versification (BookIndex, Chapter, Verse, WordIndex, Text, Word, NormalizedWord, AnalyticId) values "; } public override string ValueTuple() { StringBuilder sb = new StringBuilder(); sb.Append("("); sb.Append(NullOrNumber(BookIndex)); sb.Append(","); sb.Append(NullOrNumber(Chapter)); sb.Append(","); sb.Append(NullOrNumber(Verse)); sb.Append(","); sb.Append(NullOrNumber(WordIndex)); sb.Append(","); sb.Append(NullOrQuoted(Text)); sb.Append(","); sb.Append(NullOrQuoted(Word)); sb.Append(","); sb.Append(NullOrQuoted(NormalizedWord)); sb.Append(","); sb.Append(NullOrNumber(AnalyticId)); sb.Append(")"); return sb.ToString(); } }  

Here are the two main reuseable routines that save a lot of time doing the inserts. They assume all the records are really of the same type, which is the most common application for a bulk insert. I don't know of any reason to make the inserts accommodate multiple types, but it would be possible to apply a partition at the start if you have an application for that. 

  public Task<int> Bulksert(IEnumerable<bulksert> bulks) { StringBuilder sb = new StringBuilder(); string prefix = bulks.First().TablePrefix(); sb.AppendLine(prefix); sb.Append(bulks.First().ValueTuple()); foreach(var b in bulks.Skip(1)) { sb.AppendLine(","); sb.Append(b.ValueTuple()); } sb.AppendLine(";"); return database.ExecuteAsync(sb.ToString()); } public Task<int> Bulksert(IEnumerable<bulksert> bulks, int clusterSize) { var cluster = bulks.Take(clusterSize); var remaining = bulks.Skip(clusterSize); var InitialTask = Bulksert(cluster); if (remaining.FirstOrDefault() == null) return InitialTask; else return InitialTask.ContinueWith(ti => Bulksert(remaining, clusterSize)).Unwrap(); }

Note that the syntax for these inserts is very specific to SQLite.