Few Tips to Increase the performance in Asp.net Applications
1..Uses of Connection pooling—
Opening the connection in the database is a intensive task. It may be the slowest operation in asp.net. Furthermore a database has a limited amount of connections available and each connections requires some amount of memory.
This things can be avoid with the use of connection pooling.
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. Connection pool manager maintains a pool of open database connections.
When a new connection request come in, the pool manager checks if the pool contains any unused connections and returns one if available.
If all connections currently in the pool are busy and the maximum pool size has not been reached, the new connection is created and added to the pool. When the pool reaches its maximum size all new connection requests are being queued up until a connection in the pool becomes available or the connection attempt times out.
The following are four parameters that control most of the connection pooling behavior which acn be incuded to the connection strings.
Connect Timeout – controls the wait period in seconds when a new connection is requested, if this timeout expires, an exception will be thrown.
Max Pool Size – specifies the maximum size of your connection pool. Default is 100.
Min Pool Size – initial number of connections that will be added to the pool upon its creation. Default is zero
Pooling – controls if your connection pooling on or off. .
Example
t
ry { conn.ConnectionString = “Data Source=Server name;Initial Catalog=database name;User ID=userid; password=mindfire;Min Pool Size=5;Max Pool Size=60;” conn.Open();
}
Way to close the connection
Method1.. Implicit call
using (SqlConnection conn = new SqlConnection(myConnectionString)) { conn.Open(); doSomething(conn);
}
Method2.. Explicit call
SqlConnection conn = new SqlConnection(myConnectionString); try { conn.Open(); doSomething(conn);
}
finally { conn.Close();
}
2.. Uses of DataReader instead of using DataSet/DataTable
When we have to only fetch the data from tables only for displaying in grid or binding to any control, and NOT for any manipulation or operations, SqlDataReader is the best solution for getting the data from database as compared to SqlDataAdapter.
Using SqlDataReader instead of SqlDataAdapter, we can increases the performance of the asp.net application 1000 times more.
3.. Executing multiple Select statements at a time
What we are generally doing is writing sql queries and getting data by sql Data Reader for forward only approach. We write as many sql queries and data reader for this purpose.
So there is trip to database each and every time when ever we use the datareader which increases the cost of the application.
Asp.net provides a simple way to reduce the over head by connecting to database at a minimum level by writing all the select queries in one string and connect to database through DataReader only once.
This is possible by calling DataReader’s NextResult() method.
So instead of writting many Select statements and many DataReader inside a single page, we can write in the above ways.
Flow
1. Get a connection either from web config or writing the connection strings in the page itself.
2.Write the sql statements attached to command object.
SqlCommand cmd = new SqlCommand(“SELECT PK_ID,CategoryName FROM Categories ORDER BY CategoryName;” + “SELECT Top 10 CompanyName, FK_CITY_ID FROM Customers ORDER BY CompanyName, connection);
3. Declaring the Sql Data reader
SqlDataReader drSql = cmd.ExecuteReader(CommandBehavior.CloseConnection);
4.Get data from DataReader
While(drSql.HasRows) { While(drSql.Read()) { Console.WriteLine(“Result : ” + drSql[0].ToString() + ” ” + drSql[1].ToString()); } drSql.NextResult();
}
5.Close the DataReader
Explanation
When drSql.Read()) executes, it checks the DataReader for the result set, if true then it will print all the rows from the Catagories table.
Then it checks for drSql.Read()), It false then control jump to drSql.NextResult(); At that time it will pick up the result set obtained by the second query.
Then it will print Top 10 CompanyName, FK_CITY_ID from Customers table.
4..Uses of DataSourseMode while using SqlDataSource in Asp.net 2.0 or higher versions
The SqlDataSource control enables us to quickly and easily represent Sql database with out writing a single line of code.When using this control, there is one option DataSourceMode which has two options.
1.DataReader
2.DataSet
By making this mode to DataReader, we can increase the performance of the asp.net application.