3

Using ADO.NET, what is the fastest way to retrieve data from the database and populate the data into my business objects?

Which one should I use? DBDataReader , DBDataAdapter, or any other classes?

Is there a way to make this process automated? Let's say based on property names and matching them with database field names?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
aryaxt
  • 76,198
  • 92
  • 293
  • 442
  • possible duplicate of [What is the fastest way to read data from a DbDataReader?](http://stackoverflow.com/questions/5758526/what-is-the-fastest-way-to-read-data-from-a-dbdatareader) – nawfal Feb 11 '13 at 18:20

3 Answers3

7

That sounds exactly like what an ORM or micro-ORM does. Here's the output of dapper-dot-net's performance tests (run about 1 minute ago, on a PC that is also busy doing some transcoding, so not 100% reliable - please run yourself)... This is also a very limited test - as always, tests should be representative of your specific environment - but since we can't predict your environment, we use our environment instead! I've marked the "dapper" ones as <==== dapper

Running 500 iterations that load up a post entity
Mapper Query (non-buffered) took 57ms          <==== dapper
hand coded took 57ms
Dynamic Mapper Query (buffered) took 58ms      <==== dapper
PetaPoco (Fast) took 58ms
Dynamic Mapper Query (non-buffered) took 59ms  <==== dapper
Mapper Query (buffered) took 60ms              <==== dapper
Dapper.Cotrib took 60ms                        <==== dapper
PetaPoco (Normal) took 66ms
Dynamic Massive ORM Query took 67ms
BLToolkit took 88ms
Simple.Data took 96ms
Linq 2 SQL Compiled took 99ms
NHibernate Session.Get took 127ms
SubSonic Coding Horror took 128ms
Entity framework CompiledQuery took 130ms
NHibernate HQL took 132ms
NHibernate SQL took 134ms
NHibernate Criteria took 173ms
Soma took 184ms
Linq 2 SQL ExecuteQuery took 230ms
Linq 2 SQL took 694ms
NHibernate LINQ took 700ms
Entity framework ESQL took 730ms
Entity framework ExecuteStoreQuery took 735ms
Entity framework took 991ms
Entity framework No Tracking took 1011ms
SubSonic ActiveRecord.SingleOrDefault took 4345ms
(end of tests; press any key)
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Have you heard of [SqlFu](https://github.com/sapiens/SqlFu)? Its equally fast if not faster according to some benchmarking: 1) in their main page and 2) from [FluentData's discussion page](http://fluentdata.codeplex.com/discussions/392421). It would be great if you could include `SqlFu` in `dapper`'s original test suite. – nawfal Feb 13 '13 at 22:58
  • @nawfal I'll take a peek. Re perf: there isn't much to notice between dapper and raw ado.net - but I'll take a look! – Marc Gravell Feb 13 '13 at 23:14
  • Hahaha, thats a very good point, but still.. to calm the religious zealots like me! :P Nevertheless, SqlFu has a syntax very close to dapper.. – nawfal Feb 13 '13 at 23:21
  • 4
    As the SqlFu author, I can tell you that dapper is the fastest BUT it has the least features compared to other micro orms. Dapper is for absolute speed, the others are for a balance between speed and features. – MikeSW Apr 17 '13 at 14:55
  • @user1451111 they *all* use `SqlDataReader`; however, if you mean *just* `SqlDataReader`, then: "hand coded" – Marc Gravell Aug 28 '18 at 08:27
5

If you want automated you are looking for an orm. You might check out some of the lighter wieght ones like massive, ormlite, dapper, and PetaPoco.

Aaron Fischer
  • 20,853
  • 18
  • 75
  • 116
4

Using ADO.NET, what is the fastest way to retrieve data from the database and populate the data into my business objects?

Is there a way to make this process automated? Let's say based on property names and matching them with database field names?

Dapper. Absolutely blazing.

Community
  • 1
  • 1
jason
  • 236,483
  • 35
  • 423
  • 525
  • To add to that; in the dapper test suite we deliberately run a few raw perf tests against all the engines we know of, and dapper wins. Note that some others are close; however, L2S and EF are *nowhere near*. Dapper's design is all about the speed. Some others (simple.data, for example) are aimed primarily at developer convenience (but are still much faster than EF etc) – Marc Gravell Sep 25 '11 at 22:20
  • @MarcGravell Do you take into consideration if these orms are doing the same when benchmarking? For example there can be orms which handles the connection establishment and closing themselves (since in real scenarios you dont run these for many iterations) and running such in loop will be very slow compared to other which runs on one open instance. I think `Subsonic ActiveRecord` in your suite is opening and closing connection every time. So should be `FluentData` which you haven't benchmarked anyway. – nawfal Feb 13 '13 at 23:20