Miglioramento delle prestazioni quando si ordina in base a una colonna di una tabella unita
Ho una tabella genitore che contiene una chiave esterna per una tabella di ricerca (esempio semplificato):
CREATE TABLE [dbo].[Parent] (
[Id] [uniqueidentifier] NOT NULL,
[LookupId] [uniqueidentifier] NULL
)
CREATE TABLE [dbo].[Lookup] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](64) NOT NULL
)
In questo caso, la Parent
tabella ha oltre 10 milioni di righe e la Lookup
tabella ne ha circa 5.000. L' Parent
implementazione reale ha diversi riferimenti di chiavi esterne di questo tipo ad altre tabelle e ciascuna di queste colonne può contenere NULL.
Entrambe le tabelle di esempio hanno indici cluster univoci per le Id
colonne, Parent
hanno un indice non cluster per LookupId
e Lookup
un indice non cluster per Name
.
Sto eseguendo una query di pagina in cui desidero includere il valore di ricerca nei risultati: -
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
Funziona rapidamente, così come l'ordinazione di P.LookupId
.
Se, tuttavia, provo a ordinare per Name
(o anche L.Id
), la query viene eseguita molto più lentamente:
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
Il piano di query per la seconda query è qui: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD
Altre domande apparentemente correlate sembrano coinvolgere l'ordinamento per colonne nella prima tabella che potrebbe essere risolto utilizzando un indice appropriato.
Ho provato a creare una vista indicizzata per questa query, tuttavia, SQL Server non mi consente di indicizzare la vista perché contiene un LEFT JOIN di cui ho bisogno perché LookupId
potrebbe essere NULL e se utilizzo un INNER JOIN quei record sarebbero esclusi.
C'è un modo per ottimizzare questa situazione?
MODIFICARE
La risposta di Rob Farley (grazie!) È fantastica e funziona perfettamente per la domanda come l'ho posta originariamente, in cui sottintendevo che stavo partecipando a un unico tavolo.
Così com'è, ho più tabelle di questo tipo e non sono stato in grado di riconciliare tutte utilizzando INNER JOIN per utilizzare quella soluzione.
Per il momento ho aggirato questo problema aggiungendo una riga "NULL" alle tabelle di ricerca in modo da poter utilizzare un INNER JOIN senza perdere nessuna riga a sinistra.
Nel mio caso utilizzo le uniqueidentifier
identità, quindi creo una vista indicizzata come questa:
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
Quindi aggiungo una riga alla Lookup
tabella con un valore di 00000000-0000-0000-0000-000000000000
per Id
quindi c'è sempre una corrispondenza a destra del join.
Posso quindi creare indici su quella vista secondo necessità.
Inoltre, poiché non sto utilizzando Enterprise, ho scoperto di dover utilizzare il NOEXPAND
suggerimento per assicurarmi che quegli indici vengano utilizzati:
SELECT *
FROM [ParentView]
WITH (NOEXPAND)
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
Risposte
Cominciamo pensando a quella prima query.
Ti stai unendo tra Parent e Lookup, ma è un outer join, quindi i Parent non vengono mai rimossi dai risultati. Immagino che Lookup.Id sia unico, quindi nessun genitore avrà più ricerche a cui si unisce.
Pertanto, la 50000esima riga in Parent (ordinata per Parent.Id) sarà la 50000th riga nei risultati se non abbiamo la clausola OFFSET.
Pertanto, la query può spostarsi oltre le 50.000 righe per l'offset, esaminare le successive 50 righe e utilizzarle per unirsi alla tabella di ricerca. Non importa se il join non trova nulla, è un outer join sinistro e restituirà semplicemente NULL.
Se ordini in base a una colonna diversa in Parent, che è indicizzata, può spostarsi oltre quelle 50000 righe altrettanto rapidamente.
Consideriamo ora la seconda query.
Vuoi che le 50000 righe che ignori (per l'offset) siano le prime 50000 in base ai risultati del join. Quelle 50000 righe possono includere alcune che sono NULL, dove il valore Parent.LookupId non esiste nella tabella di ricerca. Anche se hai un buon indice su Parent.LookupId, probabilmente dovrai coinvolgere la maggior parte delle righe, perché a meno che tu non trovi 50050 righe che non si uniscono correttamente, dovrai andare avanti. Anche 50050 è molto più delle 50 righe a cui unisci nella prima query.
Ora, se hai una chiave esterna in atto, le cose potrebbero essere leggermente diverse. Quindi, il motore SQL dovrebbe sapere che se ha un valore, Lookup.Name non sarà nullo. Quindi potrebbe teoricamente iniziare trovando quelli che sono nulli, per vedere se ce ne sono 50000. Ma è ancora un po 'complicato ed è improbabile che il motore SQL produca un piano come questo.
Ma potresti.
Quindi, per risolvere le prestazioni della seconda query, farei alcune cose.
Inizia considerando quelli che non sono nulli. Ciò significa che le righe che fanno parte di un inner join. Puoi creare una vista indicizzata su questo, in modo da avere un indice nell'ordine che desideri.
Ma avrai anche bisogno di quelli in cui Parent.LookupID è nullo, tranne che per questi non hai affatto bisogno del join.
Se esegui UNION ALL su questi due set (e magari includi una colonna costante in entrambi, per assicurarti che le righe NULL appaiano prima di quelle NOT NULL nel tuo ordine), dovresti essere in grado di vedere qualche miglioramento.
Qualcosa come questo:
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;
Si spera che il tuo piano includa un operatore Merge Join (Concatenation), in modo da estrarre solo le righe di cui ha bisogno da una Index Scan nella vista indicizzata (in ordine di nome) e un Index Seek su Parent (per LookupIDs).