Tag Archives: ado.net

Understanding Connection Pooling

For any web application establishing connection of database is most time consuming & slow ( which is always depends upon network speed & memory)

Generally we are using at the most 1-3 connections for an application and we are opening the same connections repeatedly and closing the same ,we can avoid the memory overhead and time for connection establishment by using .Net Supported Connection Pooling. Connection Pooling concept is used irrespective of the number of the user.

For all data providers in ADO.NET by default Connection Pooling is enabled, we can explicitly disable is by adding Pooling = false to Connection String.

Let us understand the basic concept of connection pooling – When application is accessed first time then at that time in memory Container is getting created for maintaining the Opened & used connections.When new user give a call to OPEN connection , it(Connection pool manager) will check if the connection is

pooled (i.e. already opened ) and then if available then it returns the same instead of creating the new.On the other hand when certain call give call to CLOSE connection then instead of closing the connection it will maintain the connection in the Pool ,so that it can able to provide the same to next OPEN call for the same connection.A Connection Pool is released from the memory when the last connection to the database is closed.

SqlConnection sqlConnection = new SqlConnection();

sqlConnection.ConnectionString =

“Data Source=dataSource; Initial Catalog=mydatabase; User ID=userID;   Password=password;Connection Timeout=60; Min Pool Size=5; Max Pool Size=40;”

// A Connection Pool is created when call to Open() is given.

sqlConnection.Open();

As per MSDN,Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created. Connections are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity.Only connections with the same configuration can be pooled. ADO.NET keeps several pools concurrently, one for each configuration. Connections are separated into pools by connection string, as well as by Windows identity when integrated security is used.

We can control the Connection pooling with SQL Server by mainly using following parameters of the ConnectionString ( Create Table) Connect Timeout/Connection Timeout – Default = 15 seconds ,controls the wait period in seconds when a new connection is requested, if this timeout expires, an exception will be thrown.

Parameter Name

Default

Used For

Max Pool Size

100

Specifies the maximum size of your connection pool. Most Web sites do

not use more than 40 connections , but it depends on how long your database operations take to complete.

Min Pool Size

0

Initial number of connections that will be added to the pool upon its creation.

however, you may chose to set this to a small number such as 5 if your application needs consistent response times even after it was idle for hours. In this case the first user requests won’t have to wait for those database                 connections to establish.

Pooling

true

Controls if your connection pooling on or off. When true, the Connection object is taken from pool, or if necessary, is created and added to the appropriate pool. known values are true, false, yes, and no.

Connection Lifetime

0

Value zero means maximum connection timeout.

Connection Reset

true

Determines whether the database connection is reset when being drawn from the pool

Enlist

true

When true, the pool manager automatically enlists the connection in the creation thread’s current transaction context.

Check MSDN for Connection String Parameters for Pooling.

Let is consider One Example

SqlConnection sqlConnection = new SqlConnection();

sqlConnection.ConnectionString =

“Data Source=dataSource; Initial Catalog=mydatabase; User ID=userID; Password=password;Connection Timeout=60; Min Pool Size=5; Max Pool Size=40;”

// First Connection Pool is created when call to Open() is given.

sqlConnection.Open();

SqlConnection sqlConnection1 = new SqlConnection();

sqlConnection1.ConnectionString =

“Data Source=dataSource1; Initial Catalog=mydatabase1; User ID=userID; Password=password;Connection Timeout=60; Min Pool Size=5; Max Pool Size=40;”

// Second Connection Pool is created when call to Open() is given because ConnectionString is different from first ConnectionString

sqlConnection1.Open();

SqlConnection sqlConnection2 = new SqlConnection();

sqlConnection2.ConnectionString =

“Data Source=dataSource; Initial Catalog=mydatabase; User ID=userID; Password=password;Connection Timeout=60; Min Pool Size=5; Max Pool Size=40;”

sqlConnection2.Open();

// sqlConnection2 is assigned with existing Connection Pool A as the same connection string is used.


Take a note of following points,Usefull to get Connection Pool performance effectively

1) Connection Pooling concept mainly depends on the concept of reusable Connection Strings,Connections with same connectionstrings are to kept in a connection pool.So for this reason if we are going use the same connection throughout the code , create it in the Web.config and access it from there ( ref previous blog entry)By using this we can use the same connectionstring without bothering it will be the exact same as previous used, bcoz any change in the connection string causes new connection pool creation.

