Meningkatkan kinerja saat memesan berdasarkan kolom dari tabel yang digabungkan
Saya memiliki tabel induk yang berisi kunci asing ke tabel pencarian (contoh yang disederhanakan):
CREATE TABLE [dbo].[Parent] (
[Id] [uniqueidentifier] NOT NULL,
[LookupId] [uniqueidentifier] NULL
)
CREATE TABLE [dbo].[Lookup] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](64) NOT NULL
)
Dalam hal ini, Parenttabel memiliki lebih dari 10 juta baris dan Lookuptabel memiliki sekitar 5.000. ParentImplementasi sebenarnya memiliki beberapa referensi kunci asing ke tabel lain dan masing-masing kolom tersebut dapat berisi NULL.
Kedua tabel contoh memiliki indeks berkerumun unik untuk Idkolomnya, Parentmemiliki indeks tidak berkerumun untuk, LookupIddan Lookupmemiliki indeks tak berkerumun untuk Name.
Saya menjalankan kueri paged di mana saya ingin memasukkan nilai pencarian dalam hasil: -
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
Ini berjalan cepat, seperti halnya memesan dengan P.LookupId.
Namun, jika saya mencoba mengurutkan berdasarkan Name(atau bahkan L.Id), kueri berjalan jauh lebih lambat:
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
Rencana kueri untuk kueri kedua ada di sini: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD
Pertanyaan lain yang tampaknya terkait tampaknya melibatkan pengurutan berdasarkan kolom di tabel pertama yang dapat diselesaikan menggunakan indeks yang sesuai.
Saya mencoba membuat tampilan terindeks untuk kueri ini, namun, SQL Server tidak mengizinkan saya untuk mengindeks tampilan karena berisi LEFT JOIN yang saya perlukan karena LookupIdmungkin NULL dan jika saya menggunakan INNER JOIN, record tersebut akan dikecualikan.
Adakah cara untuk mengoptimalkan situasi ini?
EDIT
Jawaban Rob Farley (terima kasih!) Sangat bagus dan berfungsi sempurna untuk pertanyaan seperti yang saya tanyakan pada awalnya, di mana saya menyiratkan bahwa saya bergabung dengan satu tabel.
Karena itu, saya memiliki beberapa tabel seperti itu dan saya tidak dapat mendamaikan semua menggunakan INNER JOIN untuk menggunakan solusi itu.
Untuk saat ini saya telah mengerjakannya dengan menambahkan baris "NULL" ke tabel pencarian sehingga saya dapat menggunakan INNER JOIN tanpa kehilangan baris di sebelah kiri.
Dalam kasus saya, saya menggunakan uniqueidentifieridentitas, jadi saya membuat tampilan terindeks seperti ini:
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
Saya kemudian menambahkan baris ke Lookuptabel dengan nilai 00000000-0000-0000-0000-000000000000for Idsehingga selalu ada kecocokan di sebelah kanan gabungan.
Saya kemudian dapat membuat indeks pada tampilan itu sesuai kebutuhan.
Selain itu, karena saya tidak menggunakan Enterprise, saya merasa perlu menggunakan NOEXPANDpetunjuk untuk memastikan indeks tersebut digunakan:
SELECT *
FROM [ParentView]
WITH (NOEXPAND)
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
Jawaban
Mari kita mulai dengan memikirkan kueri pertama itu.
Anda bergabung antara Parent dan Lookup, tapi ini gabungan luar, jadi Parents tidak pernah dihapus dari hasil. Saya akan menebak bahwa Lookup.Id itu unik, jadi oleh karena itu, tidak ada Induk yang akan memiliki beberapa Lookup yang digabungkan dengannya.
Oleh karena itu, baris ke 50000 di Parent (diurutkan oleh Parent.Id) akan menjadi baris ke 50000 dalam hasil jika kita tidak memiliki klausa OFFSET.
Oleh karena itu, kueri bisa melewati 50000 baris untuk offset, lihat 50 baris berikutnya, dan gunakan ini untuk bergabung ke tabel Pencarian. Tidak masalah jika gabungan tidak menemukan apa pun, itu gabungan luar kiri dan itu hanya akan mengembalikan NULL.
Jika Anda mengurutkan menurut kolom berbeda di Induk, dan itu diindeks, itu bisa melewati 50000 baris dengan cepat.
Sekarang mari kita pertimbangkan kueri kedua.
Anda ingin 50000 baris yang Anda abaikan (oleh offset) menjadi 50000 pertama berdasarkan hasil gabungan. Baris 50000 tersebut mungkin menyertakan beberapa yang NULL, di mana nilai Parent.LookupId tidak ada di tabel Lookup. Bahkan jika Anda memiliki indeks yang bagus di Parent.LookupId, Anda mungkin perlu melibatkan sebagian besar baris, karena kecuali Anda menemukan 50050 baris yang tidak berhasil digabungkan, Anda harus melanjutkan. Bahkan 50050 adalah cara lebih dari 50 baris yang Anda gabungkan di kueri pertama.
Sekarang, jika Anda memiliki kunci asing di tempat maka semuanya mungkin sedikit berbeda. Kemudian, mesin SQL harus mengetahui bahwa jika memiliki nilai sama sekali, maka Lookup.Name tidak akan null. Jadi secara teoritis dapat dimulai dengan menemukan yang nol, untuk melihat apakah ada 50000 dari mereka. Tapi itu masih agak sulit, dan mesin SQL tidak mungkin menghasilkan rencana seperti ini.
Tapi Anda bisa.
Jadi untuk mengatasi kinerja kueri kedua, saya akan melakukan beberapa hal.
Mulailah dengan mempertimbangkan yang bukan nol. Itu berarti baris yang merupakan bagian dari gabungan dalam. Anda dapat membuat tampilan terindeks ini, sehingga Anda dapat memiliki indeks yang sesuai dengan urutan yang Anda inginkan.
Tetapi Anda juga akan membutuhkan yang di mana Parent.LookupID adalah null - kecuali untuk yang ini, Anda tidak perlu bergabung sama sekali.
Jika Anda melakukan UNION SEMUA di kedua set ini (dan mungkin menyertakan kolom konstan di keduanya, untuk memastikan bahwa baris NULL muncul sebelum yang NOT NULL dalam urutan Anda), Anda akan melihat beberapa peningkatan.
Sesuatu seperti ini:
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;
Mudah-mudahan rencana Anda akan menyertakan operator Merge Join (Concatenation), sehingga hanya menarik baris yang dibutuhkan dari Index Scan pada tampilan yang diindeks (dalam urutan Nama) dan Index Seek on Parent (untuk LookupIDs).