What is the fastest way to grab records from a database table, from within an ASP.NET page?

To begin with, we were shown the test setup. Microsoft's Application Center Test (part of the Enterprise edition of Studio) was used, and the presenter walked us through its features. He talked about how he had to write a Timer module to accurately get JUST the page execution metrics that he wanted to show. The time utilities in the .NET framework are only accurate to 10 milliseconds, so he ended up writing a wrapper around QueryPerformanceCounter, as it gave accuracy of 1 millionth of a second (on his setup).

The tests hit the same page 1050 times, and the first 50 results were discarded (so pages were compiled and loaded, and SQL Server was nice and warm).

Some of the tests were:

Using a DataReader vs. a DataSet

The DataReader was of course faster. It was faster by 16% in this particular case.

Using an ArrayList of Data Holders

People sometimes put the data from a reader into a data holder class (Value Object). This approach was almost no different to the pure DataReader approach (as expected).

SQLDataReader vs. OleDBDataReader

Going with native drivers is always better. The SQLDataReader was 115% faster than going through OLE.

Using Inline SQL vs. Stored Procedures

There was next to no difference in performance of using the stored proc versus the inline SQL in this experiment. This is partly due to the fact that this is JUST a SELECT statement, and SQL Server can cache these statements. This doesn't mean that you shouldn't use stored procedures!

DataReader Column Reference: By Name, Ordinal, or GetString()

The choices:



dr["ProductName"]
dr[0]
dr.GetString(0)
The order of speed? dr[0] was the fastest, followed by dr["ProductName"], followed by dr.GetString(0) as it has to do the conversion.

Inline (DataReader) vs. Controls (DataGrid)

The inline script was 233% faster than a DataGrid component.

ViewState enabled, or disabled

DataGrid with ViewState disabled is 66% faster than DataGrid with ViewState enabled.

Caching comes into play: DataGrid with: no caching, data caching, output caching

With data caching you get a 637% faster operation. With output caching you get an infinite improvement ;)

This topic was taken in a tech seminars and the data was available in one of the blogs of MS techie.