D
dkil1972
In upgrading an application to .Net 2.0 from 1.1 I had an opportunity
to implement some asynchronous calls to the database. In the
application there is a quick search facility that makes 6 calls to 3
different database. This I thought was the perfect scenario for
Asynchronous calls.
Anyway i implemented it and tested it in our win2003 testing
environment calling to a sqlserver instance on the same machine. The
testing worked and I went live with the application. I did not stress
test the application (although i found a nifty stress testing
application which you can get here http://www.opensta.org/).
The live environment is also win2003, with the sqlserver instance on a
seperate machine. Soon after deploying to live we started getting the
following error.
The timeout period elapsed prior to obtaining a connection from the
pool. This may have occurred because all pooled connections were in
use and max pool size was reached.
I did some investigating using the stress testing application, which
was set to invoke 100 requests with a 1 second interval on the test
server. Then using Management Studio Activity monitor i checked the
connection activity. Immediately after starting the requests the
number of connections started to rise, in the end it went from 25 upto
180. It took a full minute for that number to start dropping.
I then used Sql Profiler and saw that for each request a new set of 6
connections was being created but these connections are neither reused
nor closed.
Anyway here is the code...
Here i am registering the async task with the page containing the
user control
SearchDataMgr mgr = new SearchDataMgr(_criteria);
Page.RegisterAsyncTask(mgr.GetEmcProductData(product));
Page.RegisterAsyncTask(mgr.GetEmcCompanyData(company));
Page.RegisterAsyncTask(mgr.GetXpilData(dgXpil));
Page.RegisterAsyncTask(mgr.GetPCDDAmpData(amp));
Page.RegisterAsyncTask(mgr.GetPCDDVmpData(vmp));
Page.RegisterAsyncTask(mgr.GetPcddSupplierData(supplier));
All of the getter method calls used as parameters are implemented in
the same way. here is the first one...
public PageAsyncTask GetEmcProductData(BaseDataList
controlToBindTo)
{
string connectionString =
ConfigurationManager.AppSettings["connectionString"].ToString() +
";async=true";
_conn = new SqlConnection(connectionString);
_emcComm = new SqlCommand();
_emcComm.CommandText = ("usp_SearchProduct");
_emcComm.Parameters.AddWithValue("@criteria", _criteria);
_emcComm.CommandType = CommandType.StoredProcedure;
_emcComm.Connection = _conn;
_conn.Open();
AsyncState state = new AsyncState(_emcComm,
controlToBindTo, "command");
PortalSearchDataAsyncHandler productHandler = new
PortalSearchDataAsyncHandler();
return new PageAsyncTask(
new
BeginEventHandler(productHandler.BeginGetSearchData),
new
EndEventHandler(productHandler.EndGetSearchData),
new
EndEventHandler(productHandler.GetSearchDataTimeout),
state, true);
}//end method
I have a data PortalSearchDataAsyncHandler object that contains the
three handler methods required by the PageAsyncTask object. Here is
the code for the async handler methods...
internal IAsyncResult BeginGetSearchData(object src, EventArgs
e, AsyncCallback cb, object state)
{
AsyncState command = state as AsyncState;
return command.Command.BeginExecuteReader(cb, state);
}
internal void EndGetSearchData(IAsyncResult ar)
{
AsyncState commState = ar.AsyncState as AsyncState;
DataTable resultsTable = new DataTable();
SqlDataReader resultsReader =
commState.Command.EndExecuteReader(ar);
try
{
using (resultsReader)
{
resultsTable.Load(resultsReader);
commState.ControlToBindTo.DataSource =
resultsTable;
commState.ControlToBindTo.DataBind();
}
}
finally
{
commState.Command.Connection.Close();
}
}
internal void GetSearchDataTimeout(IAsyncResult ar)
{
AsyncState commState = ar.AsyncState as AsyncState;
// operation timed out, so just clean up by closing the
connection
if (commState.Command.Connection.State ==
ConnectionState.Open)
commState.Command.Connection.Close();
//_messageLabel.Text = "Query timed out...";
}
Anyway I have had to go back to doing all of this synchronously as I
can't believe that the synchronous calls are more efficient in this
case.
Thanks in advance for any help or suggestions,
Cheers
Dermot
to implement some asynchronous calls to the database. In the
application there is a quick search facility that makes 6 calls to 3
different database. This I thought was the perfect scenario for
Asynchronous calls.
Anyway i implemented it and tested it in our win2003 testing
environment calling to a sqlserver instance on the same machine. The
testing worked and I went live with the application. I did not stress
test the application (although i found a nifty stress testing
application which you can get here http://www.opensta.org/).
The live environment is also win2003, with the sqlserver instance on a
seperate machine. Soon after deploying to live we started getting the
following error.
The timeout period elapsed prior to obtaining a connection from the
pool. This may have occurred because all pooled connections were in
use and max pool size was reached.
I did some investigating using the stress testing application, which
was set to invoke 100 requests with a 1 second interval on the test
server. Then using Management Studio Activity monitor i checked the
connection activity. Immediately after starting the requests the
number of connections started to rise, in the end it went from 25 upto
180. It took a full minute for that number to start dropping.
I then used Sql Profiler and saw that for each request a new set of 6
connections was being created but these connections are neither reused
nor closed.
Anyway here is the code...
Here i am registering the async task with the page containing the
user control
SearchDataMgr mgr = new SearchDataMgr(_criteria);
Page.RegisterAsyncTask(mgr.GetEmcProductData(product));
Page.RegisterAsyncTask(mgr.GetEmcCompanyData(company));
Page.RegisterAsyncTask(mgr.GetXpilData(dgXpil));
Page.RegisterAsyncTask(mgr.GetPCDDAmpData(amp));
Page.RegisterAsyncTask(mgr.GetPCDDVmpData(vmp));
Page.RegisterAsyncTask(mgr.GetPcddSupplierData(supplier));
All of the getter method calls used as parameters are implemented in
the same way. here is the first one...
public PageAsyncTask GetEmcProductData(BaseDataList
controlToBindTo)
{
string connectionString =
ConfigurationManager.AppSettings["connectionString"].ToString() +
";async=true";
_conn = new SqlConnection(connectionString);
_emcComm = new SqlCommand();
_emcComm.CommandText = ("usp_SearchProduct");
_emcComm.Parameters.AddWithValue("@criteria", _criteria);
_emcComm.CommandType = CommandType.StoredProcedure;
_emcComm.Connection = _conn;
_conn.Open();
AsyncState state = new AsyncState(_emcComm,
controlToBindTo, "command");
PortalSearchDataAsyncHandler productHandler = new
PortalSearchDataAsyncHandler();
return new PageAsyncTask(
new
BeginEventHandler(productHandler.BeginGetSearchData),
new
EndEventHandler(productHandler.EndGetSearchData),
new
EndEventHandler(productHandler.GetSearchDataTimeout),
state, true);
}//end method
I have a data PortalSearchDataAsyncHandler object that contains the
three handler methods required by the PageAsyncTask object. Here is
the code for the async handler methods...
internal IAsyncResult BeginGetSearchData(object src, EventArgs
e, AsyncCallback cb, object state)
{
AsyncState command = state as AsyncState;
return command.Command.BeginExecuteReader(cb, state);
}
internal void EndGetSearchData(IAsyncResult ar)
{
AsyncState commState = ar.AsyncState as AsyncState;
DataTable resultsTable = new DataTable();
SqlDataReader resultsReader =
commState.Command.EndExecuteReader(ar);
try
{
using (resultsReader)
{
resultsTable.Load(resultsReader);
commState.ControlToBindTo.DataSource =
resultsTable;
commState.ControlToBindTo.DataBind();
}
}
finally
{
commState.Command.Connection.Close();
}
}
internal void GetSearchDataTimeout(IAsyncResult ar)
{
AsyncState commState = ar.AsyncState as AsyncState;
// operation timed out, so just clean up by closing the
connection
if (commState.Command.Connection.State ==
ConnectionState.Open)
commState.Command.Connection.Close();
//_messageLabel.Text = "Query timed out...";
}
Anyway I have had to go back to doing all of this synchronously as I
can't believe that the synchronous calls are more efficient in this
case.
Thanks in advance for any help or suggestions,
Cheers
Dermot