Làm việc với transaction để tăng hiệu năng câu lệnh insert trong SQL Server

Trong bài viết này Stanford sẽ giới thiệu đến các bạn đang học cơ sở dữ liệu SQL làm việc với transaction và ứng dụng transaction để tăng hiệu năng câu lệnh insert trong SQL Server.

Transaction trong SQL Server là gì ?

TRANSACTION trong SQL là tiến trình thực hiện một nhóm các câu lệnh SQL. Các câu lệnh này được thực thi một cách tuần tự và độc lập. 
Một Transaction được thực hiện thành công khi tất cả câu lệnh đều thành công, khi đó tất cả các thay đổi dữ liệu được thực hiện trong Transaction được lưu vào cơ sở dữ liệu. 
Nếu chỉ một trong số đó thất bại thì toàn bộ tiến trình sẽ thất bại, đồng nghĩa với việc dữ liệu phải rollback về trạng thái ban đầu (dữ liệu được khôi phục về trạng thái trước khi thực hiện Transaction).


Thực hiện transaction

SQL Server hỗ trợ các giao tác trong một số chế độ:

  • Tự động thực hiện giao tác: Mỗi câu lệnh đơn được thực hiện và tự động commit ngay sau khi nó hoàn thành. Ở chế độ này, người ta không cần phải viết bất kỳ câu lệnh cụ thể nào để bắt đầu và kết thúc các transaction. Mỗi câu lệnh được xem là 1 transaction, đây là chế độ mặc định cho SQL Server Database Engine.
  • Các transaction rõ ràng: Mọi transaction bắt đầu một cách rõ ràng với câu lệnh BEGIN TRANSACTION và kết thúc với giao tác ROLLBACK hoặc COMMIT.
  • Các transaction ẩn: Một transaction mới sẽ tự động bắt đầu khi transaction trước đó hoàn thành và mọi transaction được hoàn thành một cách rõ ràng bằng cách sử dụng câu lệnh ROLLBACK hoặc COMMIT.

Sử dụng transaction để tăng hiệu năng câu lệnh insert

Để thấy sự khác biệt khi sử dụng transaction phù hợp sẽ mang lại hiệu năng cao cho câu lệnh insert. Chúng ta có 2 đoạn code SQL dưới đây và theo bạn đoạn code nào chạy nhanh hơn.

GO
----1. Auto-commit transaction, mặc định SQL Server sẽ commit transaction cho mỗi câu lệnh insert và sẽ commit 250000 lần.
CREATE TABLE Test1(id INT IDENTITY PRIMARY KEY CLUSTERED, data1 BIGINT, data2 DATETIME, data3 CHAR(100))
DECLARE @i INT = 1
WHILE @i <= 250000
BEGIN
    INSERT INTO Test1(data1,data2,data3)
    SELECT @i, GETDATE(), 'Stanford.com.vn: Khong su dung transaction-' + CAST(@i AS CHAR(10))
    SET @i += 1
END
GO
  
----2. Explicit transaction, khai báo transaction để bảo SQL Server chỉ commit một lần khi đã insert đủ 250000 dòng
CREATE TABLE Test2(id INT IDENTITY PRIMARY KEY CLUSTERED, data1 BIGINT, data2 DATETIME, data3 CHAR(100))
  
BEGIN TRANSACTION
DECLARE @i INT = 1
WHILE @i <= 250000
BEGIN
    INSERT INTO Test2(data1,data2,data3)
    SELECT @i, GETDATE(), 'Stanford.com.vn: Su dung transaction-' + CAST(@i AS CHAR(10))
    SET @i += 1
END
COMMIT
Cả hai đoạn code đều làm công việc giống nhau là tạo mới một bảng, sau đó insert 250000 dòng vào bảng đó. Khác biệt duy nhất là đoạn code 1 không có khai báo BEGIN VÀ COMMIT TRANSACTION. SQL Server sẽ ghi log cho tất cả các hành động thay đổi data hoặc schema trong database và khi bạn commit transaction thì log records sinh ra sẽ được ghi vào transaction log.
Mặc dù không khai báo nhưng mặc định SQL Server sẽ sử dụng auto-commit mode transaction cho cả các câu lệnh thay đổi data. Do đó, trong đoạn code 1 của chúng ta SQL Server sẽ thực hiện commit transaction 250000 lần vì chúng ta thực hiện 250000 câu lệnh inserts trong vòng lặp while.

