Tăng tốc độ thao tác dữ liệu với dapper bằng bulk insert, bulk update, bulk delete

Một ngày nào đó năm Nhâm Thìn, Ở đất kinh kỳ cạnh hồ tây xuất hiện hai mặt trời, không biết có điềm gì? Một developer ngồi cặm cụi làm tính năng import dữ liệu cho phần mềm. Nhiệm vụ cần Insert hàng nghìn dòng dữ liệu vào database. Hắn import rồi ngồi đếm 1 2 3 5 em có đánh rơi nhịp nào không? rồi hắn cứ đếm mọi người nhìn hắn bảo thằng dở hơi, hắn kệ hắn vẫn ngồi đếm, 1000, 2000 giây trôi qua mà vẫn không chạy xong. Hỏng rồi thế này khách hàng có mà mắng chết. Hắn đành ngồi tìm hiểu sâu vào xem nguyên nhân tại sao?

Chức năng này hắn dùng C#, để kết nối với database hắn dùng một thư viện là Dapper, và để đễ dàng mapping object hắn dùng thư viện là Dapper.Contrib. Thư viện này hỗ trợ insert nhiều, nhưng hắn thử thì thấy chạy khá chậm. Tại sao lại thế nhỉ? Hazz.

Hắn ngồi lọ mọ đọc từng dòng code, để rồi tìm ra cơ chế của thư viện này là khi insert nhiều thì thư viện này đơn giản là dùng vòng for và insert từng bản ghi một. Điều này dẫn tới round trip nhiều từ ứng dụng vào database và nhiều câu insert chạy sẽ chậm hơn nhiều so với một câu insert nhiều values. Ví dụ

INSERT INTO USER(Name,Age)
Values('Adam',25);
INSERT INTO USER(Name,Age)
Values('Kenny',35);
....

sẽ chậm hơn câu lệnh

INSERT INTO USER(Name,Age)
Values
('Adam',25),
('Kenny',35)
.......

Hắn bèn tìm xem có thư viện nào không? Ơ rê ca. Sau khi lọ mọ hắn tìm được thư viện https://dapper-plus.net/bulk-insert . Nhưng sau khi chạy một thời gian hắn định dùng thì thấy giá không được rẻ lắm. Mới một dev quèn ăn mì tôm uống nước cầm hơi thì quả là hơi chát.

Vì vậy thôi thì lấy công làm lãi. Hắn quyết định tự viết một cái để dùng. Sau hàng tối lọ mọ vừa code vừa mò. Cuối cùng bản đầu tiên đã chạy được với hiệu năng khá ổn

Dưới đây là bảng so sánh hiệu năng theo mili giây với 500 bản ghi

Method	SQL Server	MySQL	PosgreSQL	SQLite
Insert	24900	8600	5300	7900
Bulk Insert	111	88	53	89
Update	9500	22600	4800	6500
Bulk Update	889	112	96	N/A
Delete	17200	23800	5400	6100
Bulk Delete	194	150	1200	115

Như trong hình trên có thể thấy hiệu năng với 500 bản ghi theo cách mới và cách cũ hiệu năng đều tăng vài chục tới 100 lần với cách thông thường. Mọi người có thể thấy các hàm BulkInsert, BulkUpdate, BulkDelete nhanh hơn rất nhiều so với các hàm thông thường với MySQL, SQLServer, PosgreSQL và SQLite.

Có một ngoại lệ trường hợp update với SQLite thì chậm hơn. Vì chưa có cách nào update nhanh với SQLite nên đành dùng cách cũ.

Các sử dụng đơn giản giống như Dapper.Contrib.

Đầu tiên bạn add nuget

dotnet add package Bulk.Dapper

Giả sử bạn có entities như sau có thể thực hiện đơn giản với connection được tạo

   public class User
    {
        [Key]
        public int id { get; set; }
        public string name { get; set; }
        public int age { get; set; }
    }

    List<User> users = new List<User>
    {
            new User { name = "Joe", age = 10 },
            new User { name = "Donal", age = 10 }
    };

Insert

Normal

       connection.BulkInsert(users);

Async

       connection.BulkInsertAsync(users);

Update

Normal

       connection.BulkUpdate(users);

Async

       connection.BulkUpdateAsync(users);

Delete

       connection.BulkDelete(users);

Async

       connection.BulkDeleteAsync(users);

Thay đổi Table name và column name

có thể thay đổi các thuộc tính tên bảng, tên cột bằng atribute Table, ColumnName

    [Table("Users")]
    public class UserChangeColumn
    {
        [Key]
        public int id { get; set; }

        [ColumnName("name")]
        public string fullname { get; set; }
        public int age { get; set; }
    }