SQLite の フィールド数 によるパフォーマンス

投稿日時:
このエントリーをはてなブックマークに追加
Pocket

対策：SQLite データベースのパフォーマンスの最適化 - Data Storage - 開発ガイド - BlackBerry Java SDK - 6.0 に、「行のサイズを最小限にする：　幅の広い列がある場合、個別のテーブルにすることを検討します。」 なんて改定あるので、実際のところ行サイズはどれぐらいパフォーマンスに影響するのか気になってきた。

せっかくなので、レコードのデータをすべてフィールドにしてしまった場合と、すべて別テーブルにしてしまっている場合と、どちらがよいパフォーマンスを示すのかについて、 RAM メモリ, SSD, HDD でそれぞれ読み書き速度を比較してみた。

あくまで、私の環境においてなので、参考ぐらいにしておいていただければ幸いだ。

検証コード

一応、以下の様な配置で、bundle 版 System.Data.SQLite.dll を配置していることが前提。

  • sqlite_performance_check.ps1
  • [x86]
    • System.Data.SQLite.dll
  • [x64]
    • System.Data.SQLite.dll

どちらかの環境だけでしか動かさないのであれば、動かす環境用の System.Data.SQLite.dll だけ置いておけば問題ない。

<###############################################################
System.Net.SQLite のパフォーマンスを見る
###############################################################>
param (
    $Count = 1KB,
    [switch]$Do01,
    [switch]$Do10,
    [switch]$Do99,
    [switch]$Read,
    [switch]$Write
)

$mydir = Split-Path $myInvocation.MyCommand.Path -Parent #スクリプトのフォルダを取得
Add-Type -Path (Join-Path (Join-Path $mydir (&{if ([IntPtr]::Size -eq 8) { 'x64' } else { 'x86' }})) 'System.Data.SQLite.dll')

# -Read も -Write も設定なければ、両方実行
if (!$Read -and !$Write) {
    $Read = $Write = $true;
}

# シャッフルデータ作成
[string[]]$shuffleStr = 0..(16KB - 1) | %{ [Guid]::NewGuid().ToString() }

function Execute-Sql ([Data.SQLite.SQLiteCommand]$cmm, [string]$sql) {
    $cmm.CommandText = $sql;
    $cmm.ExecuteNonQuery() > $null;
}

$cnn = New-Object Data.SQLite.SQLiteConnection("Data Source=$(Join-Path $mydir 'test.sqlite3')")
$cnn.Open();
$cmm = $cnn.CreateCommand();

Execute-Sql $cmm 'CREATE TABLE IF NOT EXISTS tableid01 ([Id] INTEGER PRIMARY KEY, [TableRowId] INTEGER, [DataId] INTEGER)'
Execute-Sql $cmm 'CREATE TABLE IF NOT EXISTS tabledata01 ([Id] INTEGER PRIMARY KEY, [DataId] INTEGER, [DataTypeId] INTEGER , [TEXT1] TEXT)'
Execute-Sql $cmm 'CREATE INDEX IF NOT EXISTS tableid01index on tableid01(TableRowId)'
Execute-Sql $cmm 'CREATE INDEX IF NOT EXISTS tabledata01index on tabledata01(DataId)'

Execute-Sql $cmm 'CREATE TABLE IF NOT EXISTS tableid10 ([Id] INTEGER PRIMARY KEY, [TableRowId] INTEGER, [DataId] INTEGER)'
Execute-Sql $cmm "CREATE TABLE IF NOT EXISTS tabledata10 ([Id] INTEGER PRIMARY KEY, [DataId] INTEGER, [DataTypeId] INTEGER, $((1..10 | %{ `"[TEXT$_] TEXT`" }) -join ', '))"
Execute-Sql $cmm 'CREATE INDEX IF NOT EXISTS tableid10index on tableid10(TableRowId)'
Execute-Sql $cmm 'CREATE INDEX IF NOT EXISTS tabledata10index on tabledata10(DataId)'

Execute-Sql $cmm "CREATE TABLE IF NOT EXISTS table99 ([Id] INTEGER PRIMARY KEY, [TableRowId] INTEGER, $((1..99 | %{ `"[TEXT$_] TEXT`" }) -join ', '))"
Execute-Sql $cmm 'CREATE INDEX IF NOT EXISTS table99index on table99(TableRowId)'

