私はVisualStudioで扱うC#からデータベースをなるべく高速に使うことを目的としてサーベイをしてきました。
結論からいうと、MySQLやSQL ServerよりSQLiteが別格で高速です。SQLiteの欠点はセキュリティ機能がないこと。つまりログイン機能がありません。複数のアクセス主体に対して権限付与やアクセス制御をする用途ではSQLiteは不向きです。
ですが私のように、時系列データを大量につっこんで金融分析をやっている人からするとそんなアクセス制御は不要です。とにかくパフォーマンスが重要なのです。ここでいうパフォーマンスとは実行速度。レイテンシの短さとスループットの高さです。速くリードして速くライトする、これが求められています。そういった目的ならSQLiteがベストです。
SQLiteは.NET Frameworkからアクセスするのは簡単なのもメリットです。
高速化のコツを列挙します。
１．まずDataTableクラスやDataGridViewと紐付けて更新するのはやめましょう。えらい遅くて全然処理が完了しなくなります。
２．しっかりトランザクションの開始と終了を明示して、その中でreplace文を発行して更新しましょう。selectしてデータがあるか確認してから更新では遅いです。ユニーク制約を設定した上で、replace文で更新しましょう。
３．WALモードはやめましょう。ログ先行書込みはDB本体に加えてwalファイルとshmファイルができるため、DBのサイズがでかくなってくるとこれらのファイルサイズも爆発的に大きくなってきます。そうすると特にHDDにdbファイルをおいている場合は、DBへのアクセス速度が極端に落ちます。SSDでも遅くなります。
WALモードに設定した当初は速いです。つまりまだWALファイルが小さい間は速いのです。WALモードは、walファイルに変更後の新しいデータを書いていって、古いデータはdbファイル本体に残したままにします。だからwalファイルが小さい間は速いですが、大きくなってくると遅くなります。
４．Memoryモードもやめましょう。Memoryモードはアプリケーションが落ちると、ほぼ確実にdbファイルが壊れます。一応復旧できるのですが、とても面倒です。DBファイルのサイズが大きくなればなるほど復旧に時間がかかります。
５．おすすめはSync=OFF,Journal=Persistモードです。まずSyncをOFFにすると同期を取りません。同期を取らないというのは何かというと、OSにDBへのIOを依頼したら、その返答を待たないということです。つまりOSがDBにデータ書込完了をするのを待たないということ。待たないようにするとアプリケーションの応答時間がものすごく速くなります。
この同期オフの欠点はOSが落ちたときにDBが壊れるということです。OSが落ちることなんてそうそう無いので、私は同期はオフにしています。
そしてJournalモードの設定。デフォルトはDeleteモードですが、Deleteというのは変更後のものをdbファイルに書いて、変更前のをjournalファイルに移し替える方法です。そして書込に成功したら、もうjournalファイルはいらないので消します。この消すのが遅いのです。そこで、Truncateモードというのがあります。これはjournalファイルの中身を全てゼロにします。ファイルを消すよりゼロクリアの方が速いからです。そして更に速いものにPersistモードがあります。これは無効フラグを立てることでjournalファイルはもう不要、ということを教えてあげるものです。これはたった1ビット立てるだけなのでとても高速です。
まとめると、SyncをOFFにしてPersistモードにする。これが一番高速だと私は結論づけています。さらに言えばSSDにdbファイルを置くべきです。私は当初HDDにおいてやっていましたが、10GBを超えるとえらいパフォーマンスが落ちます。
ですがSSDだとそうはなりません。SSD上のSQLiteはほぼ無敵レベルで高速です。