A5:SQL Mk-2

開発のこと、日々のこと

2023/05/05
から 松原正和
0件のコメント

SQLの実行計画の読み方

今回は、SQLを書く上で特にパフォーマンスに影響のあるSQLの実行計画の読み方について解説します。実行計画はデータベース製品によってさまざまに差異がありますが、ここでは比較的どのデータベース製品でも共通する内容について解説します。

実行計画とは記述したSQLが実際にデータベースの内部でどのように処理されて結果を返すか、その処理方法を記述した情報です。

A5:SQL Mk-2では、SQLエディタで実行計画を見たい SQL の上にキャレットがある状態でメニューから [SQL(S)] – [SQLの実行計画(J)] または、Ctrl+E で表示できます。

表示の仕方はデータベース製品ごとに異なりますが、多くのデータベース製品ではツリー状の情報として表現されます。(このため A5:SQL Mk-2でもツリービューで実行計画を表示します。)

ツリーのリーフ(端)から処理が行われ、ルート(根)に向かって絞り込み・結合・集計・ソートを行いながら処理されていきます。

まず見るべきは、無駄なテーブルの全体読み込みが発生していないか確認することです。テーブル全体読み込みは、データベースの種類によって表現が異なりますが、TABLE ACCESS FULL (Oracle)・「Seq Scan (PostgreSQL)・ALL (MySQL) などと表示されます。 A5:SQL Mk-2ではこれらの処理は赤字で表示されます。

このとき、マスターテーブルのような、件数の少ないテーブルは全件読み込みが発生していたとしてもそれほど気にする必要はありません。多くの場合、小さなテーブルはデータベースのバッファキャッシュ(メモリ)から高速に読みだされます。大きなデータを持つトランザクションテーブルが全件読み込みになっている場合は注意が必要です。あと、赤字でないからと言って全件読み込みが発生していないとは言い切れないので注意が必要です。

大きなデータのテーブルで全件読み込みが発生している場合、インデックスの追加を検討する必要があります。絞り込みを行っているカラム・結合を行っているカラムでインデックスの追加を検討します。

また、サブクエリーを使っているSQLなどではサブクエリーの見直しを検討するのもよいかと思います。

インデックスの有無で後述するテーブルの結合方式も変わってきます。

テーブルの結合の仕方について。

テーブルの結合はデータベース製品によって様々ですが、大きく分けると内部的にネストループジョイン・ハッシュジョイン・ソートマージジョインの3つの方式が使い分けられることが多いです。(データベース製品によっては特定の結合方式がないケースや別の結合方式がある場合もあります。)

基本的にはどの方式が採用されるかはデータベース製品自体が選択します。それぞれについて説明します。

  • ネストループジョイン

ネストループジョインはそれぞれのテーブル(あるいはサブクエリーの結果セット)を2重ループで結合させる処理と思えばよいです。ただし、一般的には内側のテーブルはループではなくインデックスによるアクセスで実現されます。外側のテーブルは小さめのテーブル(全件スキャンかも)で、内側のテーブルは大きなテーブル(インデックス付き)のようなケースで使用されます。

外側のテーブル・内側のテーブルは必ずしもSQLでの記述順序とは限りません。(多くのデータベース製品ではどちらが外側・内側かは自動的に判断されます。)

  • ハッシュジョイン

ハッシュジョインは、片側のテーブル(データベースが小さいと見積もったテーブル)が結合するキー項目でメモリ上のハッシュテーブルに格納されます。もう片方のテーブルを読み込みつつ、ハッシュテーブルと突き合わせます。ハッシュテーブルを作成する処理が前処理として必要になります。

  • ソートマージジョイン

ソートマージジョインは、双方のテーブルともあらかじめ結合条件のキー項目でソートします。その後、マージ処理でキー項目を突き合わせつつ結合を行います。双方のテーブルをソートするための前処理が必要となります。またテーブルが大きくメモリに読み込めない場合、一時ファイル等に書き出されます。キー項目にインデックスが付く場合、インデックスを使ってテーブルを読み込むことでソートの代替とすることがあります。

