It is hard to be original on the web, everything has been said before. In my blog on datareaders I talked about some performance tests I had done on a datareader. Both Joseph Cooney in a response and Grant Kilian in his blog point to an extensive article which compares the performance of different data access methods in a real world situation, including using a datareader. One of their conclusions is that a datareader only pays off when you are accessing larger amounts of data. The main conclusion is that you should test your own situation to find out what works best.
A nice way to test the performance is the ANTS profiler. (Yes, I do visit the dnj sponsors). You can download a fully functional trial which works for 14 days. Using ANTS is easy. Start it, run your app and see the results in ANTS. This also works for asp.net apps. Tell ANTS you are going to profile a web-page (or service). Start your browser, run the aspx'-es and when you're finished click the "Stop profiling" button. The results in ANTS are very detailed, you can drill down to the sourceline. After reorganizing my code I used ANTS to find the bottleneck's in data access performance. In this screenshot you can see some of the results.
What can be seen from this ? Binding the data to the grid is a fraction faster using a reader. But it is a pity you cannot design your grid, you do need a dataset to do that. The other thing taking time is the Fill operation of the data-adapter. But in the real world these differences are academic. The "user-experience" in both approaches is the same. On my overstressed machine, running IIS and sql server local, It takes about a second to build up the huge page, there is no human noticeable difference.
The article mentions a drawback of datareaders which can prevent an application from scaling. As long as you don't close a datareader there is a connection to the database which cannot be used by other code. The authors tested the implications of this by limiting the number of connections and setting a small delay between the execution and the closing of the reader. The result was that the dataset soon outperformed the datareader as the number of requests grew. So a datareader does not scale that well.
Which brings me to one final point worth discussing. How to handle database connections in a web page in general ? What I do is open the connection in the page_load event and close it in the page_unload event. The nice thing is that the page_unload event always gets fired, also when an exception is thrown on the page. And my connection pool will not get polluted with unused connection waiting to time out. More on all of this in an upcoming article (on the datagrid) which is at the moment still somewhere on Donny's or Doug's desk.
Blog on !
Peter