Một số kinh nghiệm khi optimize sql query

Thực ra thế này, tuần này mình optimize về phần API tìm kiếm và đến giờ vẫn chưa xong =)), và một số kinh nghiệm mình muốn chia sẽ khi optimize sql query.

Sử dụng versioning và hậu quả

GIới thiệu hoàn cảnh tí, về database của cái project đang làm thì nó lưu kiểu versioning (Giống như kiểu lưu history lại nhưng hiện tại nó lưu trong 1 table) nó có dạng như thế này:

Create Table product.Products(
    ProductId BIGINT,
    VersionNumber INT,
    ProductName NVARCHAR(100),
    VersionComment NVARCHAR(100)
    ……
)

Đại loại là thế khi này cặp Key là (ProductId và VersionNumber) @@ khi làm việc hoặc lấy danh sách thì phải đi lấy thằng latest version, khi update thì nó lại insert thêm một dòng vào bảng đó.
Dữ liệu nó sẽ hiển thị như thế này

alt text

 

Mới nhìn thôi cũng đã thấy củ chuối rồi hix hix, rất tiếc là structure này PO(ông là người Mỹ) đưa ra và product chạy được hơn 1 năm rồi, Vẫn chạy ngon lành, nhưng khi test dữ liệu lớn ( nó còn join các table đủ kiểu) thì nó đã die nhăn răng chạy không có ngày trở về..
Cái đoạn lấy latest version kiểu như thế này:

 SELECT
              pro.ProductId,
              pro.VersionComment,
              pro.VersionNumber,
              pro.ProductName

       FROM product.Products pro 
       JOIN (SELECT pro.ProductId, MAX(pro.VersionNumber) AS LatestVersionNumber
                     FROM product.Products pro
                     GROUP BY pro.ProductId) productGroup ON pro.ProductId = productGroup.ProductId
       WHERE pro.VersionNumber = productGroup.LatestVersionNumber

Các phương pháp sử dụng with CTE đã sử dụng performance thì nó cũng ngang ngang nhau

Và để giải quyết trường hợp này thì mình có 2 solution :

  • Thêm một field IsLatestVersion dòng nào là latest version thì cho nó = true. Đã test và chạy nhanh đáng kể, nhưng database trên bảng truy vấn nó vẫn nhiều nhưng không sao vẫn chấp nhận được
  • Tách table, thêm một table histories và 1 table thì lưu giữ giá trị của latest version. Khi đó database truy vấn sẽ được giảm đáng kể, thấy ok rồi đó, chạy ngon cành đào ^^ , mất mấy giây để lấy dữ liệu.

Ngon ngon nhưng 2 giải pháp này đêu không được chấp nhận vì nó impact rất nhiều tới các method của API. Việc test lại toàn bộ API không hề đơn giản sẽ rất là lâu cho dù là đang sử dụng automation test….

Hix hix đến dây vấn đề vẫn đang bị tắc và giải pháp đưa ra cho vấn đề này là đưa ra con số chính xác bao nhiêu dữ liệu thì nó sẽ bị time out khi query. Nếu mà nó ít hơn hoặc xấp xỉ dữ liệu trên Production thì khi đó tính tiếp :v :v.

Không nên dùng nhiều bảng tạm khi truy vấn

Hiện tại câu query này được build dựa trên parameters truyền vào(filter, sort, results) tức là build sql dynamic. Và câu query xử lí rất nhiều trường hợp nên phải sử dụng bảng tạm để lưu lại kết quả hix hix đây có lẽ là một sai lầm ngớ ngẫn nhất khi dùng bảng tạm trong việc query với dữ liệu lớn. Trong query cũ thì có dùng 2 table tạm, và việc chạy dữ liệu nhiều nó đã góp phần làm chậm câu query. Khi mình đo performance thì việc insert và 2 bảng tạm đó 1 cái chiếm đến 50% còn cái còn lại chiếm đến 99% (vì có 2 câu query) @@ oa oa, và việc đầu tiên xóa bảng tạm ngay lập tức, bằng việc dùng subquery và tính toán một số cái trên câu lệnh select.
Kiểu dạng này :

 Select a,b,c ,d = a +b, ..
 From(
    Select a,b,c
    From table1
    where...
 )
 Where a = 1

Từ hơn 15p để query ra đống dữ liệu và nó đã xuống còn mấy giây. 
2 câu bảng tạm nó có dạng như thế này:
Câu thứ 1:

DELACRE @tmpTable TABLE (Id INT, Name NVARCHAR(100));
INSERT INTO @tmpTABLE SELECT Id,Name FROM RealTable WHERE ….

Câu thứ 2:

SELECT Id,Name 
INTO #tmp 
FROM  product.Products pro
INNER JOIN @tmpTable….
LEFT JOIN .....

Ở đây mình sử dụng 2 bảng tạm @tmpTable và #tmp và mình đo performance thì khi insert vào #tmp thì chiếm tới 99% thời gian >.<

Do câu query thực nó rất phức tạp với lại tính security của data nên mình không nêu cụ thể lên. Nó còn một số vấn đề nữa nhưng không tiện post lên.