Ngược lại ở đoạn code 2, chúng ta khai báo transaction bằng lệnh BEGIN và sau đó COMMIT khi đã thực hiện xong vòng lặp, nên SQL Server chỉ commit 1 lần duy nhất.
COMMIT là hành động ghi log vào file transaction log và nó cần một khoảng thời gian để thực hiện, việc commit nhỏ lẻ liên tục sẽ làm tăng chi phí thực thi, bạn có thể tìm hiểu thêm về chi phí ở link này. Thay vào đó chúng ta có thể tiết kiệm thời gian ghi log bằng cách hạn chế số lần commit. Nhưng không phải cứ thực hiện insert xong rồi commit, bản thân số lượng bao nhiêu thì phù hợp cũng có ngưỡng, ngoài ra còn gặp các vấn đề blocking nếu số lượng insert 1 lần quá lớn.

Vì chúng ta sử dụng vòng lặp nên nếu dùng STATISTICS TIME để đo đạt sẽ hơi khó khăn vì kết quả rất cồng kềnh, chúng ta sẽ dùng cách khác. Theo kết quả demo trên máy của mình, đoạn code 1 mất 6 giây, còn đoạn code 2 chỉ mất chưa đến 3 giây.
Để thấy sự khác biệt giữa việc sử dụng transaction và mặc định của SQL Server, chúng ta có thể phân tích dữ liệu hai bảng trên bằng câu truy vấn dưới đây.

SELECT CONVERT(VARCHAR(24),data2,120) AS inserted_time, COUNT(1) rows_per_second
FROM Test1
GROUP BY CONVERT(VARCHAR(24),data2,120)
ORDER BY inserted_time
GO
SELECT CONVERT(VARCHAR(24),data2,120) AS inserted_time, COUNT(1) rows_per_second
FROM Test2
GROUP BY CONVERT(VARCHAR(24),data2,120)
ORDER BY inserted_time
Mỗi một dòng dữ liệu khi insert vào bảng Test1 hoặc Test2 đều có giá trị thời gian được insert, được lưu ở cột data2. Dựa vào thông tin này ta sẽ biết dòng đầu tiên và cuối cùng được insert vào lúc nào và lấy giá trị cuối trừ giá trị đầu sẽ ra thổng thời gian insert. Bạn hãy tự thực hiện điều đó. Câu truy vấn trên mình muốn xác định có bao nhiêu dòng dữ liệu được insert vào bảng Test1 và Test2 trong mỗi giây, nếu sự khác biệt là đủ lớn chúng ta sẽ dễ dàng thấy được đoạn code nào nhanh hơn.


Dựa vào kết quả trong hình chúng ta thấy dữ liệu bảng Test1 trải dài từ giây thứ 50 đến giây thứ 55, còn dữ liệu bảng Test2 chỉ gói gọn trong từ giây 03 đến giây 05. Rõ ràng là đoạn code 2 chạy nhanh hơn. Và nếu bạn muốn biết chính xác tổng thời gian insert là bao nhiêu thì có thể sử dụng câu truy vấn sau:

SELECT MIN(data2) start_time, MAX(data2) end_time, DATEDIFF(MS,MIN(data2),MAX(data2)) duration
FROM Test1;
GO
SELECT  MIN(data2) start_time, MAX(data2) end_time, DATEDIFF(MS,MIN(data2),MAX(data2)) duration
FROM Test2;


Bảng Test1 mất 4250 ms để thêm 250k dòng còn bảng Test2 sẽ mất 2210 ms để thêm.

Như vậy trong bài viết này Stanford đã giúp các bạn học SQL hiểu rõ hơn về transaction cũng như áp dụng vào nó khi cần xử lý câu lệnh như insert, update, delete để nâng cao hiệu năng cho câu lệnh xử lý trong SQL Server. Các bạn có thể thực hiện từng bước theo bài hướng dẫn trên máy tính của mình nhé.

Bên cạnh đó nếu bạn đang muốn được đào tạo bài bản từ cơ bản đến nâng cao có thể tham gia ngay khóa học sql cùng chuyên gia giàu kinh nghiệm Stanford tại đây: http://bit.ly/2SLPYFF và nhận ưu đãi hấp dẫn của Stanford trong thời gian này. Bạn có thể gọi theo hotline: 0963 723 236 - 0866 586 366 để được gọi lại tư vấn trực tiếp nhé.

=============================
☎ STANFORD – ĐÀO TẠO VÀ PHÁT TRIỂN CÔNG NGHỆ
Hotline: 0963 723 236 - 0866 586 366
Website: https://stanford.com.vn
Facebook: http://bit.ly/2FN0TYb
Youtube: http://bit.ly/2TkKT7I

Tags: tìm hiểu transaction trong SQL Server, học sql cơ bản