programming

Cách cải thiện hiệu suất xóa trong SQL Server

Hãy khám phá bốn tùy chọn--hai xấu và hai tốt--để xóa một triệu bản ghi trở lên khỏi một bảng SQL Server

Mike Wolfe

Ngày 24 tháng 5 năm 2022 • 4 phút đọc

Bạn đã bao giờ phải xóa vài triệu bản ghi khỏi cơ sở dữ liệu SQL Server chưa?

Nếu bạn từng rơi vào tình huống như vậy, có một số điều bạn nên biết về những gì bạn nên–và không nên–làm

Hãy đi sâu vào ngay

Làm thế nào để KHÔNG làm điều đó

TÙY CHỌN BAD #1- Xóa khỏi bảng được liên kết trong MS Access

Đây là cách tồi tệ nhất để xóa một số lượng lớn các bản ghi

Nếu bạn thực hiện truy vấn DELETE * FROM MyLinkedSqlServerTable trong Access, Access sẽ

Chạy truy vấn chọn để trả về tất cả các giá trị khóa chính trong bảng
Bắt đầu giao dịch với SQL Server
Gửi một lệnh XÓA riêng cho từng hàng trong bảng
Hỏi xem bạn có chắc chắn muốn xóa "xxx số hàng"
Nếu bạn bấm OK, nó sẽ thực hiện giao dịch, nếu không, nó sẽ khôi phục giao dịch

Điều này mất mãi mãi. Ngoài ra, khi bạn vượt quá một số hàng nhất định, Access sẽ cảnh báo bạn rằng nếu tiếp tục, bạn sẽ không có tùy chọn hủy thao tác

TÙY CHỌN BAD #2- Sử dụng Câu lệnh XÓA T-SQL để xóa hàng triệu hàng cùng một lúc

Sử dụng SSMS hoặc truy vấn chuyển tiếp trong Access đều là những tùy chọn tốt hơn nhiều so với việc xóa một số lượng lớn bản ghi khỏi bảng SQL Server được liên kết

Tuy nhiên, chỉ cần thực hiện câu lệnh trên máy chủ không phải là thuốc chữa bách bệnh. Để đảm bảo độ bền trong trường hợp bạn quyết định hủy DELETE của mình, trước tiên, SQL Server sẽ ghi tất cả các thao tác vào nhật ký trước khi thực hiện thao tác. Điều này cần có thời gian

Nó cũng đặt các khóa tương đối mạnh đối với các bản ghi bị xóa, điều này có thể ảnh hưởng đến các quy trình khác có thể đang cố đọc các bản ghi trong bảng bị xóa. Do đó, nó có thể ảnh hưởng tiêu cực đến hiệu suất của toàn bộ máy chủ

Làm thế nào để làm nó

LỰA CHỌN TỐT #1. CẮT bảng

Cho đến nay, cách nhanh nhất để xóa một loạt bản ghi là sử dụng câu lệnh TRUNCATE TABLE

Điều này nhanh hơn nhiều so với câu lệnh DELETE vì nó không ghi lại bất kỳ thao tác xóa cấp hàng nào. Tuy nhiên, bạn chỉ có thể sử dụng TRUNCATE TABLE

Để xóa TẤT CẢ các bản ghi trong bảng
Trên các bảng không có mối quan hệ toàn vẹn tham chiếu với các bảng khác
Trên các bảng không liên quan đến sao chép

Nếu đó là tình huống của bạn, thì TRUNCATE TABLE là cách tốt nhất.

Nếu không, hãy đọc tiếp để có lựa chọn tốt nhất tiếp theo

LỰA CHỌN TỐT #2. XÓA Bản ghi theo lô

Nếu bạn không thể sử dụng TRUNCATE TABLE, lựa chọn tốt nhất của bạn là xóa các bản ghi theo đợt

Với phương pháp này, bạn thực hiện một số câu lệnh DELETE. Mỗi câu lệnh xóa một tập hợp con của tổng số bản ghi bạn muốn xóa. Ví dụ: thay vì xóa một triệu bản ghi bằng một câu lệnh DELETE, bạn sẽ xóa 100.000 bản ghi mười lần

