SQLite3 SQL実行にかかった時間を計測する (.timer)

概要

SQLite3 で SQL を実行した際、実行にかかった時間を計測するためのコマンド。

計測出来るのは SQL のみで、コマンドラインツールのコマンドの実行時間は計測出来ない。

構文

時間計測を有効化する「on」/ 無効化する「off」
出力される時間の単位は「秒」。

.timer on
.timer off
                    

実行例

環境

• Windows 11
• SQLite Command-Line Shell ver 3.51.3

時間計測を「on」にしてSQLを実行する

c:\temp> rem  # 1
c:\temp> sqlite3 sample.db

sqlite> -- # 2
sqlite> .timer on

sqlite> -- # 3
sqlite> select * from user;
...
Run Time: real 4.729 user 0.312500 sys 1.078125
                        

1. コマンドラインツールで「sample.db」というsqliteデータベースを開く
2. 時間計測を「on」にする
3. SQLを実行すると、SQLの実行結果の後に計測結果が秒単位で出力される

各項目の意味は以下の表の通りで、これはUNIXの timeコマンドの結果と同等の内容と思われる。 （SQLite3 公式サイトに正式な説明が載っていないので推測）

種類	意味
real	実際に経過した時間。 I/O待ちや他のプロセスの影響などクエリの開始から終了までの時間になる。 コマンドラインツール上であれば、select の結果をディスプレイに出力するのにかかった時間も含まれる。
user	CPUが使われた時間。 実行する SQLの解析、テーブルのJOIN、ソート計算や関数の実行にかかった時間。 SQL 文の品質を確認したいならこの時間を確認すればよさそう。
sys	OSが処理した時間。 ファイルの読み書きの時間が含まれる。 ケースに寄りけりだが、SQLite3 ではそこまで重要視する時間ではないかも。

一般的に real ≧ user + sys となる想定

時間計測を止めるには以下のようにコマンドを実行する

sqlite> .timer off
                        

参考URL

• Command Line Shell For SQLite
  公式のコマンドラインツールに関するドキュメント
  https://www.sqlite.org/cli.html

SQLite3 文字列を結合する (concat(X,Y,...))

概要

文字列を結合するには||演算子か 関数concat()を使用する。

構文

X || Y || ...
concat(X,Y,...)
                

実行例

単純に文字列を結合する例:

sqlite> select 'taro' || '.' || 'yamada';
taro.yamada
sqlite> select concat('taro','.','yamada');
taro.yamada
                

テーブルのカラムを結合する例:

sqlite> select * from user;
┌────┬────────────┬───────────┐
│ id │ first_name │ last_name │
├────┼────────────┼───────────┤
│ 1  │ taro       │ yamada    │
│ 2  │ jiro       │ yamada    │
│ 3  │ hanako     │ suzuki    │
└────┴────────────┴───────────┘
sqlite> .mode box
sqlite> select
   ...>     first_name || '.' || last_name
   ...>         as full_name
   ...> from
   ...>     user;
┌───────────────┐
│   full_name   │
├───────────────┤
│ taro.yamada   │
│ jiro.yamada   │
│ hanako.suzuki │
└───────────────┘

sqlite> select
   ...>     concat (first_name, '.', last_name)
   ...>         as full_name
   ...> from
   ...>     user;
┌───────────────┐
│   full_name   │
├───────────────┤
│ taro.yamada   │
│ jiro.yamada   │
│ hanako.suzuki │
└───────────────┘
                

NULLを含む場合

||演算子の対象にNULLが含まれる場合、結果もNULLになる:

sqlite> .nullvalue [null]
sqlite> select 'taro' || NULL ;
┌────────────────┐
│ 'taro' || NULL │
├────────────────┤
│ [null]         │
└────────────────┘
                

concat()の引数にNULLが含まれる場合、空文字として扱われる:

