Asp.net - Dataset vs Datareader

To better improve the performance of a solution, we have to
understand the DataSet and DataReader and they have to be used correctly in the places they needed. Let see how they work...

DataReader
Datareader is like a forward only recordset. It fetches one
row at a time so very less Network Cost compare to DataSet(Fetches all the rows at a time). DataReader is readonly so we cannot do any transaction on them. DataReader will be the best choice where we need to show the data to the user which requires no transaction ie reports. Due to DataReader is forward only we cannot fetch the data randomly. .NET Dataproviders optimizes the datareaders to handle the huge amount of data.

DataSet
DataSet is always a bulky object that requires lot of memory space compare to DataReader. We can say the dataset as a small database coz it stores the schema and data in the application memory area. DataSet fetches all data from the datasource at a time to its memory area. So we can traverse through the object to get required data like qureying database.

The dataset maintains the relationships among the datatables inside
it. We can manipulate the realational data as XML using dataset.We can do transactions (insert/update/delete) on them and finally the modifications can be updated to the actual database. This provides impressive flexibility to the application but with the cost of memory space. DataSet maintains the original data and the modified data seperately which requires more memory space. If the amount of data in the dataset is huge
then it will reduce the applications performance dramatically.

The following points will improve the performane of a dataset...

1. Don't use the commandbuilder to generate the sql statements.
Though it reduces the development time the query generated

by the command builder will not be always as required. For example
To update the details of an author table the command

builder will generate a query like this ...

Query generated by CommandBuilder

UPDATE authors
SET
au_id = ?, au_fname = ?, au_lname = ?, phone = ?,
zip = ?, state = ?, city = ?, address = ?
WHERE
(au_id = ?)
AND (address = ? OR ? IS NULL AND address IS NULL)
AND (au_fname = ?)
AND (au_lname = ?)
AND (city = ? OR ? IS NULL AND city IS NULL)
AND (phone = ?)
AND (state = ? OR ? IS NULL AND state IS NULL)
AND (zip = ? OR ? IS NULL AND zip IS NULL)

But we can write much more efficient query than the above like
this ..
UPDATE authors
SET
au_id = ?, au_fname = ?, au_lname = ?, phone = ?,
zip = ?, state = ?, city = ?, address = ?
WHERE
(au_id = ?)

2. Always select the columns required in the quries. Don't use "select * from ". Take an example of authors table which has the author id , name, address, image. For showning the author name,address if we use select * then the image column also fetched from the database which requires more memory and NetWork Cost will be more due the amount of data. This will reduce the applications performance.

3. Always try to use the parameter collection for the stored
procedures. We can execute the stored proceduers in two ways.
a. we can create a string as a query and can execute it.
Ex "getAuthor(7689)".
b. we can create a command object. Assign the Stored Procedure
name to the commandtext property. Specify the commandtype as stored procedure. Create a parameter for the value passed to the SP and execute.
Ex:
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandText="getAuthors";
sqlcmd.CommandType= CommandType.StoredProcedure;
SqlParameter sqlparam = new SqlParameter
("@au_id",SqlDbType.Int);
sqlparam.Value = 7689;
sqlcmd.Parameters.Add(sqlparam);
sqlcmd.Connection= con;
sqlcmd.ExecuteReader();

There are lot of difference between the way the two quries executed.
The first query will be sent to the database server as a string though we think we are passing the author id as int. The database server will parse the query and will determine the datatype of the parameter. This extra processing will increase the execution time.
But while executing the second query the native datatype of the
parameter is sent to the database server as network packets throuh RPC.