結合されたテーブルの列で注文するときのパフォーマンスの向上
ルックアップテーブルへの外部キーを含む親テーブルがあります（簡略化された例）：
CREATE TABLE [dbo].[Parent] (
[Id] [uniqueidentifier] NOT NULL,
[LookupId] [uniqueidentifier] NULL
)
CREATE TABLE [dbo].[Lookup] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](64) NOT NULL
)
この場合、Parentテーブルには1,000万を超える行があり、Lookupテーブルには約5,000があります。実際のParent実装には、他のテーブルへのそのような外部キー参照がいくつかあり、それらの各列にはNULLが含まれる場合があります。
両方のサンプルテーブルには、Id列に一意のクラスター化インデックスParentがあり、の非クラスター化インデックスがLookupIdありLookup、の非クラスター化インデックスがありNameます。
結果にルックアップ値を含めたいページクエリを実行しています：-
SELECT
P.Id,
L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id
ORDER BY P.Id
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY
による注文と同様に、これは迅速に実行されP.LookupIdます。
ただし、by Name（またはL.Id）で注文しようとすると、クエリの実行速度が大幅に低下します。
SELECT
P.Id,
L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id
ORDER BY L.Name
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY
2番目のクエリのクエリプランは次のとおりです。 https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD
他の一見関連する質問には、適切なインデックスを使用して解決できる最初のテーブルの列による順序付けが含まれているようです。
このクエリのインデックス付きビューを作成しようとしましたが、SQL Serverでは、LookupIdNULLの可能性があるため必要なLEFT JOINが含まれているため、ビューにインデックスを付けることができません。INNERJOINを使用すると、これらのレコードが除外されます。
この状況を最適化する方法はありますか？
編集
Rob Farleyの答え（ありがとう！）は素晴らしく、私が最初に尋ねた質問に対して完全に機能します。私は1つのテーブルに参加していることを暗示していました。
現状では、そのようなテーブルが複数あり、そのソリューションを使用するためにINNERJOINを使用してすべてを調整することができませんでした。
今のところ、ルックアップテーブルに「NULL」行を追加してこれを回避し、左側の行を失うことなくINNERJOINを使用できるようにしました。
私の場合uniqueidentifier、IDを使用するので、次のようなインデックス付きビューを作成します。
CREATE VIEW [dbo].[ParentView]
WITH SCHEMABINDING
AS
SELECT
P.Id,
L.Name
FROM [dbo].Parent P
INNER JOIN [dbo].Lookup L ON ISNULL(P.LookupId, '00000000-0000-0000-0000-000000000000') = L.Id
次にLookup、値が00000000-0000-0000-0000-000000000000forの行をテーブルに追加してId、結合の右側に常に一致するようにします。
その後、必要に応じてそのビューにインデックスを作成できます。
また、Enterpriseを使用していないNOEXPANDため、これらのインデックスが確実に使用されるようにヒントを使用する必要があることがわかりました。
SELECT *
FROM [ParentView]
WITH (NOEXPAND)
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
回答
その最初のクエリについて考えることから始めましょう。
親とルックアップの間で結合していますが、これは外部結合であるため、親が結果から削除されることはありません。Lookup.Idは一意であると推測します。したがって、親が結合する複数のルックアップを持つことはありません。
したがって、OFFSET句がない場合、Parentの50000番目の行（Parent.Idの順序）が結果の50000番目の行になります。
したがって、クエリはオフセットの50000行を超えて移動し、次の50行を調べ、これを使用してルックアップテーブルに結合できます。結合が何も見つからないかどうかは関係ありません。それは左外部結合であり、NULLを返すだけです。
親の別の列で注文し、それがインデックスに登録されている場合、それらの50000行を同じようにすばやく移動できます。
次に、2番目のクエリについて考えてみましょう。
結合の結果に基づいて、（オフセットによって）無視する50000行を最初の50000行にする必要があります。これらの50000行には、Parent.LookupId値がルックアップテーブルに存在しないNULLの行が含まれる場合があります。Parent.LookupIdに適切なインデックスがある場合でも、正常に結合されない50050行が見つからない限り、続行する必要があるため、おそらくほとんどの行を含める必要があります。50050でさえ、最初のクエリで結合する50行をはるかに上回っています。
さて、外部キーを配置している場合は、状況が少し異なる可能性があります。次に、SQLエンジンは、値がある場合、Lookup.Nameがnullにならないことを認識している必要があります。したがって、理論的には、nullであるものを見つけて、50000個あるかどうかを確認することから始めることができます。しかし、それはまだ少し難しいので、SQLエンジンがこのような計画を作成する可能性は低いです。
しかし、できます。
したがって、2番目のクエリのパフォーマンスを解決するために、いくつかのことを行います。
nullではないものを検討することから始めます。これは、内部結合の一部である行を意味します。これについてインデックス付きのビューを作成できるため、必要な順序でインデックスを作成できます。
ただし、Parent.LookupIDがnullの場合も必要になります。ただし、これらの場合を除いて、結合はまったく必要ありません。
これらの2つのセット全体でUNIONALLを実行すると（そして、おそらく両方に定数列を含めて、NULL行がNOT NULL行の前に表示されるようにするため）、いくつかの改善が見られるはずです。
このようなもの：
SELECT ID, Name
FROM
(
SELECT i.ID, i.Name, 2 as SetNumber
FROM dbo.MyIndexedView i
UNION ALL
SELECT p.ID, NULL, 1 as SetNumber
FROM dbo.Parent p
WHERE p.LookupID IS NULL
) u
ORDER BY u.SetNumber, u.Name
OFFSET 50000 ROWS FETCH NEXT 50 ROWS ONLY;
うまくいけば、プランにマージ結合（連結）演算子が含まれるため、インデックス付きビューのインデックススキャン（名前順）と親のインデックスシーク（LookupIDの場合）から必要な行のみがプルされます。