これら3つの結合方式のうち、どれを採用するか(どれが速いと考えられるか)はデータベース製品自身が判断しますが、ハッシュジョイン・ソートマージジョインについては前処理が必要であることがパフォーマンスに影響する場合があります。結果的に結合結果の表の件数が少ない場合、前処理に時間がかかる分、ハッシュジョイン・ソートマージジョインは遅くなりがちです。また、全件結果を返す時間はともかく、最初の1件を返し始める時間は遅くなります。逆にネストループジョインは最初の1件目を返すのが速い代わりに全件返す時間は遅くなる場合があります。思い通りの結合方式になっていない場合は、インデックスを追加したり、書き方を変える必要があります。

いくつかのデータベース製品では「ヒント句」などを指定することで、どのような結合方式を採用するか・どのインデックスを使うかをある程度制御できます。

できるだけ高速な読み込み方法・結合方法を採用するために、判断基準として各データベース製品は統計情報(データの件数や種類・偏り等々)をバックグラウンドで収集します。多くの場合は自動で採取されます。大量データを投入した直後などは統計情報が実データとあっておらず、遅い実行計画になってしまうことがあります。この場合、手動で統計情報を取得するコマンドがあったりするので、それを活用すると速い実行計画になることがあります。

この記事では実行計画の読み方・パフォーマンスチューニングに関する考え方はここまでにします。遅いSQLがある場合はこの実行計画を見ながら、どのようにSQLを修正する・インデックスを追加するを考えていくことになります。

2023/05/05
から 松原正和
1件のコメント

A5:SQL Mk-2 Version 2.18.0 で導入された「ワークスペース」について

A5:SQL Mk-2 Version 2.18.0 が 2023/5/4 に公開されました。

今回のバージョンからワークスペースの機能が組み込まれています。起動時にいきなり選択画面が出てくるので、これまでA5:SQL Mk-2を使用されていた方は少しびっくりされるかもしれません。

ワークスペースはすべての設定とDBの登録情報、開いているファイルの情報をひとまとめにしたものです。
ワークスペースを切り替えることで複数の案件を簡潔に切り替えることができ、ファイルの開きすぎも抑えることができます。
ワークスペースの情報はレジストリまたは設定ファイルに保存されます。

ワークスペースはレジストリ・設定ファイルそれぞれで名前を持たないデフォルトワークスペースと名前付きのワークスペースを持ちます。デフォルトワークスペースもリネームを行うことで、名前付きワークスペースに変更することができますし、名前を空にすることでデフォルトワークスペースにすることもできます。

ワークスペースのような機能はいらないよという方は「起動時に表示する」のチェックを外すと、起動時に表示されなくなります。(前回起動したワークスペースが起動されます。)

再度、ワークスペースを使用したくなったら、メニューより、[設定(P)] – [ワークスペースの起動と管理(Z)…] またはスピードボタンより再度ワークスペース選択画面を表示できます。

2022/07/02
から 松原正和
1件のコメント

A5:SQL Mk-2 25th ANNIVERSARY !!

実は、今年(2022年)は、A5:SQL Mk-2(のもととなったプログラム)の開発を開始してから、25周年になります。四半世紀開発してきたことになります。

最初のバージョンは、「あまのいわと 5.0」という名前で開発されていました。何のソフトウェアであるかもわからないですね。中二病です。

現存する最古のソースファイルのタイムスタンプには、1997/07/17のものがあります。おそらく、開発開始はこの2~3週間前ではなかったかと思います。

驚いたことに、このごく初期の開発版ですら Windows 11 でも動作します。Windowsの互換性はすごいですね。ただし、セットアップ時に少しエラーが出たのと、コントロールパネルからデフォルトプリンタの設定作業が必要でした。

これからあと何年開発を続けられるでしょうか?。このあとさらに25年開発して、50th Anniversary とか言ってみたいですね。

2022/07/02
から 松原正和
0件のコメント

Windows を仮想マシンで動作させるときの注意点 (Home edition 版の話)

Twitter でも書いたのですが、VMware Workstation で Windows 11 Home edition を動かしたときに、使い物にならないくらい遅い現象がありました。Windows 11だから悪いのか、新調したPCに何か問題があるのか、VMware Workstationに何か問題があるのか…なんだろうなと思ったのですが、原因は意外なところにありました。

