よく聞くけど、SharePoint リストの 5,000 件問題ってなんなの？

MICROSOFT 365 VIRTUAL MARATHON 2022 MAY, 4 – 6, 2022 OTA HIROFUMI / 太田 浩史 Microsoft MVP for Office Apps & Services @hrfmjp hrfmjp idea.tostring.jp SharePoint リストの 5,000 件問題

MICROSOFT 365 VIRTUAL MARATHON 2022 SPONSORS M365VIRTUALMARATHON.COM #M365VM

OTA HIROFUMI / 太田 浩史 Microsoft MVP for Office Apps & Services ▪ 株式会社内田洋行 ▪ Twitter: twitter.com/hrfmjp ▪ LinkedIn: www.linkedin.com/in/hrfmjp ▪ Blog: idea.tostring.jp ▪ Microsoft Teams 踏み込み活用術 M365VIRTUALMARATHON.COM #M365VM

このセッションの内容 • そもそも 5,000 件問題とは • SharePoint の対応 • モダンリストの動作 • まとめ M365VIRTUALMARATHON.COM #M365VM

免責 • 調査・検証の結果をまとめた自由研究です • 検証は 2022 年 4 月中旬ごろ行いました • SharePoint Online のアップデートにより動作が変更されることがあります M365VIRTUALMARATHON.COM #M365VM

こんな噂を聞いたことはありませんか？ SharePoint リストには 5,000 件までしか保存できない？ いまは 20,000 件でしょ？ モダンリストを使えば 20,000 件なんだよね M365VIRTUALMARATHON.COM 5,000 件超えると何が起こるの？ SharePoint 5,000 件問題 結局のところ、 5,000 件問題ってなに？ すでにリストには 30,000 件も アイテムが入ってるんだけど？ #M365VM

令和の都市伝説 5,000 件問題は実在した!! M365VIRTUALMARATHON.COM #M365VM

調査の前提条件 • リストには 100,000 件ほどのアイテムを保存 • すべてのアイテムはリストのルートに作成（フォルダー分けをしていない） M365VIRTUALMARATHON.COM #M365VM

MICROSOFT 365 VIRTUAL MARATHON 2022 MAY, 4. – 6. 2022 そもそも 5,000 件問題とは

そもそもなぜ 5,000 件問題が存在するのか う～ん、大変 SharePoint に入っている データをまとめて処理しちゃお SharePoint に過度の負荷がかかるのを避けるため M365VIRTUALMARATHON.COM #M365VM

そもそもなぜ 5,000 件問題が存在するのか 昔のオンプレミスの SharePoint の動作 SQL Server データ テーブル レコード 1 SharePoint に入っている データをまとめて処理しちゃお レコード 2 レコード 3 ・・・ レコード 10000 レコード 10001 ・・・ 5,000 ロック エスカレーション 一定数以上の行ロックが発生したらテーブル全体をロック M365VIRTUALMARATHON.COM #M365VM

そもそもなぜ 5,000 件問題が存在するのか 処理が終わるまで後続の処理をブロック SQL Server データ テーブル レコード 1 レコード 2 レコード 3 ・・・ レコード 10000 レコード 10001 🥺 ・・・ M365VIRTUALMARATHON.COM #M365VM

そもそもなぜ 5,000 件問題が存在するのか 5,000 件より多くを対象とした処理をブロック SQL Server データ テーブル レコード 1 SharePoint が ブロックします！ レコード 2 レコード 3 ・・・ 😊 レコード 10000 レコード 10001 ・・・ より多くのユーザー要求を処理できる M365VIRTUALMARATHON.COM #M365VM

つまり 5,000 件問題とは 5,000 件よりも多くのアイテムを 一度に 処理できない 保存できない SharePoint リストには最大 3,000 万件のデータが格納できる M365VIRTUALMARATHON.COM #M365VM

どんな処理が対象となるか 5,000 件よりも多くのアイテムを • • • • 一度に取得する処理 並び替える処理 フィルターする処理 グループ化する処理 など M365VIRTUALMARATHON.COM #M365VM