$cmm.Dispose();
$cnn.Dispose();

$cnn = New-Object Data.SQLite.SQLiteConnection("Data Source=$(Join-Path $mydir 'test.sqlite3')")
try {
    $cnn.Open()
    $cmm = $cnn.CreateCommand();
    $cmm.Parameters.Add('Id', [Data.DbType]::Int64) > $null;
    $cmm.Parameters.Add('Type', [Data.DbType]::Int64) > $null;
    foreach ($i in 1..99) {
        $cmm.Parameters.Add("TEXT$i", [Data.DbType]::String) > $null;
    }

    try {

        if ($Do99) {
            Write-Host '::DO99:: '

            if ($Write) {
                Write-Host 'Insert: '
                Measure-Command {
                    $strsql = "INSERT INTO table99 ([TableRowId], $((1..99 | %{ `"[TEXT$_]`" }) -join ', ')) VALUES(:Id, $((1..99 | %{ `":TEXT$_`" }) -join ', '))"

                    Execute-Sql $cmm 'BEGIN TRANSACTION'
                    foreach ($i in 0..($Count - 1)) {
                        $cmm.Parameters['Id'].Value = $i; # TableRowId
                        foreach ($j in 1..99) {
                            $cmm.Parameters["TEXT$j"].Value = $shuffleStr[($i * $Count + $j) % 16KB];
                        }
                        Execute-Sql $cmm $strsql

                        # 1K 毎に トランザクションを切る
                        if ($i % 1KB -eq 0) {
                            Execute-Sql $cmm 'END TRANSACTION'
                            Execute-Sql $cmm 'BEGIN TRANSACTION'
                        }
                    }
                    Execute-Sql $cmm 'END TRANSACTION'

                } | Tee-Object -Variable Func99InsertTime | Select-Object TotalSeconds
            }

            if ($Read) {
                Write-Host 'Select: '
                $shuffleIds = 0..($Count - 1) | sort -prop @{Exp={[Guid]::NewGuid()}}
                Measure-Command {
                    foreach ($i in $shuffleIds) {
                        $cmm.Parameters['Id'].Value = $i;
                        Execute-Sql $cmm 'SELECT * FROM table99 WHERE [TableRowId] = :Id';
                    }
                } | Tee-Object -Variable Func99InsertTime | Select-Object TotalSeconds
            }
        }

        if ($Do10) {
            Write-Host '::DO10*10:: '

            if ($Write) {
                Write-Host 'Insert: '
                Measure-Command {
                    $strsqlA = 'INSERT INTO tableid10 ([TableRowId], [DataId]) VALUES(:Id, :Type)'
                    $strsqlB = "INSERT INTO tabledata10 ([DataId], [DataTypeId], $((1..10 | %{ `"[TEXT$_]`" }) -join ', ')) VALUES(:Id, :Type, $((1..10 | %{ `":TEXT$_`" }) -join ', '))"

                    Execute-Sql $cmm 'BEGIN TRANSACTION'
                    foreach ($i in 0..($Count - 1)) {
                        $cmm.Parameters['Id'].Value = $i; # TableRowId
                        $cmm.Parameters['Type'].Value = $i;   # DataId
                        Execute-Sql $cmm $strsqlA

                        foreach ($j in 0..9) {
                            $cmm.Parameters['Id'].Value = $i; # DataId
                            $cmm.Parameters['Type'].Value = $j;   # DataTypeId
                            foreach ($k in 1..10) {
                                $cmm.Parameters["TEXT$k"].Value = $shuffleStr[(10 * $i * $Count + $j * $i + $k) % 16KB];
                            }
                            Execute-Sql $cmm $strsqlB
                        }

                        # 1K 毎に トランザクションを切る
                        if ($i % 1KB -eq 0) {
                            Execute-Sql $cmm 'END TRANSACTION'
                            Execute-Sql $cmm 'BEGIN TRANSACTION'
                        }
                    }
                    Execute-Sql $cmm 'END TRANSACTION'

                } | Tee-Object -Variable Func99InsertTime | Select-Object TotalSeconds
            }

            if ($Read) {
                Write-Host 'Select: '
                $shuffleIds = 0..($Count - 1) | sort -prop @{Exp={[Guid]::NewGuid()}}
                Measure-Command {
                    foreach ($i in $shuffleIds) {
                        $cmm.Parameters['Id'].Value = $i;
                        Execute-Sql $cmm 'SELECT * FROM tabledata10 WHERE DataId IN (SELECT DataId FROM tableid10 WHERE [TableRowId] = :Id)';
                    }
                } | Tee-Object -Variable Func99InsertTime | Select-Object TotalSeconds
            }
        }

        if ($Do01) {
            Write-Host '::DO1*100:: '

            if ($Write) {
                Write-Host 'Insert: '
                Measure-Command {
                    $strsqlA = 'INSERT INTO tableid01 ([TableRowId], [DataId]) VALUES(:Id, :Type)'
                    $strsqlB = 'INSERT INTO tabledata01 ([DataId], [DataTypeId], [TEXT1]) VALUES(:Id, :Type, :TEXT1)'

                    Execute-Sql $cmm 'BEGIN TRANSACTION'
                    foreach ($i in 0..($Count - 1)) {
                        $cmm.Parameters['Id'].Value = $i; # TableRowId
                        $cmm.Parameters['Type'].Value = $i;   # DataId
                        Execute-Sql $cmm $strsqlA

                        foreach ($j in 0..99) {
                            $cmm.Parameters['Id'].Value = $i; # DataId
                            $cmm.Parameters['Type'].Value = $j;   # DataTypeId
                            $cmm.Parameters['TEXT1'].Value = $shuffleStr[(100 * $i * $Count + $j) % 16KB];
                            Execute-Sql $cmm $strsqlB
                        }

                        # 1K 毎に トランザクションを切る
                        if ($i % 1KB -eq 0) {
                            Execute-Sql $cmm 'END TRANSACTION'
                            Execute-Sql $cmm 'BEGIN TRANSACTION'
                        }
                    }
                    Execute-Sql $cmm 'END TRANSACTION'

                } | Tee-Object -Variable Func99InsertTime | Select-Object TotalSeconds
            }

            if ($Read) {
                Write-Host 'Select: '
                $shuffleIds = 0..($Count - 1) | sort -prop @{Exp={[Guid]::NewGuid()}}
                Measure-Command {
                    foreach ($i in $shuffleIds) {
                        $cmm.Parameters['Id'].Value = $i;
                        Execute-Sql $cmm 'SELECT * FROM tabledata01 WHERE DataId IN (SELECT DataId FROM tableid01 WHERE [TableRowId] = :Id)';
                    }
                } | Tee-Object -Variable Func99InsertTime | Select-Object TotalSeconds
            }
        }

    } finally {
        $cmm.Dispose()
    }
} finally {
    $cnn.Dispose()
}