Thật không may, điều này hơi khó hơn bạn tưởng, vì bạn không thể sử dụng trực tiếp mệnh đề TOP trong câu lệnh DELETE

Giới hạn và hạn chế
Khi TOP được sử dụng với DELETE, các hàng được tham chiếu không được sắp xếp theo bất kỳ thứ tự nào và mệnh đề ORDER BY không thể được chỉ định trực tiếp trong câu lệnh này. Nếu bạn cần sử dụng TOP để xóa các hàng theo thứ tự thời gian có ý nghĩa, bạn phải sử dụng TOP cùng với mệnh đề ORDER BY trong câu lệnh chọn phụ.

Tôi sẽ trình bày chi tiết cách bạn có thể "sử dụng TOP cùng với mệnh đề ORDER BY trong một câu lệnh chọn phụ" trong một bài viết trong tương lai.

CẬP NHẬT [2022-08-31]. "Bài viết trong tương lai" có thể được tìm thấy ở đây, CÁCH. Tự động xóa hàng loạt bản ghi máy chủ SQL

Lưu ý chia tay

Xóa hàng triệu bản ghi không phải là điều bạn nên làm thường xuyên

Trong năm năm qua, tôi đã phải thực hiện chính xác hai lần trên cơ sở dữ liệu sản xuất. Cả hai trường hợp đều xử lý các ứng dụng ghi nhật ký tự động kết thúc ở trạng thái tạo ra quá nhiều dữ liệu nhật ký. Một là ứng dụng giám sát mạng. Ứng dụng còn lại là ứng dụng giám sát Nhật ký sự kiện Windows

Vì vậy, mặc dù việc xóa một số lượng lớn hàng như vậy cùng một lúc tương đối hiếm nhưng có những lý do chính đáng khiến bạn có thể cần thực hiện việc đó

Bài viết tham khảo

LÀM THẾ NÀO ĐỂ. Tự động xóa hàng loạt bản ghi máy chủ SQL

Sử dụng một câu lệnh DELETE để xóa hơn 20 triệu bản ghi khỏi SQL Server là một ý tưởng tồi. Đây là một cách tiếp cận tốt hơn

Làm cách nào để tăng tốc SQL Server DELETE?

Cách xóa nhanh hàng triệu hàng bằng SQL .

Loại bỏ tất cả các hàng nhanh chóng với truncate

Sử dụng tạo-bảng-như-chọn để xóa một phần lớn dữ liệu

Bỏ hoặc cắt bớt phân vùng

Sử dụng di chuyển bảng đã lọc

Làm cách nào để xóa 1 triệu hàng trong SQL Server?

Cho đến nay, cách nhanh nhất để xóa một loạt bản ghi là sử dụng câu lệnh TRUNCATE TABLE . Điều này nhanh hơn nhiều so với câu lệnh DELETE vì nó không ghi lại bất kỳ thao tác xóa cấp hàng nào. Tuy nhiên, bạn chỉ có thể sử dụng TRUNCATE TABLE. Để xóa TẤT CẢ các bản ghi trong bảng.

Index có thể tăng tốc độ XÓA không?

Vì vậy, việc có nhiều chỉ mục có thể tăng tốc các câu lệnh được chọn, nhưng làm chậm quá trình chèn, cập nhật và xóa . Ghi chú. Cập nhật và xóa với mệnh đề WHERE có thể sử dụng chỉ mục để quét, ngay cả khi cột được lập chỉ mục đang được cập nhật.

Tại sao XÓA chậm hơn chèn?

Vì cần hoàn tác . Khi chúng tôi khôi phục cơ sở dữ liệu sử dụng thông tin trong không gian bảng UNDO để đảo ngược câu lệnh của chúng tôi. Đối với INSERT, hành động hoàn tác là xóa, vì vậy tất cả những gì nó cần là ROWID của hàng được chèn.