5,000 件問題はいまでも私たちの身近に存在する M365VIRTUALMARATHON.COM #M365VM

MICROSOFT 365 VIRTUAL MARATHON 2022 SharePoint の対応 MAY, 4. – 6. 2022

一度に取得するアイテム数を制限する 遅延読み込み • ビューの表示に必要な数のアイテムを取得 • 以降のアイテムはユーザーのスクロールに 合わせて順次取得していく • 多くのアイテムを一度に取得しないことで データが多いときのパフォーマンスを向上 一番下まできたら 次の表示に必要な アイテムを読み込む M365VIRTUALMARATHON.COM #M365VM

ちょっと具体的な処理を考える リストから更新日時の新しい順にアイテムを 5 件取得して表示する リストのデータ アイテム 途中の処理 更新日時 アイテム 取得するデータ 更新日時 アイテム 更新日時 アセロラ 2022/3/10 アセロラ 2022/3/10 ライチ 2022/4/20 パパイア 2022/1/20 パパイア 2022/1/20 レモン 2022/4/20 マルメロ 2022/1/8 マルメロ 2022/1/8 リンゴ 2022/4/20 マンゴー 2022/4/19 ・・・ ・・・ モモ 2022/4/3 モモ 2022/4/3 プルーン 2022/4/19 ライチ 2022/4/20 ライチ 2022/4/20 ライチ 2022/4/19 ・・・ ・・・ ・・・ 全件を対象とした並び替え処理がブロックされる M365VIRTUALMARATHON.COM #M365VM

列インデックスの利用 SharePoint が効率よくデータを扱うための機能 アイテム 更新日時 アセロラ 2022/3/10 パパイア 2022/1/20 マルメロ 2022/1/8 ・・・ モモ 2022/4/3 ライチ 2022/4/20 ・・・ アセロラは あそこだな スグに取り出せるように 前もって整理しとこ… マルメロって なんだろう？ ライチは下の 方にあるな インデックスがあれば 5,000 件を超える処理も実行できる M365VIRTUALMARATHON.COM #M365VM

列インデックスの作成 4 1 2 3 5 [リストの設定] から設定画面を開き、 2 [インデックス付きの列] をクリック。インデックス付きの列の 設定画面で、 3 [新しいインデックスの作成] をクリックし、 4 インデックスを付けたい列を選択した後 5 [作成] ボタンを押すとインデックスが作成される 1 ID 列は、列インデックスを作成しなくてもインデックス付きの列として動作 M365VIRTUALMARATHON.COM #M365VM

列インデックスをサポートする列 種類によってインデックスの作成可否が異なる サポートされている列の種類 • • • • • • • • • 1行テキスト 選択肢 数値 通貨 日付と時刻 はい/いいえ 個人 管理されたメタデータ ルックアップ サポートされていない列の種類 • • • • • • 複数行テキスト 選択肢（複数値） 集計値 ハイパーリンク 個人（複数値） 管理されたメタデータ（複数） ルックアップ列と呼ばれる種類の列では、 インデックスでも 5,000 件問題を完全には回避できない 参考: リストまたはライブラリ列にインデックスを追加する M365VIRTUALMARATHON.COM #M365VM

• https://support.microsoft.com/ja-jp/office/リストまたはライブラリ列にインデックスを追加する-f3f00554-b7dc-44d1-a2ed-d477eac463b0

列インデックスの動作 フィルターが出来る M365VIRTUALMARATHON.COM 並び替えが出来る #M365VM

列インデックスの自動作成 列インデックスはユーザー操作に応じて自動作成されることがある あっ！並び替えた！ インデックス作っておこ リスト内のアイテムが 5,000 件を超えている場合の操作で作成される ※ ルックアップ列は列インデックスは自動作成されませんでした M365VIRTUALMARATHON.COM #M365VM