99フィールドを1レコードに仕舞ってしまっているのが -Do99 で、
ひとつのレコードに データフィールドを10個持つレコードを、10レコード関連づけているのが -Do10
ひとつのレコードに データフィールドを1つだけ持つレコードを、100レコード関連づけているのが -Do01

それぞれの Insert した時間と ランダムに Select した時間を測定するものだ。

1024 レコード、4096 レコード、16384 レコードと、レコード数による変化と、
RAM ディスク上で動かした場合、HDD 上で動かした場合、SSD 上で動かした場合、一度ファイルにアクセスして RAM にキャッシュされた場合など、ストレージによる変化を確認すると、以下の様になる。

1KB

> .\sqlite_performance_check.ps1 -Do01 -Do10 -Do99 -Count 1KB

            TotalSeconds
            ------------
::DO99::
Insert:
                3.400592
Select:
               0.3267183
::DO10*10::
Insert:
               6.8167525
Select:
               0.3121771
::DO1*100::
Insert:
              28.8467136
Select:
               0.3149239

4KB

> .\sqlite_performance_check.ps1 -Do01 -Do10 -Do99 -Count 4KB

            TotalSeconds
            ------------
::DO99::
Insert:
              13.5436556
Select:
                1.316919
::DO10*10::
Insert:
              27.1984719
