SQLite で LINQ を使う

投稿日: 2016/08/08 作成者: masuda

と或るところで、さらっと答えてしまったら間違っていたので、仕切り直しがてらに進呈致します。どうやら、昔、どこかでオンメモリの設定で使っていてその知識がそのままだったようです。ファイルベースで動いていますね、SQLiteは。後述しますが、接続文字列のところで「DataSource=sample.db」のようにファイル名ではなくて「DataSource=:memory:」にするとオンメモリのDBとして動作します。

System.Data.SQLite を使う

SQLiteの本家サイトから落としてもいいのですが、手っ取り早く NuGet で落とします。

.NET Core 用には System.Data.SQLite.Core があるのと、Windows IoT Core のような UWP アプリで内部でデータベースを扱うのに SQLite 一択となるので、一度使ってみるといいかもしれません。NuGet には「Microsoft.Data.SQLite」もあるので、多少乱立ぎみなのかも。

SQLiteにテーブルを作る

実験用に users テーブルを作ります。SQL文は、一度 PupSQLite で作ったものから吐き出しています。

Pup’s Atelier-Software
https://www.eonet.ne.jp/~pup/software.html

オンメモリにする場合は、コネクションをクローズするたびに無くなってしまう（揮発性）なので、アプリが起動したときにコネクションを開いて、終わるときに閉じるという具合でしょう。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
{
    string sql = @"
CREATE TABLE [users] (
[id] VARCHAR(256),
[username] VARCHAR(256) NOT NULL,
[email] VARCHAR(256),
[birthday] DATETIME,
[age] INTEGER,
[memo1] VARCHAR(256),
[memo2] VARCHAR(256),
[memo3] VARCHAR(256),
[memo4] VARCHAR(256),
[memo5] VARCHAR(256),
[memo6] VARCHAR(256),
[memo7] VARCHAR(256),
[memo8] VARCHAR(256),
[memo9] VARCHAR(256),
[memo10] VARCHAR(256),
PRIMARY KEY(id)
);";
    var cn = new SQLiteConnection("DataSource=" + db_file);
    var cmd = new SQLiteCommand(sql, cn);
    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();
    textMsg.Text = "Users テーブルを作成しました";
}

Memo1 から Memo10 のデータは、ファイルを巨大にするためのダミーデータ用です。このテーブルで作ると10万件で、500MB ぐらいのファイルができあがります。

Userクラスを作る

LINQ で使えるように Entity クラスを作ります。
カラムの型は指定しなくても、うまくマッピングできています。Table属性とColumn属性のNameを指定すればokです。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
using System.Data.Linq.Mapping;
 
[Table(Name ="users")]
class User
{
    [Column(Name ="id", IsPrimaryKey = true )]
    public string Id { get; set; }
    [Column(Name = "username", CanBeNull = false)]
    public string UserName { get; set; }
    [Column(Name = "email",CanBeNull = true)]
    public string Email { get; set; }
    [Column(Name = "birthday", CanBeNull = true)]
    public DateTime? Birthday { get; set; }
    [Column(Name = "age" ,CanBeNull = true)]
    public int? Age { get; set; }
 
    [Column(Name = "memo1")]
    public string Memo1 { get; set; }
    [Column(Name = "memo2")]
    public string Memo2 { get; set; }
    [Column(Name = "memo3")]
    public string Memo3 { get; set; }
    [Column(Name = "memo4")]
    public string Memo4 { get; set; }
    [Column(Name = "memo5")]
    public string Memo5 { get; set; }
    [Column(Name = "memo6")]
    public string Memo6 { get; set; }
    [Column(Name = "memo7")]
    public string Memo7 { get; set; }
    [Column(Name = "memo8")]
    public string Memo8 { get; set; }
    [Column(Name = "memo9")]
    public string Memo9 { get; set; }
    [Column(Name = "memo10")]
    public string Memo10 { get; set; }
}

検索用の LINQ

System.Data.Linqを参照設定しておいて、DataContextクラスを使います。テーブルを参照するときは、GetTableメソッドで指定のテーブルを取得。

1
2
3
4
5
6
7
8
9
10
11
12
private void clickCount(object sender, RoutedEventArgs e)
{
    var sw = new Stopwatch();
    sw.Start();
    var cn = new SQLiteConnection("DataSource=" + db_file);
    var context = new DataContext(cn);
    var users = context.GetTable<User>();
    int count = users.Count();
 
    textMsg.Text = $"{count} 件のデータがあります";
    textTime.Text = $"{sw.ElapsedMilliseconds} msec";
}