20,000 件問題 問題 列インデックスの自動作成はアイテム数が 20,000 件以下の場合にしか動作しない 解決策 20,000 件を超えたら 列インデックスを手動で作成する ※ SharePoint Online ではアイテム数によらず列インデックスを作成できる ただし、現時点の動作を実際に確認すると、 20,000 件を超えていても列インデックスは自動作成されました M365VIRTUALMARATHON.COM 公式情報が どこにもない… #M365VM

ここまでのまとめ 5,000 件問題は処理負荷を軽減し、より多くのユーザーの要求を 処理するために SharePoint が設けている制限 列インデックスを作成することで、制限を回避することができる 列インデックスはユーザー操作に応じて自動作成されることがある 自動作成されなければ、手動で作成したら良い M365VIRTUALMARATHON.COM #M365VM

MICROSOFT 365 VIRTUAL MARATHON 2022 モダンリストの動作 MAY, 4. – 6. 2022

インデックス付き列の動作 フィルターが出来る M365VIRTUALMARATHON.COM 並び替えが出来る #M365VM

ルックアップ列のインデックスの制限 フィルターは出来る 並び替えは出来ない 並び替えたフリ M365VIRTUALMARATHON.COM #M365VM

インデックスが付かない列のフィルター動作 フィルターが出来る フィルターが出来ない？ 一体なにが 起きているのか？ M365VIRTUALMARATHON.COM #M365VM

5,000 件問題フローチャート 操作対象のアイテム数は 5,000 件以下か？ はい いいえ 列インデックスが 利用できる操作か？ 問題なく動作 はい 並び替えやフィルターが 問題なく動作する いいえ ？ ※ ルックアップ列に制限 M365VIRTUALMARATHON.COM #M365VM

インデックスが付かない列のフィルター動作 アイテム 栽培地 熱帯 パパイア 熱帯 マルメロ 暖地, 寒冷地 ・・・ ・・・ マンゴー 熱帯 アンズ 寒冷地 上位 20,000 件を評価 アセロラ 列インデックスを持たない列でフィルターされた場合、 現在の並び順に従い上位から 20,000 件のアイテムを評価 フィルターに適合する アイテムがある フィルターに適合する アイテムがない ・・・ ・・・ モモ 暖地, 寒冷地 ライチ 熱帯 ・・・ M365VIRTUALMARATHON.COM #M365VM

インデックスが付かない列の動作 アイテム 栽培地 アセロラ 熱帯 パパイア 熱帯 マルメロ 暖地, 寒冷地 ・・・ ・・・ マンゴー 熱帯 アンズ 寒冷地 ・・・ モモ 暖地, 寒冷地 ライチ 熱帯 ・・・ M365VIRTUALMARATHON.COM 次の 20,000 件を評価 ・・・ 「その他のアイテムを読み込む」をクリックすると 次の 20,000 件のアイテムを評価する 以降、アイテムが見つかるまで繰り返し #M365VM

インデックスが付かない列の内部処理 RenderListDataAsStream • 上位から 5,000 件ずつに分割し フィルターを実行 • 一度に 4 回リクエストを送り 5,000 x 4 = 20,000 件 を 処理するようになっている • 4 回のリクエストを待たずに アイテムが見つかれば、 それ以降は実行されない インデックスが付かない列の フィルター処理は 5,000 件ずつ 4 回に分けて 処理が実行される M365VIRTUALMARATHON.COM #M365VM

インデックス付きの列を組み合わせたフィルター 複数のインデックス付きの列を利用したフィルターは ふたつ目の列の条件ではインデックスが有効にはたらかない ひとつ目の フィルター M365VIRTUALMARATHON.COM ふたつ目の フィルター #M365VM

インデックス付きの列を組み合わせたフィルター 複数のインデックス付きの列を利用したフィルターは ふたつ目の列の条件ではインデックスが有効にはたらかない ひとつ目のフィルターに 該当したアイテム数 ふたつ目のフィルターの動作 5,000 件以下 フィルターした結果が すぐに表示される 5,000 件より多い ひと目のフィルターの結果を 上位から 20,000 件ずつ評価する インデックス付きの列を 複数組み合わせたフィルター インデックスが効いていない M365VIRTUALMARATHON.COM #M365VM