タスクマネージャーで見てみると、CPU使用率が100%に張り付き、グラフ表示を論理プロセッサに切り替えることもできません。どうやら1論理プロセッサしか認識していないようです。

実は、VMware Workstation の設定で、プロセッサを「プロセッサ数:4」・「プロセッサごとのコア数:1」にしていたのですが、Windows の Home Edition はライセンス上、プロセッサ数(ソケット数)が1個に制限されているため、プロセッサ数を4に設定しても、1プロセッサしか認識せず、1プロセッサ・1コアの状態で稼働していました。遅いはずです。(Windows Update, OneDrive, その他のプロセスでCPUを奪い合っていました。)

以下のような設定(ダメな設定)にしていました。

これを以下の設定(大丈夫な設定)のように、「プロセッサ数:1」・「プロセッサごとのコア数:4」に修正しました。

起動直後なので、まだCPU使用率高いですが、100%に張り付くことはなくなりました。

そもそもなんでこんな設定にしてしまったかですが、VMware Workstation (16.2.3)で Windows 10向けの仮想マシンを作るとき(Windows 11のテンプレートはまだない)、初期設定が「プロセッサ数:2」・「プロセッサごとのコア数:1」になっていました。うっかりここのプロセッサ数だけ4に修正していたのが原因のようです。これは間違います(orz)。VMware Workstation 的には、Professional Edition (以上?)でしか検証していないのかもしれないですね。

ひょっとして Home Edition は仮想マシンでは動作させてはいけない制約とかあったっけと不安になりましたが、とりあえずそのようなライセンス上の制約は見つけられませんでした。

2022/06/04
から 松原正和
1件のコメント

A5:SQL Mk-2で補完機能を改善

Twitter で「次世代SQLクライアントArctypeを触ってみる」とかがいくつか呟かれていて、少し悔しかったのですが、見た目のセンスはともかく?機能的には負けてないぞとか思うわけです。特にA5:SQL Mk-2の補完機能は補完機能はサブクエリや共通表式(WITH句)も解釈し、未知の構文ですら機能する高度な機能です。

↑ 共通表式の中にサブクエリーを使っているようなケースでも補完します!!。
↑ 未知の構文(というか、これはスペルミスのような…?)でもある程度補完できます。

ところで、SELECT 句の中でカラム名にエイリアスをつけた場合、カラム名エイリアスは、GROUP BY, HAVING, ORDER BY 句で利用可能でしたが、これまでは対応していなかったのを対応してみました。

↑ SELECT句で指定した、「受注番号」「 顧客番号」 のようなカラム名エイリアスがGROUP BY句で補完できます。(WHERE句では補完されません)

また、これまで「テーブル別名.」の後で補完機能を使うと、キーワードが補完候補に出てきてしまっていましたが、構文的におかしいので出さないようにしました。

他のSQLクライアントでは、SQL Server Management Studio (Microsoft) が、かなり高度な補完機能を持つようです。あとは、DataGrip は有償でそこそこのお値段もしますが、外部結合から JOIN の ON 句を生成出来たりかなり高度な補完機能を持つらしいですね。(DataGripは使ったことないですが。)

2022/05/05 追記

FROM句でテーブル名エイリアス(サブクエリーのエイリアス)の後ろにカッコで括ってカラム名エイリアスを書く構文にも対応できるようにしました。

2022/02/27
から 松原正和
0件のコメント

コマンドラインからA5:SQL Mk-2をインストールする(Wingetコマンドから または Zipファイルから)

Wingetコマンドでインストールする場合

コマンドプロンプトから次のようにして、winget を使用してA5:SQL Mk-2をダウンロードできます。

winget install "A5:SQL Mk-2" --id "9NSBB9XTJW86"
ここで、--id "9NSBB9XTJW86" は、A5:SQL Mk-2の識別子を指定しています。なくても動作するのですが、万が一 "A5:SQL Mk-2" という名前の別のソフトウェアがマイクロソフトストアで公開された場合、そちらをダウンロードしてしまわないように指定しています。

実のところ、--id "9NSBB9XTJW86" を指定すれば、 "A5:SQL Mk-2" の文字列指定は不必要なのですが、それだとなんのアプリケーションをインストールするのかわからないくなるので、"A5:SQL Mk-2" の文字列も残しています。

