Of course this is a big topic. We got LINQ, SubSonic and all the great ORM Mappers now, but when it comes to performance it’s still a critical decision. I never really cared much about that when developing Desktop Applications as you usually don’t have to deal with more than one user at the same time.
We run several Web Applications based on ASP.NET and many of them got more than 10 Million Pageviews per Day. So it is really critical to have a good Database Backend. I just want to talk about the direct Data Access, so I leave out all the caching stuff, that is a topic of its own. I tested the DataReader, the DataSet and some of the popular ORM tools. It’s a standard SELECT Query of 10.000 Database Rows. Here’s what I found out regarding performance:
Obviously the fastest way of accessing Data is still the DataReader with about 20-30ms.
The DataSet raises the bar to about 120ms already.
After that we got LINQ with about 210ms.
There’s ADO.NET with a whopping 400ms.
And the slowest ORM Mapper is unfortunately nHibernate 2.1 with around 580ms.
So if you have to develop the Data Access for big Web Applications you should still stick as much as possible to the DataReader, no matter how (un)comfortable it is, at least for the really critical sections.
By the way: Really big sites like Plenty Of Fish or MySpace which are based on ASP.NET are not using Caching at all. We’re talking about more than 1.2 billion page views/month here. It is not used because as soon as the data is put in the cache it’s already expired. Furthermore no components and just very simple structures including if, then and for loops. Obviously a Load Balancer makes very much sense here. You usually exclude a Database which is being used for searching to save load from the main database.
ORM Mappers are sweet, but it’s still critical to use them if your Website might go through the roof sometime.