Đố vui SQL 1: Sử dụng transaction để tăng hiệu năng câu lệnh insert

Câu đố được post ở group Quản trị CSDL SQL Server, bài viết này nhằm cung cấp câu trả lời kèm demo để 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.

CREATE DATABASE labdb01
GO
USE labdb01 
GO

----1. auto-commit transaction, mặc định SQL Server sẽ commit transaction cho mỗi câu lệnh insert và sẽ commit 10000 lần.
CREATE TABLE T1(id INT IDENTITY PRIMARY KEY CLUSTERED, data1 BIGINT, data2 DATETIME, data3 CHAR(64))
DECLARE @i INT = 1
WHILE @i <= 10000
BEGIN
	INSERT INTO T1(data1,data2,data3)
	SELECT @i, GETDATE(), 'T1-' + CAST(@i AS CHAR(8))
	SET @i += 1
END
GO

----2. explicit transaction, khai báo transaction để bảo SQL Server chỉ commit một lần khi đã insert đủ 10000 dòng
CREATE TABLE T2(id INT IDENTITY PRIMARY KEY CLUSTERED, data1 BIGINT, data2 DATETIME, data3 CHAR(64))

BEGIN TRANSACTION
DECLARE @i INT = 1
WHILE @i <= 10000
BEGIN
	INSERT INTO T2(data1,data2,data3)
	SELECT @i, GETDATE(), 'T2-' + CAST(@i AS CHAR(8))
	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 10000 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 10000 lần, vì chúng ta thực hiện 10000 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 4 giây, còn đoạn code 2 chỉ mất chưa đến 1 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

USE labdb01
GO

SELECT CONVERT(VARCHAR(24),data2,120) AS inserted_time, COUNT(1) rows_per_second
FROM T1 
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 T2 
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 T1 hoặc T2 đề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 T1 và T2 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.

sử dụng transaction trong SQL
Tốc độ insert dữ liệu vào 2 bảng T1 và T2 theo giây.

Dựa vào kết quả trong hình chúng ta thấy dữ liệu bảng T1 trải dài từ giây thứ 26 đến giây thứ 31, còn dữ liệu bảng T2 chỉ gói gọn trong 1 giây. 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

USE labdb01
GO

SELECT MIN(data2) start_time, MAX(data2) end_time, DATEDIFF(MS,MIN(data2),MAX(data2)) duration
FROM T1
GO
SELECT  MIN(data2) start_time, MAX(data2) end_time, DATEDIFF(MS,MIN(data2),MAX(data2)) duration
FROM T2

sử dụng transaction trong SQL Server
Thời gian insert của bảng T1 và T2.

Bảng T1 mất 4880 ms để insert 10000 dòng, còn bảng T2 chỉ mất 593 ms.

Leave a Reply