コマンドを実行すると、「マイクロソフトストアを使用するか」と「A5:SQL Mk-2をインストールするか」の質問が表示されるので、各々 Y を入力するとインストールすることができます。

完全にインストールを自動化するには以下のようにします。
winget install "A5:SQL Mk-2" --id "9NSBB9XTJW86" --accept-source-agreements --accept-package-agreements

ここでは、–accept-source-agreements は、マイクロソフトストアをソフトウェアのインストール元として使用することを許可するオプションです。

同様に、–accept-package-agreements は、A5:SQL Mk-2 をインストールすることを許可するオプションです。

注意点として、マイクロソフトストアを事前に起動して、自身のアカウントでサインインしておく必要があるようです。PCのキッティングには少し使いづらいかもしれません。

Vector 等からダウンロードしたZipファイルを展開する場合

上述の Winget を使う方式は、マイクロソフトストアにあらかじめサインインしている必要があり、PCのキッティングには少し使いにくいので、ダウンロード済みのZipファイルからインストールする方法を紹介します。

あらかじめ、VectorからZipファイルをダウンロードしておき、コマンドプロンプトから上記のコマンドを指定します。

PowerShell Expand-Archive -Path a5m2_2.17.1_x64.zip -DestinationPath C:\App\A5M2

スタートメニューにA5:SQL Mk-2を登録するには、あらかじめ A5M2.exe へのショートカットを作っておき、C:\ProgramData\Microsoft\Windows\Start Menu\Programs\ 配下にコピーします。(ショートカットのリンク先は、”C:\App\A5M2\A5M2.exe” など、実際に実行ファイルを指している必要があります。)

この作業は管理者権限で起動されたコマンドプロンプトで作業します。

mkdir "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\A5 SQL Mk-2(x64)"
xcopy "C:\data\A5M2.lnk" "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\A5 SQL Mk-2(x64)"

2022/02/13
から 松原正和
0件のコメント

A5:SQL Mk-2で多くなりすぎたタブを整理する

A5:SQL Mk-2を使っているとしばしばタブの数が多くなり、使いづらくなってきます。

このとき、タブ部分の Shift+マウスオーバーでタブ内容のプレビューを行うことができます。

また、Shift+タブの×ボタンで、保存の確認ダイアログなしでタブを終了することができます。

この2つのShiftキー操作とタブ操作を使うことで、不要なタブをプレビューで探し出し、×ボタンを押すことでどんどんタブを閉じることができます。

また、タブは、ドラッグ&ドロップで移動することができます。

これらの操作で、タブを整理し使いやすくできます。

2022/01/22
から 松原正和
0件のコメント

A5:SQL Mk-2 Version 2.17.0 は Windows XP で動かないのか?

2022/01/22 にA5:SQL Mk-2 Version 2.17.0 を公開しました。

(同梱の history.txt では、2021/10/23 となっていますが、これは誤りです)

ところで、readme.txt を見るとわかるのですが、Version 2.17.0 から動作環境のOSが Windows Vista ~ となっています。これは、開発環境である Delphi 11 でビルドされた実行ファイルのサポートが Windows Vista 以降のためです。

ただ本当に動作しないのかというと、Delphi 11 にパッチを当て、実行ファイルの PEヘッダでサポートする最小OSバージョンを 5.1 に変更してやることで動作するようになるようです。ただ、全機能が正しく動作するかは確認できていません。起動して、DB接続とSQL実行を少し試した程度です。

もちろん、Windows XPでいまさら開発する人たちがいるとは思わないのですが、ごく古いFA等のシステムでは、きっとWindows XPは現役かと思います。きっとPC-9801 の MS-DOS で動作するシステムも現役でしょう。

PC-9801のMS-DOS ではさすがに動作しようがありませんが、A5:SQL Mk-2はごく古いシステムのトラブルシューティング等でも使われることを想定しているので、本当は動作環境に Windows XPも含めたかったのですが、Delphi 11が対応しない方針である以上、正式に動作環境に加えるのは残念ですが断念しました。