2) Open the connection only when it is required just before using the connection, because when we open the connection then connection is taken from the previous created pool or new pool is getting created and after opening the connection if we are not using the connection then resources are getting waisted also connections will close only after actual use and leads to the loss of a connection in pool , that might cause creation of new pool or thowing Exception “MAX Pool Size Reached” at time of heavy traffic.

3)Close the connection explicitly by calling Close() after the activity is done or after the tranasction is completed.Closing connection is important beacause when we give call to Close() connection ,connection is being passed/added to connection pool so that other one can use it. Missing the connection close sometimes leads to connection leaks, as number of open connection increased and max pool size obtained.  If you think you are lazy enough to close the connection then open all the connections inside the “using” loop .

using (SqlConnection connection = new SqlConnection(“Data Source=dataSource; Initial Catalog=mydatabase; User ID=userID; Password=password;Connection

Timeout=60; Min Pool Size=5; Max Pool Size=40;”))

{

connection.Open();

}

4)Avoid connection pooling when we are planning to use Basic or Windows Authentication,Connections are pooled according to the connection string plus the user identity.so in the above case we will get one pool per user,which is not available to other users leads to poor performance by leaving large number of connections open while consuming memory.

As per MSDN –

Pool fragmentation is a common problem in many Web applications where the application can create a large number of pools that are not freed until the process exits. This leaves a large number of connections open while consuming memory, resulting in poor performance.

Pool Fragmentation Due to Many Databases

Many Internet service providers host several Web sites on a single server. They may use a single database to confirm a Forms authentication login and then open a connection to a specific database for that user or group of users. The connection to the authentication database gets pooled and used by everyone.However, there is a separate pool of connections to each database, thus increasing the number of connections to the server.

This is also a side effect of the application design. There is a relatively simple way to avoid this side effect, however, without compromising security when connecting to SQL Server. Instead of connecting to a separate database for each user or group, connect to the same database on the server and then execute the Transact-SQL USE statement to change to the desired database. The following code fragment demonstrates creating an initial connection to the master

database and then switching to the desired database specified in the databaseName string variable.

// Assumes that command is a SqlCommand object.

using (SqlConnection connection = new SqlConnection(

“Server=MSSQL1;uid=xxx;pwd=xxx;database=master”))

{

connection.Open();

command.ExecuteNonQuery(“USE ” + databaseName);

}

Detail Ref – MSDN


Advertisements

Frequently Used Enhancements in Ado.net 2.0 / New in Ado.net 2.0

In version 2.0 of ado.net there are lot more features to discuss or to list. I am here listing some the changes which we are using frequently while using the ado.net.

1) DataTable

DataTable supports two new methods Load () & Save () from earlier version

Load () – Load DataReader instance directly into DataTable.

Save () – Store Datatable instance to Storage.

Copy () – Copy DataTable into another keeping schema & data intact.

DataTable dtTable2 = dtTable1.Copy ()

– Now we can Read XML & Xml Schema in DataTable Object Using new methods ReadXml, ReadXmlSchema.

– Also if we want to write DataTable to XML then also two New methods are added WriteXml, WriteXmlSchema.

– DataTable is having new method Merge () for merging two tables.

Suppose dtTable1 & dtTable2 are two tables then we can merge table2 With table1

dtTable1.Merge (dtTable2);

– In 2.0 Datatable class Supports Serialization, supports RemotingFormat property.

The available Serialization Format options are Binary and Xml.

DataTable dtTable = new DataTable ();

dtTable.RemotingFormat = SerializationFormat.Binary;

Detail Ref MSDN

2) DataTableReader

In 2.0 it supports CreateDataReader method. The CreateDataReader method creates an instance of the DataTableReader. DataTableReader is created from a DataSet or a DataTable’s CreateDataReader method, is Forward only container for Datatable(s) data.

The DataTableReader contains all of the rows from the container object, other than deleted rows. The DataTableReader is a lighter weight object than the DataTable and, unlike the DataReader (SqlDataReader), the DataTableReader is disconnected. And Looping through DataTableReader is easy using NextResult ()

In short

-Populate a DataTable/DataSet from a DataReader

Load from DataReaders

-OLEleDbDataReader

-SqlDataReader

-DataTableReader

3) Batch Updates

