ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

Excelの動作が重たいのは「折り返して全体を表示する」設定が原因かも

概要

Excelが重たい理由は様々指摘されていますが、何をしても改善しないときは「折り返して全体を表示する」設定がONになっているからかもしれません。本エントリではこの事象についての検証結果をまとめます。

テスト環境

  • Excel 2013 (15.0.5189.1000)

他のバージョンでも同様の事象が再現することを確認しています。

  • CPU AMD A10-5800K 3.8GHz
  • メモリ 24.0GB

CPUこそ古いですが、メモリはそれなりに積んでいます。

テストファイル

「折り返しOFF」と「折り返しON」の2バージョンを作成します。

  1. Excelファイルを「空白のブック」で新規作成する。
  2. (「折り返しON」のみ)A-K列の「折り返して全体を表示する」設定をONにする。
  3. A-K列、1-1048576行の全セルを 'a' (1文字)で埋める。
  4. Excelブック (*.xlsx) 形式で保存する。

テスト項目・結果

(1) ファイルサイズ
ファイルのプロパティから「サイズ」の値を確認する。

(2) オープン
ファイルをダブルクリックしてから表が表示されるまでの時間を計測する。
3~5回目の計測値を平均して結果とする。

(3) セル移動
ファイルを開いた直後にctrl+↓キーで最下行に移動する時間を計測する。
3回の計測値を平均して結果とする。

(4) 並べ替え
ファイルを開いた直後に並べ替え(昇順)を実行し、完了する時間を計測する。
3回の計測値を平均して結果とする。

折り返し設定 ファイルサイズ オープン セル移動 並べ替え
折り返しOFF 33.4MB 20秒 0秒 3秒
折り返しON 34.0MB 22秒 39秒 40秒

まとめ

折り返しOFFの場合に比べ、折り返しONの場合には処理時間が大幅に延びています。単純に同じ動作を繰り返す場合は処理速度の改善するケースがありましたが、実際の利用シーンでは様々な操作を行う中でたびたび待たされる状況が発生しました。

Excelのバージョンによってはファイルを開いて操作可能となるまでに長い時間を待たされるケースもあるようです。
別の環境でファイルの種類を「Excel 97-2003 ブック (*.xls)」にしても同様の事象が再現することを確認しました。
セルに設定する文字列が1文字でも事象を確認できており、実際の表示の折り返し有無とは無関係でした。

現在のところ、解決策は見つかっていません。回避策として、不要な「折り返して全体を表示する」設定はOFFにすることをお勧めします。

集合に含まれない組み合わせをクエリで抽出する

今回のクエリの説明例として、データベーススペシャリスト平成27年春期 午前Ⅱ 問11 の問題・解答を引用します。

<問題>
庭に訪れた野鳥の数を記録する"観測"表がある。観測のたびに通番を振り、鳥名と観測数を記録している。AVG関数を用いて鳥名別に野鳥の観測数の平均値を得るために、一度でも訪れた野鳥については、観測されなかったときの観測数を0とするデータを明示的に挿入する。SQL文のaに入る字句はどれか。ここで、通番は初回を1として、観測のタイミングごとにカウントアップされる。

<解答>

CREATE TABLE 観測 (
  通番 INTEGER,
  鳥名 CHAR(20),
  観測数 INTEGER,
PRIMARY KEY(通番, 鳥名))

INSERT INTO 観測
SELECT DISTINCT obs1.通番, obs2.鳥名, 0
  FROM 観測 AS obs1, 観測 AS obs2
 WHERE NOT EXISTS (
   SELECT * FROM 観測 AS obs3
    WHERE obs1.通番 = obs3.通番
      AND obs2.鳥名 = obs3.鳥名)

このクエリの目的は試験問題の解説記事に記載されています。

"観測"表に存在しないすべての(通番,鳥名)の組み合わせについて、観測数を0にしてレコードを新規追加する

平成27年春期問11 SQL文のaに入る字句はどれか|データベーススペシャリスト.com

記事ではクエリについてかなり丁寧に説明されていますが、それでもピンときにくい点があるかと思います。そこで、このクエリの分かりにくい点を解説し、集合に含まれない組み合わせをクエリで抽出する方法を説明します。

<観測表>

通番 鳥名 観測数
1 ヒバリ 1
1 メジロ 2
2 スズメ 1

SQLの実行順序は以下のとおり決まっています。
FROM → WHERE → GROUP BY → HAVING → SELECT
この順に沿ってクエリを読み解きます。

