DataReaders, do slow clients cause number of connections to exceed max connections

Question

When using DataReaders, and in this example Response.Write (but I guess it would apply to any Controls), does slow clients affect number of connections? The average connection pool usually has about max 15 connections. So if 100 users hit the application and 50 of them have very slow connections with high latencies, will the number of connections exceed the maximum number of connections?

If using a DataTable, I'd imagine that all the data is loaded into memory so the connection isn't left open while doing the Response.Write.

Example:

using(SqlDataReader reader = DataUtility.GetReader("select * from employees"))
{
   while(reader.Read()) 
   {
      Response.Write(reader["emp_id"]).ToString();
      Response.Write(reader["username").ToString();
   }
}

vs

DataTable emps = DataUtility.GetDataTable("select * from employees");
foreach(DataRow row in emps.Rows)
{
      Response.Write(row["emp_id"]).ToString();
      Response.Write(row["username").ToString();
}

in your query `"select * from employees` are there only `2` fields in the employees table..? if not then refactor your query to return on `emp_id && username` — MethodMan, May 05 '14 at 18:29
I would not do it the way you are doing, I would go to the DB, get my objects created, close the connections, then respond to client. — T McKeown, May 05 '14 at 18:32
You can add more connections in your connection pool by changing the query string. — quarksoup, May 05 '14 at 18:35
Seeing IDataReader code in the same method as "Response.Write" is painful. You have no separation of concerns here. I would learn how to create a basic DTO object, populate one or a collection of them using an IDataReader, close/dispose of the IDataReader as fast as possible, and then push the single or Icollection of DTO objects up to your presentation layer. — granadaCoder, May 05 '14 at 18:36
Here is an example. http://stackoverflow.com/questions/13650443/why-is-datatable-faster-than-datareader/15908284#15908284 — granadaCoder, May 05 '14 at 18:37

score 1 · Accepted Answer · answered May 05 '14 at 18:39

The response is buffered on the server, so the code won't wait for the response to actually be sent to the client. A slow connection doesn't make the server code run slower.

Still, your server code will be more efficient if you first fetch all the data that you need from the database, then close the database connection and use the data to create the response. That way you can have the database connection open as short a time as possible, using less database resources.

Side note: I see that you use select * in your code, which fetches all fields from the table. You should specify only the fields that you actually will use, so that you fetch as little data as possible from the database.

"if you first fetch all the data that you need from the database, then close the database connection and use the data to create the response" Yes! Don't miss this important part. Initiate, consume/use the datareader as quickly as possible, then close/dispose of them. Converting your datareader to a basic DTO would be one tried/true way of doing this as quickly as possible. — granadaCoder, May 05 '14 at 18:49

score 0 · Answer 2 · answered May 05 '14 at 18:33

We can't answer this question with the code given. The number of connections depends on when you open and close the connection in your code. If you wait for all the data to be sent to client before you close your connection then a slow connection would have an effect.

Most C# code is not written this way. Typically you open a connnection, do some work and close the connection. Then do additional processing and send the response to the client.

Show us more code, I expect you are not closing you connections and this is causing your problem.

DataReaders, do slow clients cause number of connections to exceed max connections

2 Answers2