So sánh tốc độ truy vấn dapper vs entity năm 2024

Dapper là một Micro ORM (Object Relational Mapping) Framework sử dụng cho .Net, giống như Entity Framework, được phát triển bởi StackExchange, hỗ trợ làm việc với nhiều hệ quản trị cơ sở dữ liệu khác nhau một cách dễ dàng và có tốc độ truy xuất dữ liệu rất tốt.

1. Khái niệm về ORM

ORM (Object Relational Mapping) là một Framwork được xây dựng để liên kết các bảng trong database với các đối tượng trong ứng dụng. Có 2 cách tiếp cận ORM:

• Code First: Viết mã đối tượng trước, sinh database sau.
• Database First: Xây dựng database trước, sinh đối tượng sau từ database sau

2. Dapper là gì?

• Dapper là một Micro ORM là một thư viện xây dựng trên nền ADO.NET. Nói một cách khác Dapper là một thư viện mở rộng của ADO.NET (extend của lớp IDbConnection) tuy nhiên lại được bổ sung thêm các tính năng của ORM.
• Được phát triển bới các developer của StackExchange dưới dạng phần mềm mã nguồn mở.
• Nó được thiết kế để truy cập các cơ sở dữ liệu quan hệ sao cho có hiệu năng tốt nhất.
• Nó nhỏ gọn hơn nhiều so với Entity Framework nên gọi là Micro ORM.

3. Lý do nên dùng Dapper?

• Speed and fast in performance.
• Fewer lines of code.
• Object Mapper.
• Static Object Binding.
• Dynamic Object Binding.
• Easy Handling of SQL Query.
• Easy Handling of Stored Procedure.
• Operating directly to IDBConnection class that provides smoothness and running query directly to the database instead of passing data using various objects as we do in EF
• Multiple Query Support.
• Support for Stored Procedure.
• Bulk Data insert functionality.
• Dapper also allows fetching multiple data based on multiple inputs

So sánh hiệu năng Dapper với các Framework ORM khác

So sánh hiệu năng Dapper với Entity Framework Core

4. Các phương thức mở rộng trong Dapper

Dapper mở rộng từ giao diện IDbConnection với rất nhiều các phương thức:

• Execute – an extension method that we use to execute a command one or multiple times and return the number of affected rows
• Query – with this extension method we can execute a query and map the result
• QueryFirst – it executes a query and maps the first result
• QueryFirstOrDefault – we use this method to execute a query and map the first result, or a default value if the sequence contains no elements
• QuerySingle – an extension method that can execute a query and map the result. It throws an exception if there is not exactly one element in the sequence
• QuerySingleOrDefault – executes a query and maps the result, or a default value if the sequence is empty. It throws an exception if there is more than one element in the sequence
• QueryMultiple – an extension method that executes multiple queries within the same command and map results