sqlite> .nullvalue [null]
sqlite> select concat('taro', NULL );
┌───────────────────────┐
│ concat('taro', NULL ) │
├───────────────────────┤
│ taro                  │
└───────────────────────┘
                

参考URL

• 公式のconcat()に関するドキュメント
  https://www.sqlite.org/lang_corefunc.html#concat

SQLite3 ソートしたときのNULLの位置を調整する

概要

NULLを含むデータをソートした場合、 デフォルトだとNULLは最小値より小さい値としてソートされる。
nulls first、もしくは nulls last を使用することで、 NULLの位置をコントロールすることができる。

構文

NULL を最初に配置する。

order by column_name nulls first
                

NULL を最後に配置する。

order by column_name nulls last
                

実行例

環境

• Windows 10 64bit
• SQLite Command-Line Shell ver 3.50.4

以下のテーブルからデータをソートして取得する。

id	name
0	hoge1
1	hoge2
2

昇順ソートでNULLの位置を調整する

nameカラムに対し、

1. 何も指定しない場合
2. ascだけを指定した場合
3. nulls firstを指定した場合、 ascだけを指定した場合と同じ結果になる
4. ascとnulls first は一緒に指定することもできる（結果は同じ）
5. nulls lastを指定した場合、 値が入っているレコードが先に来て、nullが最後に来る

sqlite> .nullvalue [null]
sqlite> .mode box
sqlite> -- 1.
sqlite> select * from user;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 0  │ hoge1  │
│ 1  │ hoge2  │
│ 2  │ [null] │
└────┴────────┘
sqlite> -- 2.
sqlite> select * from user order by name asc;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 2  │ [null] │
│ 0  │ hoge1  │
│ 1  │ hoge2  │
└────┴────────┘
sqlite> -- 3.
sqlite> select * from user order by name nulls first;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 2  │ [null] │
│ 0  │ hoge1  │
│ 1  │ hoge2  │
└────┴────────┘
sqlite> -- 4.
sqlite> select * from user order by name asc nulls first;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 2  │ [null] │
│ 0  │ hoge1  │
│ 1  │ hoge2  │
└────┴────────┘
sqlite> -- 5.
sqlite> select * from user order by name asc nulls last;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 0  │ hoge1  │
│ 1  │ hoge2  │
│ 2  │ [null] │
└────┴────────┘
sqlite> select * from user order by name nulls last;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 0  │ hoge1  │
│ 1  │ hoge2  │
│ 2  │ [null] │
└────┴────────┘
                    

降順ソートでNULLの位置を調整する

nameカラムに対し、

1. descだけを指定した場合
2. desc nulls firstを指定した場合、 値が入っているレコードが後に来て、nullが最初に来る
3. desc nulls lastを指定した場合、 descを単独で指定した場合と同じ結果になる

sqlite> -- 1.
sqlite> select * from user order by name desc;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 1  │ hoge2  │
│ 0  │ hoge1  │
│ 2  │ [null] │
└────┴────────┘
sqlite> -- 2.
sqlite> select * from user order by name desc nulls first;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 2  │ [null] │
│ 1  │ hoge2  │
│ 0  │ hoge1  │
└────┴────────┘
sqlite> -- 3.
sqlite> select * from user order by name desc nulls last;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 1  │ hoge2  │
│ 0  │ hoge1  │
│ 2  │ [null] │
└────┴────────┘
                    

参考URL

• SQLite 公式サイト(英語)
  https://www.sqlite.org/index.html
• SQLite ORDER BY に関するドキュメント
  https://www.sqlite.org/syntax/ordering-term.html

SQLite3 エラーが起きた時に処理をキャンセルする (.bail)

概要

SQLite3のコマンドラインツールを使用している際、 一連の処理でエラーになった場合、 .bailコマンドを設定しておくことで その後の処理をキャンセルすることができる。

SQLiteコマンドラインツールを起動する際に -bailオプションを指定することでも 同様の効果を得ることができる。