(1) FROM

FROM 観測 AS obs1, 観測 AS obs2

FROM句に複数のテーブル名が指定されており、クロス結合が行われます。その結果、以下のワークテーブルが作られます。

obs1.通番 obs1.鳥名 obs1.観測数 obs2.通番 obs2.鳥名 obs2.観測数
1 ヒバリ 1 1 ヒバリ 1
1 ヒバリ 1 1 メジロ 2
1 ヒバリ 1 2 スズメ 1
1 メジロ 2 1 ヒバリ 1
1 メジロ 2 1 メジロ 2
1 メジロ 2 2 スズメ 1
2 スズメ 1 1 ヒバリ 1
2 スズメ 1 1 メジロ 2
2 スズメ 1 2 スズメ 1

クエリで使われるのは "obs1.通番", "obs2.鳥名" のみなので、説明の為に簡略化します。本来はSELECT句で評価(射影)されることに注意してください。

obs1.通番 obs2.鳥名
1 ヒバリ
1 メジロ
1 スズメ
1 ヒバリ
1 メジロ
1 スズメ
2 ヒバリ
2 メジロ
2 スズメ

ここでポイントになるのが、obs1, obs2 という個別の表が1つのワークテーブルになり、元のテーブル名が "obs1.通番" のようにワークテーブルの列名の一部になったということです。このようにイメージしたほうが以降の処理を考えやすくなります。

ここで得られた結果は (通番, 鳥名) の全ての組み合わせとなっています。

(2) WHERE

WHERE NOT EXISTS (
  SELECT * FROM 観測 AS obs3
   WHERE obs1.通番 = obs3.通番
     AND obs2.鳥名 = obs3.鳥名)

WHERE句に obs1, obs2, obs3 と複数のテーブル名が登場して混乱しそうになりますが、obs1, obs2 はワークテーブルへと変化したことを思い出してください。ワークテーブルが work という名前とすれば、以下の形に変わります。

WHERE NOT EXISTS (
  SELECT * FROM 観測 AS obs3
   WHERE work.通番 = obs3.通番
     AND work.鳥名 = obs3.鳥名)

これは単純な相関サブクエリの形をしていることが分かります。

この相関サブクエリの意味を紐解きます。

WHERE句の評価は1行ずつ行われます。ここではワークテーブルの (1, スズメ) を評価しているとします。これを相関サブクエリに値として代入します。

WHERE NOT EXISTS (
  SELECT * FROM 観測 AS obs3
   WHERE 1 = obs3.通番
     AND 'スズメ' = obs3.鳥名)

このWHERE句は観測表(obs3)に (1, スズメ) が存在しないときに成立します。これが問題文の「観測されなかったとき」に対応します。

つまり、ワークテーブルの (通番, 鳥名) の全ての組み合わせから観測表に存在しない組み合わせを選択しています。

obs1.通番 obs2.鳥名
1 スズメ
1 スズメ
2 ヒバリ
2 メジロ

(3) SELECT

SELECT DISTINCT obs1.通番, obs2.鳥名, 0

このSELECT句より以下の結果が得られます。

obs1.通番 obs2.鳥名 -
1 スズメ 0
2 ヒバリ 0
2 メジロ 0


今回のクエリは「集合に含まれない組み合わせ」を抽出するため、全ての組み合わせ(FROM句)から既に存在する組み合わせを引き算する(WHERE句)ことで実現していました。

<参考文献>

SQLの実行順序やFROM句をクロス結合として考えることの参考にしました。

SQLの外部結合の実行ステップを理解する

はじめに

プログラマのためのSQL 第4版(以下、「訳書」とする)「25.3.1 外部結合の歴史」には外部結合の実行ステップおよび実行例が記述されています。

なお、該当の記述は4刷で一部訂正が入っています。正誤表は書籍詳細ページよりご確認ください。
プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに(ジョー・セルコ ミック ミック)|翔泳社の本

ただ、原書である『Joe Celko's SQL for Smarties』第4版(以下、「原書」とする)と訳書では、訂正結果を踏まえても相違する記述があるようです。

また、原書・訳書共に説明のやや分かり辛い点があるように思われます。

そこで、本の説明をベースとして外部結合の実行ステップについて補足を行います。

ANSi-89 Standard in SQL Server 2008
この質問スレッドで "Buy me a beer ot three and I will tell the story of the old ANSI X3H2 committee," から始まる回答の書き込みは本の著者であるセルコ自身によるものと思われ、原書の記述とほぼ同じになっています。併せてご参照ください。