Ngoài ra Dapper cũng cung cấp tất cả các phương thức bất động bộ tương ứng ((ExecuteAsync, QueryAsync, QueryFirstAsync, QueryFirstOrDefaultAsync, QuerySingleAsync, QuerySingleOrDefaultAsync, QueryMultipleAsync).

A few months ago I ran across a blog post about getting started with Dapper.NET. I had heard of Dapper before but had never actually tried to use it. Dapper is known for being fast, but I was curious just how much faster it would be in the situations I typically find myself: writing database queries. I’ve long used Entity Framework for data layers, which is often criticized for being slow.

To test out Dapper’s speed, I built four basic tests so I could compare its performance to Entity Framework. If you’d like to run these experiments yourself, all the code used for this blog post is available on GitHub.

Data Model

The data model is relatively small and simple. I’ve worked on a few projects involving athletes so that was my starting point.

[sourcecode language=”plain”]

public class Athlete { public long Id { get; set;}

public string FirstName { get; set; }

public string LastName { get; set; }

public string Position { get; set; } }

[/sourcecode]

An athlete is nothing without a team, which leads to the next class.

[sourcecode language=”plain”] public class Team { public long Id { get; set; }

public string Name { get; set; } }

[/sourcecode]

An athlete can, of course, be on multiple teams so a third class is needed to link the two.

[sourcecode language=”plain”] public class AthleteTeam { public long AthleteId { get; set; }

public long TeamId { get; set; } } [/sourcecode]

I’ve never had a good experience when using Entity Framework to manage the relationships between objects, so there are no navigation properties on these objects. They are a straight mapping of class to database table. I mostly use ORMs to reduce the amount of SQL I write, not necessarily to manage the relationships between my objects.

Test Setup

Before starting any tests, I seeded my database with three sports teams with each containing 1,000 athletes. The first thing I found was both Entity Framework and Dapper take longer with the first query than with subsequent queries. For Entity Framework, I know a query has to be complied on the first execution, but for Dapper I’m not sure what it’s doing during the first execution. For all tests, I ran each query twice and ran each test 10 times before averaging out the results. All times are in milliseconds.

Loading 1 Athlete

In the first test, I loaded a single athlete by Id from the database.

Test Iteration Entity Framework First Entity Framework Second Dapper First Dapper Second 1 348 13 83 6 2 361 15 81 5 3 332 13 67 6 4 312 11 67 6 5 313 14 69 6 6 334 14 69 6 7 368 12 75 7 8 331 13 72 9 9 333 13 72 6 10 321 14 69 8 Average (ms) 335.3 13.1 72.6 6.7

There’s no denying that Dapper is faster than Entity Framework, especially on the first execution. Compiling the LINQ query is an expensive event.

Loading Many Athletes by Position

In the second test, I loaded all the athletes for a specific position to see how each framework fared loading multiple records and searching on a string field instead of a primary key.

Test Iteration Entity Framework First Entity Framework Second Dapper First Dapper Second 1 80 35 13 7 2 83 33 19 10 3 81 33 12 8 4 75 33 11 7 5 69 33 11 7 6 79 33 11 7 7 78 38 12 7 8 72 32 12 8 9 76 32 12 8 10 65 34 14 6 Average (ms) 75.8 33.8 12.7 7.5

The differences between the first and second queries were less extreme for the second test. My suspicion is both frameworks are mapping the database on the first query and pay an extra time penalty. Loading more results also increased the gap in speed between the second Entity Framework and Dapper queries.

Loading Teams with Athletes

I wanted to see how well each framework would handle joins which led to the third test: loading a team with all 1,000 athletes.

Test Iteration Entity Framework First Entity Framework Second Dapper First Dapper Second 1 143 38 23 16 2 144 42 27 20 3 127 39 21 16 4 132 29 21 26 5 136 38 28 22 6 142 37 24 20 7 132 35 22 16 8 133 39 22 16 9 127 36 24 18 10 121 37 21 21 Average (ms) 133.7 37 23.3 19.1

The results are pretty consistent with the other tests. Entity Framework is still lagging behind, but there was a Dapper run that took longer on the second query rather than the first. On average though the second Dapper query still ran 4.2 milliseconds faster.

Inserting Athletes

In the last test, I wanted to look at the speed differences inserting records into the database. I inserted one athlete at a time and for Dapper I used the Dapper-Extensions nuget package to handle the insert. The package reduces an insert to a single method call. In the past, the insert code I’ve seen has always looked a little weird and was one of my main complaints about Dapper.

Test Iteration Entity Framework First Entity Framework Second Dapper First Dapper Second 1 284 14 85 6 2 278 11 91 7 3 252 12 83 5 4 249 11 106 6 5 268 10 76 7 6 268 10 80 8 7 266 12 92 6 8 267 13 86 8 9 256 11 80 7 10 253 11 78 6 Average (ms) 264.1 11.5 85.7 6.6

Surprisingly, the first Dapper queries took around as long as the first queries for loading a single athlete. I don’t know enough about the internals of the Dapper-Extensions, but it seems likely there is an additional step taken when using its insert functionality. Otherwise the results are consistent with the trends seen across the other tests.

Conclusion

Dapper was written with speed as a priority and the tests definitely prove this out. However, if we ignore the overhead of the first query, the difference between the two ranged from 5-25 milliseconds. The largest site I know of using Dapper is Stack Overflow and I would think they definitely benefit from saving milliseconds anywhere they can. In a smaller application I’m not sure it makes sense to make an ORM decision solely for the sake of a few milliseconds. Instead I would pick based on the style of the code written for either framework. I like Entity Framework for its LINQ integration and will continue to favor it for that. To me using Dapper felt like writing SQL, which I try to avoid when possible.

In the past when a query starts to take too long executing through Entity Framework I have replaced it with either a view or a stored procedure, depending on the situation. Going forward, Dapper will be a valuable tool that could be used before going straight to raw SQL.