インデックス付きの列を組み合わせたフィルター アイテム アセロラ パパイア アイテム ひとつ目の フィルター アセロラ パパイア マルメロ マルメロ ・・・ ・・・ ・・・ ・・・ マンゴー アンズ ふたつ目の フィルター 30,000 件まで マンゴー 絞り込み アンズ ・・・ ・・・ ・・・ ・・・ ひとつ目のフィルターで 20,000 件以内に 絞れていると ユーザーには易しい ※ 理想は 5,000 件以下 上位 20,000 件を 対象に絞り込み 下位 10,000 件は、「その他のアイテムを読み込む」が クリックされた後にあらためて探索する モモ ライチ ・・・ M365VIRTUALMARATHON.COM #M365VM

フィルターの順番を確認するには URLのパラメータ • ビュー上で操作を行った場合 • フィルターの条件は次のパラメーター • • • FilterField1, FilterValue1, FilterType1 FilterField2, FilterValue2, FilterType2 FilterField3, ビューの設定画面 • ビューとして保存した場合 • フィルターの設定の上の方で指定された列が ひとつ目のフィルター条件 • 番号の若い方がひとつ目のフィルター条件 M365VIRTUALMARATHON.COM #M365VM

ひとつのインデックス付き列で並び替えとフィルター 同じ列で並び替えとフィルターを同時に行う場合 インデックスが有効にはたらかない インデックス付き列に 並び替えと フィルターを 同時に適用 M365VIRTUALMARATHON.COM #M365VM

フィルター条件の AND と OR AND 条件 • 複数のインデックス付きの列を組み合わせたフィルターは、 ふたつ目の列の条件ではインデックスが有効にはたらかない • ビューの画面から操作した場合は AND 条件になる OR 条件 • フィルター順などに関わらず、インデックスが効かない • ビューの設定画面からのみ設定可能 M365VIRTUALMARATHON.COM #M365VM

列インデックスが有効にはたらかないパターン • インデックスが付いていない列での並び替えやフィルター • 複数のインデックス付き列を組み合わせたフィルター • ひとつのインデックス付き列で同時に並び替えとフィルター • OR 条件のフィルター • できるだけこのパターンを避けて利用できると良い • モダンリストなら「その他のアイテムを読み込む」が利用できる M365VIRTUALMARATHON.COM #M365VM

ここまでのまとめ 列インデックスが有効にはたらかないパターンを知っておくと ◎ インデックス付きの列を複数組み合わせたフィルター条件では、 条件の指定順に注意。ひとつ目の条件で出来るだけ対象を絞り込む モダンリストの「その他のアイテムを読み込む」を活用 M365VIRTUALMARATHON.COM #M365VM

調査結果から導くリスト利用の極意 一、ビューは、必ずインデックス付きの列で並び替えせよ 一、目的のアイテムを探すには、フィルターを最大限活用せよ 一、フィルターは、できる限りインデックス付き列を利用せよ 一、複数のインデックス付き列を組み合わせたフィルターでは、 ひとつ目のフィルターで、できる限り数を絞り込むよう意識せよ 一、モダンリストの「その他のアイテムを読み込む」を活用せよ M365VIRTUALMARATHON.COM #M365VM

フィルター ウィンドウを利用しよう フィルター ウィンドウ • 複数の値で絞り込んでいくのに便利 • 簡単に素早くアイテムを絞り込んで探すことができる [フィルターウィンドウに固定] でフィルターを追加 1 4 2 3 M365VIRTUALMARATHON.COM #M365VM

MICROSOFT 365 VIRTUAL MARATHON 2022 MAY, 4. – 6. 2022 まとめ

5,000 件問題は列インデックスとモダンリストで対処しよう SharePoint の 5,000 件問題は、負荷軽減のために今も存在する アイテム数が多くなった場合は、列インデックスの利用が大切 使いこなすためには、モダンリストの動作を理解する M365VIRTUALMARATHON.COM #M365VM