Select:
               1.2525614
::DO1*100::
Insert:
             115.3996579
Select:
               1.2619968

16KB

> .\sqlite_performance_check.ps1 -Do01 -Do10 -Do99 -Count 16KB

            TotalSeconds
            ------------
::DO99::
Insert:
              53.7108137
Select:
               5.2602945
::DO10*10::
Insert:
             146.2867899
Select:
               5.0426413
::DO1*100::
Insert:
             639.7420941
Select:
               5.0647618

RAM

> .\sqlite_performance_check.ps1 -Do01 -Do10 -Do99 -Count 16KB -Read

            TotalSeconds
            ------------
::DO99::
Select:
               5.2987277
::DO10*10::
Select:
               5.0834981
::DO1*100::
Select:
               5.0632528

SSD (1回目, キャッシュ無し)

> .\sqlite_performance_check.ps1 -Do01 -Do10 -Do99 -Count 16KB -Read

            TotalSeconds
            ------------
::DO99::
Select:
               9.3642307
::DO10*10::
Select:
               9.8006135
::DO1*100::
Select:
               10.8584984

SSD (2回目, キャッシュ有り)

> .\sqlite_performance_check.ps1 -Do01 -Do10 -Do99 -Count 16KB -Read

            TotalSeconds
            ------------
::DO99::
Select:
               5.4711732
::DO10*10::
Select:
                5.261745
::DO1*100::
Select:
               5.2114789

HDD (1回目, キャッシュ無し)

> .\sqlite_performance_check.ps1 -Do01 -Do10 -Do99 -Count 16KB -Read

            TotalSeconds
            ------------
Select:
               39.365767
::DO10*10::
Select:
              53.9342967
::DO1*100::
Select:
              67.7335061

HDD (2回目, キャッシュ有り)

> .\sqlite_performance_check.ps1 -Do01 -Do10 -Do99 -Count 16KB -Read

            TotalSeconds
            ------------
Select:
               5.1556883
::DO10*10::
Select:
               4.9654451
::DO1*100::
Select:
               4.9695433

レコード数ごとのパフォーマンスは以下の通り。
レコード数に線形に増えているようだ。

さすがに Insert は レコードの数が増えるに従ってどんどん長くなるが、Select のほうはほとんど変わらず、逆にフィールド数が少ない方が早いくらいだ。

レコード数 1024 4096 16384
Insert 99*1 3.401 13.544 53.711
10*10 6.817 27.198 146.287
1*100 28.847 115.400 639.742
Select 99*1 0.327 1.317 5.260
10*10 0.312 1.253 5.043
1*100 0.315 1.262 5.065

ストレージごとに違いは以下の通り。
さすがに RAM は早いが、数百MB で 余っている RAM にキャッシュされてしまえば、SSD や HDD でも同じ速度が出るようだ。

ストレージが遅い場合は、レコード数が少なくフィールドが多い方が早いが、RAM に読み込んでしまうと、むしろ レコード数が多くてもフィールドが小さい方が早くなるようだ。

ストレージ RAM SSD (1回目, キャッシュ無し) SSD (2回目, キャッシュ有り) HDD (1回目, キャッシュ無し) HDD (2回目, キャッシュ有り)
Select 99*1 5.299 9.364 5.471 39.366 5.156
10*10 5.083 9.801 5.262 53.934 4.965
1*100 5.063 10.858 5.211 67.734 4.970

多フィールド少レコードでも、少フィールド多レコードでも、 Select の速度はそこまで大きく変わらないようだ。
データのフィールドが大きくなってしまう場合は、テーブルを分けたりしてみるても良さそうだ。

コメントを残す

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。