Loại bỏ `DISTINCT` và `CROSS APPLY` bằng Window Function (`ROW_NUMBER`)

by

Bối cảnh

Có một kiểu query mà mình gặp đi gặp lại trong các hệ thống vận hành thực tế — đặc biệt là các hệ thống có lịch sử thay đổi dữ liệu theo thời gian (audit log, trạng thái hồ sơ, version record…).

Team thường cần “lấy bản ghi mới nhất” cho mỗi nhóm dữ liệu, ví dụ: mỗi user – mỗi document – mỗi trạng thái.

Ban đầu, query chạy ổn. Dữ liệu ít thì mọi thứ đều “có vẻ đúng”. Nhưng khi data bắt đầu phình ra (vài trăm nghìn → vài triệu record), performance bắt đầu tụt dốc không phanh.

Và pattern quen thuộc mình thấy là:

  • SELECT DISTINCT
  • Kết hợp với CROSS APPLY (SELECT TOP 1 ... ORDER BY ...)

Nhìn qua thì hợp lý. Nhưng thực tế bên dưới execution plan lại là một câu chuyện hoàn toàn khác.

Vấn đề

Giả sử bạn đang có một query kiểu như thế này (mình đã đổi tên bảng/column để đảm bảo bảo mật):

SELECT DISTINCT 
    u.partner_ref,
    u.doc_ref,
    u.state_code,
    latest_record.last_updated_at
FROM user_document_log u
CROSS APPLY (
    SELECT TOP 1 *
    FROM user_document_log sub
    WHERE sub.partner_ref = u.partner_ref
      AND sub.doc_ref = u.doc_ref
      AND sub.state_code = u.state_code
    ORDER BY sub.last_updated_at DESC
) latest_record
WHERE u.is_archived = 0

Nghe thì có vẻ đơn giản:

  • Với mỗi dòng u, tìm record mới nhất tương ứng
  • Sau đó dùng DISTINCT để loại trùng

Nhưng có 2 vấn đề cực lớn:

1. CROSS APPLY = vòng lặp ẩn (hidden loop)

Về bản chất, SQL Server sẽ:

  • Lấy từng dòng từ user_document_log
  • Với mỗi dòng, chạy lại subquery SELECT TOP 1 ... ORDER BY

=> Nếu có 1 triệu dòng → chạy subquery 1 triệu lần

Đây chính là dạng Nested Loop cực kỳ tốn tài nguyên.

2. DISTINCT = chi phí dọn rác cực lớn

Sau khi đã “bắn ra” một đống record trùng lặp từ bước trên, DISTINCT phải:

  • Dùng Hash Match (Aggregate) hoặc Sort
  • Gom toàn bộ dataset lại
  • Loại duplicate

=> Đây là bước cực nặng, thường nằm ở cuối execution plan
=> RAM + CPU đều bị ăn rất mạnh

Suy nghĩ

Khi nhìn execution plan kiểu này đủ nhiều, mình bắt đầu nhận ra một điều:

Vấn đề không nằm ở việc “lọc trùng”, mà nằm ở cách mình tạo ra dữ liệu trùng ngay từ đầu.

Tức là:

  • CROSS APPLY tạo ra quá nhiều dòng không cần thiết
  • DISTINCT chỉ là “lau dọn hậu quả”

Cách tối ưu hơn là:

Ngay từ đầu, chỉ lấy đúng 1 record cần thiết cho mỗi nhóm, thay vì lấy tất cả rồi lọc lại.

Và đây là lúc Window Function phát huy sức mạnh.

Giải pháp

Thay vì:

  • Lặp subquery (CROSS APPLY)
  • Rồi lọc trùng (DISTINCT)

Ta chuyển sang:

  • Xếp hạng dữ liệu bằng ROW_NUMBER()
  • Sau đó lọc rn = 1

Tức là chỉ quét 1 lần duy nhất (single pass).

Cách viết lại query

WITH RankedUserDoc AS (
    SELECT 
        ud.partner_ref,
        ud.doc_ref,
        ud.state_code,
        ud.last_updated_at,
        -- các field khác...

        ROW_NUMBER() OVER (
            PARTITION BY 
                ud.partner_ref,
                ud.doc_ref,
                ud.state_code
            ORDER BY 
                ud.last_updated_at DESC
        ) AS rn

    FROM user_document_log ud
    WHERE ud.is_archived = 0
)

SELECT 
    r.*
FROM RankedUserDoc r
JOIN other_table ot ON ...
WHERE r.rn = 1
  AND ... -- các điều kiện khác

Điều gì thay đổi?

  • Không còn CROSS APPLY → không còn loop N lần
  • Không còn DISTINCT → không còn bước sort/aggregate nặng
  • Chỉ còn:
  • 1 lần scan dữ liệu
  • 1 lần tính ROW_NUMBER()

Tại sao nhanh hơn?

ROW_NUMBER() hoạt động theo kiểu:

  • SQL Server có thể tận dụng index + partition
  • Xử lý theo dạng streaming / window
  • Không cần giữ toàn bộ dataset trong memory để deduplicate

=> Execution plan gọn hơn, predictable hơn, scale tốt hơn

Kết luận

Đây là một trong những refactor “nhỏ nhưng có võ”:

  • Code gọn hơn
  • Logic rõ ràng hơn
  • Performance cải thiện rõ rệt khi data lớn

Một rule đơn giản mình hay dùng:

Nếu bạn đang dùng DISTINCT để “chữa cháy”, khả năng cao bạn đang tạo ra dữ liệu dư thừa ở bước trước đó.

Và trong các bài toán “lấy record mới nhất theo group”, thì:

ROW_NUMBER() gần như luôn là lựa chọn sạch và scalable hơn so với CROSS APPLY + TOP 1.

Leave a Comment