外部結合の実行ステップ

外部結合の実行ステップについて、訳書では以下のように説明しています。

  1. まず2つのテーブルをクロス結合する。そして結果セットの行を1行ずつスキャンする。
  2. もし条件が TRUE になる行があればその行を保持する。
  3. もし条件が FALSE または UNKNOWN になれば、その行については保存対象のテーブルの列を保持し、保存対象でないテーブルのすべての列を NULL に変換する。最後に重複行を削除する。

一方、原書では以下の説明となっています。

  1. We build the CROSS JOIN of the two tables. Scan each row in the result set.
  2. If the predicate tests TRUE for that row, then you keep it. You also remove all rows derived from it from the CROSS JOIN.
  3. If the predicate tests FALSE or UNKNOWN for that row, then keep the columns from the preserved table, convert all the columns from the unpreserved table to NULLs, and remove the duplicates.

原書・訳書共にほぼ同じ説明と見えますが、原書の 2. にある "You also remove all rows derived from it from the CROSS JOIN." が訳書にはありません。そして、これは外部結合で重要な処理の一つです。これを次の実行例で確認します。

外部結合の処理例

外部結合の実行ステップを実行例で確認します。

原書・訳書記載の例

以下に示す2つのテーブルがあるとします。

[Table1]

a b
1 w
2 x
3 y
4 z

[Table2]

a c
1 r
2 s
3 t

本で例に挙げられている以下の外部結合を考えます。

Table1 
LEFT OUTER JOIN 
Table2
ON Table1.a = Table2.a -- JOIN condition
   AND Table2.c = 't'; -- single table condition

なお、訳書4刷以降では異なった例に修正されていることをご注意ください。今回は原書に倣って説明を進めます。

ここでは "LEFT OUTER JOIN" なので、保存対象のテーブルは演算子左側の "Table1" となります。

(1)
2つのテーブルをクロス結合します。そして結果セットの行を1行ずつON句の条件で評価します。

▲ = "Table1.a = Table2.a" が成立する。
● = "Table2.c = 't'" が成立する。

a b a c 評価結果
1 w 1 r
1 w 2 s
1 w 3 t
2 x 1 r
2 x 2 s
2 x 3 t
3 y 1 r
3 y 2 s
3 y 3 t ▲● ON句の条件が TRUE になる行集合
4 z 1 r
4 z 2 s
4 z 3 t

(2)
ON句の条件が TRUE になる行を保持します。また、その行から派生した (derived) 全ての行を削除します。
「派生した」の解釈ですが、保存対象テーブルでON句の条件が TRUE になったのと同じ行からクロス結合結果が生み出され、かつON句の条件を満たさない行です。

a b a c 評価結果
1 w 1 r
1 w 2 s
1 w 3 t
2 x 1 r
2 x 2 s
2 x 3 t
3 y 1 r 派生した行なので削除する
3 y 2 s 派生した行なので削除する
3 y 3 t ON句の条件が TRUE になる行集合
4 z 1 r
4 z 2 s
4 z 3 t

クロス結合結果でON句の条件にて TRUE となった行に保存対象テーブルの {3, y} の行が含まれています。この {3, y} の行からクロス結合が生み出され、かつON句の条件を満たさない行が派生した行として削除対象になります。

(3)
条件が FALSE または UNKNOWN の行について、保存対象のテーブルの列を保持し、保存対象でないテーブルのすべての列を NULL に変換します。最後に重複行を削除します。
重複行の判定は保存対象テーブルの同じ行から生み出されたクロス結合結果の行同士で行います。

a b a c 評価結果
1 w NULL NULL
1 w NULL NULL 重複行なので削除する
1 w NULL NULL 重複行なので削除する
2 x NULL NULL
2 x NULL NULL 重複行なので削除する
2 x NULL NULL 重複行なので削除する
3 y 3 t ON句の条件が TRUE になる行集合
4 z NULL NULL
4 z NULL NULL 重複行なので削除する
4 z NULL NULL 重複行なので削除する

(4)
最終的に以下の結果が得られます。

a b a c
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

この結果は訳書と異なりますが、原書とは一致しており、ジョー・セルコが本来意図したものとなります。

なお、訳書には原書と異なった結果と共に『あとは、この結果に「Table2.c = 't'」というSARGの条件を適用すれば、最終結果は1行に絞り込まれる。』とありますが、この説明は原書に見当たりませんでした。