ただ、もし Windows XPで動作に問題がある場合でも、報告していただければできる限りは対処したいと思いますので、不具合を発見した場合は、掲示板にでも書いていただけるとありがたいです。

なお、Windows 2000では起動も失敗するようです。これは、残念ですが対応は難しそうです。

2021/05/04
から 松原正和
0件のコメント

Arm Mac (M1 Mac) 上で Wine を使ってA5:SQL Mk-2を動作させる方法

Arm Mac (M1 Mac)で、Wineを使ってA5:SQL Mk-2の動作に(とりあえず)成功したので、ここに手順を記します。
まだ安定しない場合もあるかと思うので、その場合は掲示板等で報告していただけるとありがたいです。
なお、Rosetta 2について記述していませんが、必要に応じてインストールするかOSが聞いてくるはずです。

前提(条件)

OS バージョン : Mac OS 11.3
Wineバージョン : Wine 5.0
A5:SQL Mk-2 Version 2.16.0 beta 35以降(Version 2.16.0の正式版が公開されたらそちらを使用)

A5:SQL Mk-2のダウンロードと展開

Vector からダウンロードする。
64bit版をダウンロードすること。

Homebrew のインストール

公式サイト

シェルから以下のコマンドを実行

インストール処理の最後に表示されるメッセージに従って以下を実行する。
(異なるコマンドが提示されたらそちらを実行する)

Wine のインストール

公式サイト

シェルから以下のコマンドを実行

winetricks のインストール(日本語フォントのセットアップ)

winetricks のインストールと日本語代替フォントのセットアップを行う。

winetricks 本体のインストール

MS ゴシック等の代替フォントをインストールする。

必須ではないが、必要に応じてメイリオフォントの代替フォントをインストールする。

RetinaMode

近年の Macでは、4Kまたはそれ以上のFullHD を超える解像度のディスプレイが多い。
Wineではこのまま実行すると、各ドットが縦横2倍にスケーリングされた表示になってしまう。
高解像度のディスプレイの場合は、それを活かすためにWineの設定を変える必要がある。
以下のコマンドでレジストリエディタを起動する。(レジストリエディタ自体、スケーリング表示されているはず)

HKEY_CURRENT_USER\Software\Wine\Mac Driver を開き(「Mac Driver」 はないので作成)、文字列型で RetinaModeY に設定する。

次に winecfg を起動する。

スケーリング表示がデフォルトの 96dpi になっているので、ディスプレイのサイズや解像度に応じて好みの値に修正する。(24〜27インチの4Kディスプレイなら168〜192dpiくらい?)

A5:SQL Mk-2の実行

A5:SQL Mk-2のzipファイルを展開したフォルダで以下を実行

Automatorを使って実行する場合

Automatorでは、書類の種類を「アプリケーション」として以下のように「シェルスクリプトを実行」で起動するようにする。

Automator用のアイコン画像ファイル
上記アイコン画像を開いて、画像をコピーしておく。
ファインダーからアプリケーションフォルダで、A5:SQL Mk-2のAutomatorのアイコンを選択し、右クリックメニューから「情報を見る」を選択する。
左上のロボットのアイコンをクリックし、選択状態になったら、⌘Command+Vでペーストすると画像を変更することができる。

2021/04/04
から 松原正和
0件のコメント

A5:SQL Mk-2 で MySQL のジオメトリ型のテキスト表示を実装中…ちょっと困り中

PostgreSQLについては、Version 2.15系からテキスト形式(EWKT形式)でGeometry型を表示できるようになったものの、他のDBではテキスト形式表示できていませんでした。ようやく、MySQLのGeometry型をテキスト形式で表示できるように修正中。

ちょっと困っている(これのせいで実装が遅れていた)のが、PostgreSQLとは緯度経度が逆ということ。PostgreSQLでは Point(経度 緯度) のような形式でテキストを扱うのに対し、MySQLでは Point(緯度 経度) 形式で扱います。地図表示のライブラリでのテキスト形式の扱いもPostgreSQLと同様、Point(経度 緯度)のようです。

POINT(35.713768 139.777254) で入力すると出力時にはPOINT(139.777254 35.713768) で出てくるとかトラブルの原因になりそうで不安です。…どうしよう。