Nhân có dịp nghiên cứu 1 stored procedure để truy vấn dữ liệu số lượng lớn mà đang gặp vấn đề về performance chung cả hệ thống (bộ nhớ RAM Database server tăng cao), tôi tìm được bài viết giải thích khá rõ ràng về ảnh hưởng của bảng tạm trong SQL nên chia sẻ.
Bài viết Eliminate the Use of Temporary Tables For HUGE Performance Gains với link gốc của bài viết ở đây :
http://www.sql-server-performance.com/2002/derived-temp-tables/
Đại khái để tạo bảng tạm trong SQL, khi đặt tên bảng thì có dấu # phía trước nếu là bảng tạm cục bộ – local temporary table (VD : CREATE TABLE #LTMPTABLE) hoặc ## phía trước nếu là bảng tạm toàn cục – global temporary table (VD : CREATE TABLE ##GTMPTABLE). Ví dụ thông thường của việc sử dụng bảng tạm như sau :
1) CREATE the temporary table
2) INSERT data into the newly created table
3) SELECT data from the temporary table (usually by JOINing to other physical tables) while holding a lock on the entire tempdb database until the transaction has completed.
4) DROP the temporary table
Theo đó SQL khi xử lý sẽ như bên dưới, nghĩa là qua rất nhiều bước và nhiều hoạt động đọc/ghi ổ cứng được thực thi (thông qua xử lý trên tempdb). Và điều quan trọng là SQL sẽ khóa database tempdb cho đến khi truy vấn liên quan đến bảng tạm xong, nghĩa là nếu dữ liệu được truy vấn nhiều thì suốt thời gian đó đừng hòng có hoạt động nào cũng sử dụng bảng tạm, cũng cần sử dụng tempdb xảy ra được mà phải chờ. Và về phía giao diện người dùng thì thấy hệ thống bị chậm, hay lâu quá là vì vậy.
1) Lock tempdb database
2) CREATE the temporary table (write activity)
3) SELECT data & INSERT data (read & write activity)
4) SELECT data from temporary table and permanent table(s) (read activity)
5) DROP TABLE (write activity)
4) Release the locks
Trong khi nếu 1 bảng thông thường hoặc bảng derived table thì sẽ rất ít hoạt động và thậm chí có thể không cần khóa bảng được truy vấn dữ liệu nếu dùng từ khóa with(nolock) hoặc read uncommitted trong stored procedure.
1) CREATE locks, unless isolation level of “read uncommitted” is used
2) SELECT data (read activity)
3) Release the locks
Và như vậy thì bản thân câu truy vấn sẽ ít bước hơn nhưng việc không khóa cả database tempdb trong khi truy vấn đã giúp được các hoạt động khác vẫn tiến hành được mà không phải chờ, qua đó sẽ nhanh hơn.
Tuy nhiên, trong trường hợp dữ liệu nhiều thì phần lớn chia sẻ cho rằng derived table lại sẽ chậm hơn bảng tạm mà có đánh index (derived table chỉ lưu trên bộ nhớ khi chạy và không đánh index được).
Kết luận : Khi dùng bảng tạm thì lưu ý có đánh Index và vấn đề xem xét thời gian lock tempdb sao cho ít nhất. Hoặc 1 cách khác là tạo bảng vật lý trong database tempdb (cần test thêm) hoặc database dữ liệu (cần thêm quyền, có thể tạo schema tên temp để tạo table có schema này) để giảm thời gian khóa database tempdb. Update : việc tạo bảng vật lý trong database tempdb là không khuyến cáo vì có thẻ database dữ liệu và database tempdb khác collcation.
Trong bài viết có sử dụng 1 số từ, thuật ngữ tôi tạm dịch hoặc không dịch được thì dùng luôn từ tiếng Anh, nếu có các từ, thuật ngữ nào bạn không rõ thì hỏi thêm SQL Book Online hoặc nhanh thì Google.
Tóm Tắt
Share this:
Like this:
Like
Loading…