より複雑な例

同じ値の行が複数あったり、NULLの行が含まれている例を考えます。

[Table3]

k3 v3
1 AAA
2 BBB
2 BBB
NULL NULL

[Table4]

k4 v4
1 aaa
1 aaa
3 ccc
NULL NULL
Table3 
LEFT OUTER JOIN 
Table4
ON Table3.k3 = Table4.k4;

(1)
2つのテーブルをクロス結合します。そして結果セットの行を1行ずつON句の条件で評価します。

k3 v3 k4 v4 評価結果
1 AAA 1 aaa ON句の条件が TRUE になる行集合
1 AAA 1 aaa ON句の条件が TRUE になる行集合
1 AAA 3 ccc
1 AAA NULL NULL
2 BBB 1 aaa
2 BBB 1 aaa
2 BBB 3 ccc
2 BBB NULL NULL
2 BBB 1 aaa
2 BBB 1 aaa
2 BBB 3 ccc
2 BBB NULL NULL
NULL NULL 1 aaa
NULL NULL 1 aaa
NULL NULL 3 ccc
NULL NULL NULL NULL

NULL = NULL は UNKNOWN となります。

(2)
ON句の条件が TRUE になる行を保持します。また、その行から派生した (derived) 全ての行を削除します。

k3 v3 k4 v4 評価結果
1 AAA 1 aaa ON句の条件が TRUE になる行集合
1 AAA 1 aaa ON句の条件が TRUE になる行集合
1 AAA 3 ccc 派生した行なので削除する
1 AAA NULL NULL 派生した行なので削除する
2 BBB 1 aaa
2 BBB 1 aaa
2 BBB 3 ccc
2 BBB NULL NULL
2 BBB 1 aaa
2 BBB 1 aaa
2 BBB 3 ccc
2 BBB NULL NULL
NULL NULL 1 aaa
NULL NULL 1 aaa
NULL NULL 3 ccc
NULL NULL NULL NULL

{1, AAA} は4行ありますが、内2行は「ON句の条件が TRUE になる行集合」のため、残り2行が「派生した行」として削除されます。

(3)
条件が FALSE または UNKNOWN の行について、保存対象のテーブルの列を保持し、保存対象でないテーブルのすべての列を NULL に変換します。最後に重複行を削除します。

k3 v3 k4 v4 評価結果
1 AAA 1 aaa ON句の条件が TRUE になる行集合
1 AAA 1 aaa ON句の条件が TRUE になる行集合
2 BBB NULL NULL
2 BBB NULL NULL 重複行なので削除する
2 BBB NULL NULL 重複行なので削除する
2 BBB NULL NULL 重複行なので削除する
2 BBB NULL NULL
2 BBB NULL NULL 重複行なので削除する
2 BBB NULL NULL 重複行なので削除する
2 BBB NULL NULL 重複行なので削除する
NULL NULL NULL NULL
NULL NULL NULL NULL 重複行なので削除する
NULL NULL NULL NULL 重複行なので削除する
NULL NULL NULL NULL 重複行なので削除する

{2, BBB, NULL, NULL} は8行ありますが、クロス結合の基となった行が異なれば重複行と判定しないため、削除されるのは内6行となります。

(4)
最終的に以下の結果が得られます。

k3 v3 k4 v4
1 AAA 1 aaa
1 AAA 1 aaa
2 BBB NULL NULL
2 BBB NULL NULL
NULL NULL NULL NULL

おわりに

外部結合はクロス結合がベースとなり、そこから保存対象外の行が削除される、という流れを理解することが大切です。これを理解していれば、結果にどのような行が現れるかを説明できます。

ここで取り上げた内容以外にも、ON句の後にWHERE句が実行される、というルールがあります。このため、条件をON句に記述するのか、WHERE句に記述するかで結果が異なってきます。詳細は訳書「25.3.1 外部結合の歴史」の「業者と部品」の例を参照ください。

外部結合の実行ステップについて、原書と訳書の説明に差異があること、また原書においてもクロス結合結果からどの行を削除すべきかの説明にあいまいな点があり、理解し辛くなっていました。この記事が理解の一助となれば幸いです。また、私の解釈に誤りがあればコメントをいただけると大変ありがたいです。

原書と訳書の説明の差異については出版社(翔泳社)に問い合わせを行っています。回答および何かしら公開できる情報がありましたら、ここに追記したいと思います。