すると、LINQ が使えるようになるので、where文などを使って条件を指定できるようになります。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
private void clickAge(object sender, RoutedEventArgs e)
{
    var sw = new Stopwatch();
    sw.Start();
    var cn = new SQLiteConnection("DataSource=" + db_file);
    var context = new DataContext(cn);
    var users = context.GetTable<User>();
    var q = from t in users
            where 40 <= t.Age && t.Age < 50
            select t;
    var count = q.Count();
    sw.Stop();
    textMsg.Text = $"{count} 件のデータがあります";
    textTime.Text = $"{sw.ElapsedMilliseconds} msec";
}

10万件のデータを検索したところ（インデックスなし）、300 mesc ちょっとで返って来ます。

メモリの具合はどうかというと、Working set の private が 30 MB 程度。このとき sample.db ファイルの大きさは 500MB 程度あるので、ファイルアクセスをしています。年齢で検索するために clickAge を呼び出した瞬間に HDD アクセスが大量発生してます。

となると検索スピードは HDD/SSD のスピードに依るということですね。

データ作成

データ作成をする INSERT は意外と遅いです。ロジックが多少複雑だというのもあるんでしょうが、1000件作るのに7秒程度かかります。オンメモリにすると 300msec 程度なので insert スピードが 20倍ぐらい違います。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
private void clickCreateData(object sender, RoutedEventArgs e)
{
    var cn = new SQLiteConnection("DataSource=" + db_file); //  ";SyncMode=off;JournalMode=Memory");
    var context = new DataContext(cn);
    var users = context.GetTable<User>();
    _rnd = new Random();
    int max = int.Parse(textSize.Text);
    var sw = new Stopwatch();
    sw.Start();
    for ( int i=1; i<=max; i++ )
    {
        users.InsertOnSubmit(makeUser());
        if ( i % 100 == 0 )
        {
            context.SubmitChanges();
            Debug.WriteLine($"{i} 件 挿入...");
        }
    }
    context.SubmitChanges();
    sw.Stop();
    textMsg.Text = $"{max} 件のデータを挿入しました";
    textTime.Text = $"{sw.ElapsedMilliseconds} msec";
}
 
private User makeUser()
{
    var user = new User();
    user.Id = Guid.NewGuid().ToString("D");
    user.UserName = createName();
    user.Email = createEmail(user.UserName);
    user.Birthday = null;
    user.Age = _rnd.Next(10, 100);
    user.Memo1 = createMemo();
    user.Memo2 = createMemo();
    user.Memo3 = createMemo();
    user.Memo4 = createMemo();
    user.Memo5 = createMemo();
    user.Memo6 = createMemo();
    user.Memo7 = createMemo();
    user.Memo8 = createMemo();
    user.Memo9 = createMemo();
    user.Memo10 = createMemo();
 
    return user;
}
private string createName()
{
    // xxxxxx xxxxxx
    var name = "Aaaaaa Aaaaaa";
    var ch = name.ToCharArray();
    for (int i = 0; i < ch.Length; i++) {
        if (ch[i] == ' ') continue;
        ch[i] = (char)(ch[i] + _rnd.Next(26));
    }
    return new string(ch);
}
private string createEmail( string username )
{
    // xxxxxx xxxxxx
    var name = username.Split(' ')[0];
    return name + "@mail.com";
}
private string createMemo(int size = 256)
{
    var ch = new char[size];
    for (int i = 0; i < size; i++)
    {
        ch[i] = (char)('A' + _rnd.Next(26));
    }
    return new string(ch);
}

機会を作って、.NET Core のほうの SQLite も試してみるということで。

サンプルコード

サンプルコードはこちら
https://1drv.ms/u/s!AmXmBbuizQkXgfsUYttAgWYXnhiogw

参考先

C# で SQLite を便利に使うサンプルコード（LINQ to SQLite） – 翔星 Be ランド日記
http://shinta0806be.ldblog.jp/archives/9084539.html

In-Memory Databases
https://www.sqlite.org/inmemorydb.html

 

カテゴリー: SQLite パーマリンク

SQLite で LINQ を使う への1件のコメント

  1. ピンバック: C#でSQLite3を使ってみる | IT技術情報局

コメントは停止中です。