Bagaimana menghindari pemindaian tabel di SQL Server dalam situasi ini

Ada dua tabel, Costs dan Logs. Data di tabel Costs bisa jutaan baris, dan di tabel Logs bisa miliaran baris.

Saya perlu memperbarui kolom CostBy di tabel Costs dalam tugas layanan di lingkungan produksi dalam 100 catatan setiap kali dijalankan.

CREATE TABLE Cost
(
    C_PK uniqueidentifier primary key not null,
    C_CostBy varchar(3) not null
)

CREATE TABLE Logs
(
    L_PK uniqueidentifier primary key not null,
    L_ParentTable varchar(255) not null,  -- Table Cost and other table's name
    L_ParentID uniqueidentifier not null, -- Cost's pk and other table's pk
    L_Event varchar(3) not null, -- Part are 'ADD' and other event types
    L_User varchar(3) not null 
 )

CREATE NONCLUSTERED INDEX [L_ParentID] 
    ON [dbo].[Costs] ([L_ParentID] ASC)

Berikut ini pernyataan pembaruan asli:

UPDATE TOP(100) Costs
SET CostBy = ISNULL(L_User, '~UK')
FROM Costs
LEFT JOIN Logs ON L_ParentID = C_PK AND L_Event = 'ADD'
WHERE CostBy = ''

Namun, pernyataan tersebut memperkenalkan masalah kinerja besar-besaran, tingginya biaya pemindaian tabel di Costs tabel.

Pertanyaan saya adalah bagaimana menghindari pemindaian tabel di tabel Costs atau bagaimana cara mengoptimalkan pernyataan pembaruan?

Terima kasih sebelumnya.


sql-server database-performance query-performance full-table-scan
person Jimmy Wang    schedule 27.03.2020    source sumber
comment
T-SQL yang Anda tampilkan tidak dapat dikompilasi, harap kirimkan kode yang benar. Selain itu, beberapa baris data sampel juga bagus.   -  person Thailo    schedule 27.03.2020
comment
Apa alasan memperbarui 100 baris teratas saja? Apakah itu persyaratan fungsional, atau apakah Anda melakukan ini untuk memperbarui beberapa hasil sekaligus/melakukan beberapa panggilan?   -  person JohnLBevan    schedule 27.03.2020
comment
Apakah ini tugas yang hanya dilakukan sekali saja, atau apakah kode ini akan digunakan secara teratur (yaitu apakah ada persyaratan untuk memperbarui tabel Biaya setiap 10 menit/setiap kali seseorang melakukan suatu tindakan/dll, atau apakah ini hanya mengoreksi beberapa data yang tidak valid di tugas ad-hoc?   -  person JohnLBevan    schedule 27.03.2020

Jawaban (1)


arrow_upward
0
arrow_downward

Anda mungkin ingin mencoba yang berikut ini.

Pertama, buat indeks pada Logs, termasuk semua kolom yang relevan:

CREATE INDEX ix ON Logs 
(
  L_Parent_ID -- join condition, variable
)
INCLUDE 
(
  L_User -- no filter condition, but you use it your update
)
WHERE 
(
  L_Event = 'ADD' -- join condition, constant
)

Jika ini adalah indeks unik, mis. hanya satu baris yang akan ada dengan acara ADD untuk ID induk tertentu, pastikan untuk menjadikan ini indeks unik karena dapat meningkatkan kinerja secara signifikan.

Kedua, dan ini adalah situasi untung-untungan, Anda dapat mencoba dengan indeks pada Biaya (CostBy) karena Anda hanya mencari nilai CostBy kosong untuk diperbarui. Indeks ini perlu diperbarui berdasarkan kueri Anda karena indeks ini memperbaruinya, sehingga mungkin memperlambat kueri Anda alih-alih mempercepatnya. Hal ini tergantung pada sejumlah faktor.

Jika Anda memiliki lisensi perusahaan, coba keduanya dengan WITH (DATA_COMPRESSION = PAGE), ini dapat meningkatkan waktu IO secara signifikan dengan mengorbankan CPU. Tergantung mana yang menjadi hambatan Anda.

Selain itu, bergantung pada sifat data Anda, memperbarui statistik dapat meningkatkan kueri Anda. Jika ada jumlah baris yang tidak proporsional dengan CostBy = '' dengan nilai lain di sana, Anda dapat memanfaatkan statistik lengkap pada bidang tersebut. Pertimbangkan NORECOMPUTE jika Anda hanya memerlukannya untuk kueri khusus ini, kali ini.

CREATE STATISTICS st_Costs_CostBy
ON Costs (CostBy)  
WITH FULLSCAN, NORECOMPUTE;
person MarcinJ    schedule 27.03.2020