In .Net 1.1 when are using DataAdapter to update database each command was sent to the database one at a time, causing a lot of roundtrips to the database.

ADO.NET 2.0 has introduced the concept of Batch Updates, which allows us to decide number of commands sent to the database at a given time ,this can increase the performance of your data access layer by reducing the number of roundtrips to the database. DataAdapter.UpdateBatchSize Property – The DataAdapter has an UpdateBatchSize Property that allows you to set the number of commands that will be sent to the database with each request.

UpdateBatchSize = 1, disables batch updates

UpdateBatchSize = X where X > 1, sends x statements to the database at a time

UpdateBatchSize = 0, sends the maximum number of statements at a time allowed by the server

Command.UpdatedRowSource Property When using batch mode, the UpdatedRowSource property of the command can only be set to either UpdatedRowSource.None or UpdatedRowSource.OutputParameters

Detail Refernce – David Hyden explains Batch Updates in Detail

4) DataView.ToTable() –

Create a DataTable from a DataView

-avoiding building from iteration or a new database query as such in 1.1

-DistinctRows parameter

5) More Control Over RowState

DataRow.RowState is no longer read only as it was in previous version 1.1.Two new states are added SetAdded & SetModified. We can use these states to set the RowState of an unchanged row.

6)Asynchronous Data Access – In previous version of ADO.NET ExecuteReader, ExecuteScalar and the ExecuteNonQuery methods are only working in Synchronous mode ,used to block the current executing thread by waiting process to continue.Whereas in ADO.NET 2.0 supports asynchronous data access mode,these methods come with Begin and End methods that support asynchronous execution.

Which helps to reduce delays and improved data access efficiently while using web apps. Example.

IAsyncResult result = Command.BeginExecuteReader()

while (! result.IsCompleted)

{

// until command is getting excuted use this time to use some other activity.

}

DataReader reader = Command.EndExecuteReader(result )

Asynchronous command objects are also provided

SqlDataReaders – BeginExecuteReader & EndExecuteReader

XMlDataReaders – BeginExecuteXmlReader & EndExecuteXmlReader

7) Sql Bulk Copy

In the previous version of ADO.NET, copying a large chunks of data from a source table to a destination table in SQL database had performance drawbacks due to the repeated database accesses/Server roundtrips.

The SqlBulkCopy enables you to perform fast bulk inserts using the .NET Data Provider for SQL Server.This class can be used to specify the source and the target data sources for this copy operation.

Using the SqlBulkCopy feature , we can perform:

A single bulk copy operation , Multiple bulk copy operations, A bulk copy operation within a transaction

Lets consider single bulk copy operation for example.

SqlConnection conn1 = new SqlConnection(Settings.ConnectionString);

conn1.Open();

SqlCommand sqlCommand = new SqlCommand(“Select * from Employee”, conn1);

SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

DataTable dt = new DataTable(“TestTable”);

SqlConnection conn2 = new SqlConnection(Settings.ConnectionString1);

conn2.Open();

SqlBulkCopy sqlBulkcopy = new SqlBulkCopy(conn2);

// Assign destination table

bulkcopy.DestinationTableName = “TestTable”;

// Copying data to table

bulkcopy.WriteToServer(sqlDataReader);

8) Provider / Connection Statistics –

In .net 2.0 for sqlconnection object is provieded with “Statistics” data facility By default this feature is disabled, we can enable this by usng StatisticsEnabled property to ttrue after connection is getting created we can capture “Statistics” stored in the form of IDictionary using “RetrieveStatistics()” of the connection object. Statistics include ConnectionTime,ExecutionTime,Prepares,BuffersSent,SelectRows,ServerRoundTrips etc. important data usefull for determining the effenciency of the application.

If RetrieveStatistics is called without StatisticsEnabled having been called first, the values retrieved are the initial values for each entry. If you enable statistics, run your application for a while, and then disable statistics, the values retrieved will reflect the values collected up to the point where statistics were disabled. All statistical values gathered are on a per-connection basis. Detail Ref – MSDN – Provider Statistics

9) Ado.net 2.0 supports new datatypes – varchar(max), nvarchar(max), varbinary(max) in Sql Sever 2005, also ado.net 2.0 gone one step ahead it is now also providing support for User defined data types (UDTs) in SQl Server 2005

You can also find the detailed list of the new changes in ado.net at – New in Ado.net 2.0