これにより、連鎖的にエラーが発生したり、意味のない処理が動くことを止めることができる。

構文

コマンドラインツール起動中にBail機能をON/OFF にする。（デフォルトはOFF）

.bail on
.bail off
                

コマンドライン起動時にBail機能をON にする。（デフォルトはOFF）

c:\temp> sqlite3 -bail
                

実行例

環境

• Windows 10 64bit
• SQLite Command-Line Shell ver 3.50.2

.bailはCLIでコマンドを手入力する作業ではあまり恩恵がない。（と思う）
外部のSQLファイルを読み込んだり、シェルのコマンドラインからSQLを渡したときに効果を発揮する。

.bailがOFFのままの場合

以下のようなSQLファイルを準備する。

sample_01.sql

-- # 1
.bail off

-- # 2
create table user_01 (id, name);
create tbl user_02 (id, name);
create table user_03 (id, name);
                    

1. bailをOFFにする
2. create table文を３つ実行する。
   ただし、2つ目のSQL構文に誤りがあるのでエラーが発生するようになっている。

準備したSQLファイルをSQLite3のコマンドラインツールから読み込んで テーブル一覧を表示してみる。

.read ./sample_01.sql
.tables
                    

• SQLite3 コマンドラインツールからSQLファイルを読み込んで実行する
• SQLite3 コマンドラインツールでテーブル一覧を表示する (.tables)

実行結果

sqlite> .read ./sample_01.sql
Parse error near line 6: near "tbl": syntax error
  create tbl user_02 (id, name);
         ^--- error here
sqlite> .tables
user_01  user_03
                    

２つ目のSQLが失敗し、エラーになっている。 １つ目のSQLと３つ目のSQLは成功し、 テーブルが２つ作成されていることがわかる。

.bailをONにした場合

以下のようなSQLファイルを準備する。

sample_02.sql

-- # 1
.bail on

-- # 2
create table user_01 (id, name);
create tbl user_02 (id, name);
create table user_03 (id, name);
                    

1. bailをONにする
2. create table文を３つ実行する。
   2つ目のSQL構文に誤りがあるのでエラーになるようになっている。

準備したSQLファイルをSQLite3のコマンドラインツールから読み込む。

.read ./sample_02.sql
.tables
                    

• SQLite3 コマンドラインツールからSQLファイルを読み込んで実行する
• SQLite3 コマンドラインツールでテーブル一覧を表示する (.tables)

実行結果

sqlite> .read ./sample_02.sql
Parse error near line 6: near "tbl": syntax error
  create tbl user_02 (id, name);
         ^--- error here
sqlite> .tables
user_01
                    

.tablesを実行すると、２つ目のSQLが失敗し、 ３つ目のSQLで作成されるはずだったuser_03テーブルが 作成されていないことがわかる。

よって２つ目でSQLが失敗した後、以降の処理がキャンセルされていることがわかる。

コマンドラインツールから手で入力する場合

以下のような実行の仕方では .bailは意味がない。

sqlite> .bail on
sqlite> create table user_01 (id, name);
sqlite> create tbl user_02 (id, name);
Parse error: near "tbl": syntax error
  create tbl user_02 (id, name);
         ^--- error here
sqlite> create table user_03 (id, name);
sqlite> .tables
user_01  user_03
                    

以下のように一度に複数のSQLを実行する場合であれば、 途中で発生したエラー以降はキャンセルされる。

（右スクロールしてください →）

sqlite> .bail on
sqlite> create table user_01 (id, name); create tbl user_02 (id, name); create table user_03 (id, name);
Parse error: near "tbl": syntax error
  create tbl user_02 (id, name); create table user_03 (id, name);
         ^--- error here
sqlite> .tables
user_01
                    

参考URL

• Command Line Shell For SQLite
  公式のコマンドラインツールに関するドキュメント
  https://www.sqlite.org/cli.html