4

I have an asp.net web page which interacts with a SQL Server database, grabs some data and then returns an XML response (which I feed into Freeswitch using xml_curl).

Because Freeswitch (FS from now on) does not store cookies, each request creates a new session.

When the number of requests gets too much (about 97 to 100), the SqlConnection.Open() method gets timeout from the SQL Server Instance, which then results in HTTP Error 500.

To test my assumption, I have created a small script using PHP and cURL, which make repeated requests to my asp.net page. If I store cookies (and thus sessions) in the PHP script I can make 10000 successful requests in almost 314 seconds.

But without sessions, I get stuck at about 97~100 requests, and then I get HTTP Error 500.

Is there anyway to overcome this problem?

==Edit==
Here is how I interact with the database:

String connectionString = WebConfigurationManager.ConnectionStrings["SqlServerConnection"].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "Select * from dbo.Template where Name = '" + name + "'";

Template template = new Template();

connection.Open();

SqlDataReader reader = command.ExecuteReader();


if (reader.HasRows)
{
    reader.Read();
    template.Name = reader["Name"].ToString();
    template.XMLContent = reader["XMLContent"].ToString();
}
else
{
    template.Name = "";
    template.XMLContent = "";
}

reader.Close();
connection.Close();

return template;

And the Template table has these fields:

ID => int, identity, primary key
Name => nvarchar(255), unique
XMLContent => ntext
John Smith
  • 233
  • 1
  • 2
  • 12
  • How are you calling the database? Are you closing the connection when you no longer need it? Have you disabled connection pooling ? It would help if you posted the code that accesses the database. You should also check the SQL statements that you execute to ensure you don't cause blocking in the database. BTW ASP.NET sessions have nothing to do with accessing the database - unless have added code to bind the two, like opening the connection on session start, closing it on session close. – Panagiotis Kanavos Feb 06 '12 at 14:58
  • I added the code. And as I said, the code runs 10000 requests if I store the cookies (and thus session). – John Smith Feb 06 '12 at 15:06
  • 4
    As an aside, you will want to avoid SQL injection by using Parameters instead of string concatenation. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx – Daniel Moses Feb 06 '12 at 15:30
  • To add to the sideband comments, if you don't need the full ASP.NET Page framework, consider a generic handler ('`ashx`') instead. It won't help with *this* problem, but will be more performant in resources and time for your webserver. – AakashM Feb 06 '12 at 15:51
  • @DMoses I wanted to keep things simple here. I am aware of that. Thanks. – John Smith Feb 07 '12 at 05:18

3 Answers3

3

It appears you are using a connection pool. By default these pools have a max of 100 connections to your SQL server and queue any additional connections. The queue has a timeout (default 15 seconds) which can be extended if you wish to queue your requests longer. This means that you might get backed up on your server. You can also increase the pool max size if your SQL server can handle it.

Here is how you increase your connection settings by adding these parameters:

  • Timeout=60
  • Max Pool Size=150
  • etc etc
Daniel Moses
  • 5,872
  • 26
  • 39
  • Thank you man. I disabled the connection pooling with `Pooling=false` (since there is only one page using this connection string, it does not matter). Problem solved. – John Smith Feb 07 '12 at 06:07
  • Using pooling on a single page does 2 things in your scenario: 1) let's you limit the amount of connections your application is using against the SQL (thus not pegging the SQL server) and 2) Makes creating a connection to the sql server much faster. This can be noticeable if your SQL statement runs in 10ms but obtaining a connection takes 300ms. In any case, glad that worked for you. – Daniel Moses Feb 07 '12 at 15:05
1

Some steps to impove this code.

  1. If you do not need session, disabled it for this page so not cookie is going to be made.
  2. Use some cache here base on the name If the request for name is the same, get it from cache and not open the database
  3. Use a static variable to read only one time the connection string.
  4. Place it on [try catch | using] , to be sure that you close the connection in case of failure
  5. Maybe you can try a mutex lock logic, to avoid too many request together.
  6. Use parameters on your sql call.
Aristos
  • 66,005
  • 16
  • 114
  • 150
1

In addition to @Aristos suggestions:

  • Use Async-Pages!

Example and "Benchmark"

Some time ago I asked nearly the same question here on so

Community
  • 1
  • 1