Very Helpful information. Thanks Cathi.

I do have a question though I am not sure if it pertains to .NET forum.

In SQL Server, if the Stored Procedure itself is executing dynamic SQL's, whould it still be 3% faster than executing those SQL directly?

>>Using a SQL Server Stored Procedure was 3% faster that using a dynamic Select statement. The reason this difference isn't greater is SQL Server's ability to compile dynamic statements and store them temporarily.



>Hi All,
>
>I was talking with Stephen Walther tonight (author of ASP.NET Unleashed) and he has done some testing of data access with ASP.NET using different scenarios. I thought I would pass on his results.
>
>Using a DataReader instead of a DataSet is 16% to 50% faster, depending on the configuration of the DataSet like enforcing constraints.
>
>Using an ArrayList resulted in the same speed performance as a DataReader. Using an ArrayList gives you the added ability to have static representation of the data whereas the DataReader does not.
>
>Using the SqlClient set of classes is 52% faster than using the OleDb classes when accessing SQL Server.
>
>Using C# code to access data via a DataReader object is 4% faster than the VB.NET equivalent with VB.NET Option Strict set ON.
>
>Using a SQL Server Stored Procedure was 3% faster that using a dynamic Select statement. The reason this difference isn't greater is SQL Server's ability to compile dynamic statements and store them temporarily.
>
>Accessing columns in a DataReader by ordinal is 14% faster than by name. Using the GetString method is even slower by 11% than accessing by name.
>
>There is a 4% penalty of accessing DataReader column names using the wrong case. ADO.NET has to go through an extra step to resolve the inproper case.
>
>Using an Inline DataReader is 63% faster than using a DataGrid. A DataGrid has a lot of overhead associated with binding the data.
>
>Using a DataGrid with a DataReader is 6% faster than using a DataGrid with a DataSet.
>
>Using DataGrid Templates is 24% slower than not using templates.
>
>If you use DataGrid Templates, databinding using explicit casting is 9% faster than databinding using the Eval function.
>
>Explicit casting:
>

>< %# ((DbDataRecord)Container.DataItem)["ProductName"]   
>

>
>Eval Function:
>

>< %# DataBinder.Eval(Container, "DataItem.ProductName")   
>

>
>Using the DataGrid's ItemDataBound event handler to databind controls is 46% slower than using explicit casting shown above.
>
>Caching a DataSet to use in a DataGrid is 72% faster then not using caching.
>
>Using a cached DataSet is significantly faster (he didn't have the exact numbers calculated) than using a DataReader.
>
>Using AutoGeneratedColumns for a DataGrid is 24% faster than specifically selecting columns.