Trong công việc tôi rất hay gặp những bảng có số lượng cột khá lớn. Ví dụ trong một dự án tôi tham gia gần đây có một bảng chứa 320 cột. Nhưng sau khi khảo sát thì phát hiện ra có khoảng 30 cột là được truy cập thường xuyên hơn hẳn các cột còn lại. Chúng tôi đã dùng kỹ thuật phân đoạn trên: chia thành 2 bảng, một bảng chứa 30 cột được dùng thường xuyên cộng với cột ID, bảng kia cũng chứa cột ID và các cột còn lại. Kết quả là hiệu năng tăng lên đáng kể: từ lúc rất hay bị lỗi time-out do thủ tục chạy quá lâu (35-40s), đến nay đã hoàn toàn không bị nữa kể cả khi server đang chịu tải nặng, với thời gian thực hiện thủ tục trung bình vào khoảng 10-12s.

Vậy lý do nào dẫn đến cải thiện về hiệu năng? Điều này liên quan đến cách tổ chức dữ liệu của bảng bên trong SQL Server. SQL Server chia mỗi bảng thành các trang (page) có kích thước đều nhau 8KB. Các bản ghi được lưu nối tiếp nhau vào từng trang, đến khi đầy trang thì lưu tiếp sang trang mới. Tùy theo kích thước của bản ghi (bằng kích thước của các cột cộng lại) mà có bao nhiêu bản ghi được xếp vừa vào một trang. Nếu kích thước nhỏ sẽ có nhiều bản ghi được chứa trong một trang, ngược lại nếu kích thước lớn thì mỗi trang sẽ chứa được ít bản ghi. Thậm chí nếu kích thước này vượt quá 8KB thì vài trang mới chứa hết một bản ghi (SQL Server 2000 không cho phép điều này nên kích thước bản ghi tối đa được phép tạo trong SQL Server 2000 chỉ là 8KB).

Trang là đơn vị dữ liệu nhỏ nhất mà hệ thống đọc từ ổ cứng. Kể cả khi chỉ cần truy xuất một bản ghi thì SQL Server cũng tìm đến vả load cả trang chứa nó vào bộ nhớ. Do đó với bảng hẹp thì cùng một thao tác đọc trang sẽ tải được nhiều bản ghi vào bộ nhớ hơn so với bảng rộng. Hay nói cách khác, để đọc cùng một số lượng bản ghi như nhau thì bảng hẹp cần đọc ít trang hơn bảng rộng. Vì thế truy vấn trên bảng hẹp luôn luôn nhanh hơn truy vấn trên bảng rộng.

Trong ví dụ sau, tôi có bảng BangBeo (bảng béo) gồm ba trường ID int, txt1 char(10), txt2 char(8000). Nay tôi tách bảng BangBeo thành hai bảng:

· BangGay (bảng gầy) gồm ID và txt1
· BangBeoPhu (bảng béo phụ) gồm ID và txt2

Tôi vẫn giữ lại bảng BangBeo ban đầu để so sánh

Mặc dù hai câu lệnh trả về cùng một kết quả, nhưng phương án thực thi cho thấy câu lệnh trên bảng gầy chỉ chiếm một phần nhỏ chi phí so với câu lệnh trên bảng béo:

Trong ví dụ trên, bảng béo có kích thước bản ghi xấp xỉ 8KB, do đó mỗi trang chứa được một bản ghi, tổng cộng bảng với 100 bản ghi sẽ chiếm 100 trang. Còn bảng gầy có kích thước vẻn vẹn 14 byte (INT 4 byte và CHAR 10 byte) - chính xác ra thì còn thêm một số byte header. Vì thế toàn bộ bảng với 100 bản ghi chiếm chưa hết một trang. Như vậy mặc dù trả về cùng kết quả nhưng câu truy vấn trên bảng béo cần đọc tới 100 trang, còn câu lệnh trên bảng gầy chỉ cần đọc có 1 trang. Kết quả thống kê I/O cũng cho thấy điều này (chú ý các con số tô đậm):

Bảng béo:

(100 row(s) affected)
Table ‘BangBeo’. Scan count 1, logical reads 100, physical reads 10, read-ahead reads 100

Bảng gầy:

(100 row(s) affected)
Table ‘BangGay’. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0

Một vài lưu ý

Kỹ thuật phân đoạn bảng theo chiều dọc như trình bày ở trên không được hỗ trợ bởi Microsoft, theo nghĩa nó không trong suốt đối với bên ngoài và các bảng được phân đoạn trở thành các bảng biệt lập (không giống với phân đoạn theo chiều ngang khi bảng vẫn hiện ra là một bảng duy nhất). Việc tách thành các bảng riêng như trên không nằm ngoài mục đích nâng cao hiệu năng cho câu truy vấn trên một số cột nhất định. Vì thế nó chỉ có ý nghĩa khi bạn xác định được một tập các cột được truy vấn thường xuyên hơn các cột còn lại. Bạn cũng không phải có bảng rộng tới 300 cột mới cần đến phân đoạn. Kể cả với bảng hẹp hơn nhưng số cột được truy vấn nhiều lần chỉ là một phần nhỏ trong tổng số tất cả các cột, bạn sẽ thấy lợi ích của việc phân đoạn.

Kỹ thuật phân đoạn này cũng kéo theo một vài rắc rối khi viết code:

Đối với lệnh SELECT, bạn phải nhớ các cột nào nằm ở bảng nào để viết câu lệnh cho đúng, vì các cột giờ không nằm chung trong một bảng nữa. Bạn có thể tạo một view gom cả hai bảng và truy vấn qua view này, nhưng như thế sẽ làm mất đi tác dụng về hiệu năng, vì khi JOIN thì cả hai bảng sẽ luôn được truy xuất và số trang cần đọc không giảm đi tí nào. Khi có thay đổi về thiết kế, chẳng hạn cần di chuyển cột từ bảng này sang bảng kia, bạn phải quay lại sửa lại câu lệnh cũ.

Đối với các lệnh DELETE/INSERT/UPDATE, bạn luôn nhớ cần thực hiện trên cả hai bảng. Bạn có thể thực hiện câu lệnh qua view, và ở bên dưới view tạo một trigger loại INSTEAD OF trong đó thực hiện thao tác trên cả hai bảng.

[Nghean-Aptech st]