MICROSOFT 365 VIRTUAL MARATHON 2022 ボーナストラック MAY, 4. – 6. 2022

ルックアップ列（参照列） 大量のデータを対象としたルックアップも動作する 検索で探す ルックアップ対象列に インデックスを 作成しておく M365VIRTUALMARATHON.COM #M365VM

ルックアップ列（参照列） 大量のデータを対象としたルックアップも動作する アイテム 検索で探す インデックス付き列の参照 A AAA AB ABA ABB BA • 参照先のアイテムを文字コード順にソート 25,000 件ずつ探索 AA • 「その他の結果」がクリックされるたびに 上から 25,000 件ずつ探索 • リストの最後まで同じ動作を繰り返し ポイント ・・・ ・・・ • 対象の列にインデックスを設定する XXX • 「その他の結果」の動作を理解する XYY M365VIRTUALMARATHON.COM XYZ #M365VM

グループ化と合計 グループ化や合計の機能は 5,000 件を超えると上手く動作しない 5,000 件以下に フィルターしておくと グループ化できる M365VIRTUALMARATHON.COM #M365VM

インデックス付き選択肢列への選択肢の追加 インデックス付きの選択肢列 • アイテム数が 20,000 件を超える場合、インデックス付きの 選択肢列への選択肢の追加ができなくなる • 選択肢を追加するには、対象列の列インデックスを削除する • 選択肢追加後に、再度インデックスを作成する • 実運用時には、遭遇しそうな動作 M365VIRTUALMARATHON.COM #M365VM

権限の継承 サイト 上位の権限を 引継ぐ 権限の継承を中止する 100,000 件を超えた アイテムがあると 権限の継承の中止はできない （再継承もできない） リスト 上位の権限を 引継ぐ アイテム M365VIRTUALMARATHON.COM #M365VM

5,000 件問題と関係なく動作する機能 Excel へエクスポート Power Query Access リンクテーブル リストに保存されたデータの集計や分析には、 Excel や Power BI、Access が利用できる M365VIRTUALMARATHON.COM #M365VM

Power Apps の委任 委任できるはずの関数でデータ取得時にエラーになる場合がある M365VIRTUALMARATHON.COM #M365VM

Power Apps の委任 委任するときはインデックスも同時に考える 委任対象の列に インデックスを 作成しておく M365VIRTUALMARATHON.COM #M365VM

Power Automate のアイテム取得 リストからアイテムを取得するときにエラーになる場合がある M365VIRTUALMARATHON.COM #M365VM

Power Automate のアイテム取得 しきい値のエラーが出た場合はインデックスを確認 操作対象の列に インデックスを 作成しておく M365VIRTUALMARATHON.COM #M365VM

MICROSOFT 365 VIRTUAL MARATHON 2022 MAY, 4. – 6. 2022 まとめ

このセッションで伝えたかったこと 5,000 件問題は正しく怖がろう モダンリストは良いぞ！ M365VIRTUALMARATHON.COM #M365VM

MICROSOFT 365 VIRTUAL MARATHON 2022 アンケートにご協力ください スピーカーおよびイベント への感想やご意見 https://forms.office.com/r/qCXhcZZUgU MAY, 4. – 6. 2022

• https://forms.office.com/r/qCXhcZZUgU

リファレンス • Manage large lists and libraries • SharePoint limits - Service Descriptions | Microsoft Docs • Living Large with Large Lists and Large Libraries | Microsoft Docs M365VIRTUALMARATHON.COM #M365VM

• https://support.microsoft.com/en-us/office/manage-large-lists-and-libraries-b8588dae-9387-48c2-9248-c24122f07c59
• https://docs.microsoft.com/en-us/office365/servicedescriptions/sharepoint-online-service-description/sharepoint-online-limits
• https://docs.microsoft.com/en-us/microsoft-365/community/large-lists-large-libraries-in-sharepoint