Subscribe: Atom or RSS

The Valley of Data Access - Part 6

Done. The database access code for Charlie has now been refactored.

All duplicate code has been pulled out of the many Mapper classes and moved into a single Helper class. The Helper class has also taken on the responsibility of performing the cascading security and localization work. Even better, the cascading logic no longer requires repeated trips to the database. Where previously Charlie required an embarrassing 49 hits to the database in order to serve the first webpage, it now requires twelve. Subsequent page requests require about eight hits.

Charlie currently hits the database once to retrieve an business entity or an entity collection, and then hits the database again to retrieve the security roles for the entity or collection. As I mentioned in my last weblog entry, I had a go at combining these two queries into one. I believe it can be done, but the approach involves a few penalties. The first penalty is that I’d have to swap from using fast DataReaders to relatively slow DataSets. The second, and greater, penalty is that I’d have to introduce a tight coupling between Charlie and its Security plugin, so that they can “gang up” their database queries. The third penality is that ganging up the queries would make it awkward for a plugin to use a different data store. Maybe the Weblog plugin could make use of the free MySQL database available on my WebHost4Life account, while the Security plugin uses the Microsoft SQL Server database. That flexibility appeals to me.

So I have put aside the idea of trying to combine the queries. If the extra database queries ever become a problem, that is the time I will look again at the issue. And even if the issue returns, a hardware solution might prove to be better than a software solution. If a website ever becomes so highly trafficked that the extra hits incur a major performance penalty, then that website might warrant a dedicated server. But again, it is a problem only when it becomes a problem.

One slight improvement to Charlie’s data access code is the introduction of a SqlConnectionManager. Previously, each Mapper created, opened, used, and then closed a new connection. Now, each Mapper pulls an open database connection from the SqlConnectionManager and then, when it’s finished with the connection, returns it to the Manager. So that Charlie doesn’t end up hanging on to open connections for too long, the SqlConnectionManager receives a call to its CloseConnections method at two points in the ASP.NET page lifecycle. The first point is before the HttpHandler starts to execute, and the second point is when the page request has finished processing. What this means is that the absolute longest that Charlie holds an open database connection is 0.4 seconds, and it is usually much more brief. Here is the code for the simple SqlConnectionManager:

internal static class SqlConnectionManager
{

   private static String contextKey = 
      "Charlie.Framework.Services.DataAccess.SqlConnectionManager";

   internal static SqlConnection GetConnection(String connectionString)
   {
      HttpContext context = HttpContext.Current;
      Hashtable connections = context.Items[contextKey] as Hashtable;
      if (connections == null)
      {
         connections = new Hashtable();
         context.Items[contextKey] = connections;
      }
      SqlConnection connection = 
         connections[connectionString] as SqlConnection;
      if (connection == null)
      {
         connection = new SqlConnection(connectionString);
         connections.Add(connectionString, connection);
      }
      if (connection.State != ConnectionState.Open)
      {
         connection.Open();
      }
      return connection;
   }

   internal static void ReturnConnection(SqlConnection connection)
   {
      // nothing yet
   }

   internal static void CloseConnections()
   {
      HttpContext context = HttpContext.Current;
      Hashtable connections = context.Items[contextKey] as Hashtable;
      if (connections != null)
      {
         IEnumerator enumerator = connections.GetEnumerator();
         while (enumerator.MoveNext())
         {
            DictionaryEntry entry = (DictionaryEntry)enumerator.Current;
            SqlConnection connection = entry.Value as SqlConnection;
            if (connection != null)
            {
               if (connection.State != ConnectionState.Closed)
                  connection.Close();
               connection = null;
            }
         }
         connections.Clear();
      }
   }
}

With the exception of the first page request and its inexplicable two-second delay, Charlie is taking between 0.02 and 0.4 seconds to serve a page request. While the slower responses are always due to database activity, the response times still seem fast enough. I am not going to try to optimise the code further.

So the bulk of Charlie’s Business layer and Persistence layer is done. I’m now going to move onto the Controller layer. That should be easy, because I am going to pirate some code. Stay tuned for some swashbuckling tales.

by Alister Jones | Next up: “You Are Here” →

2 comments

----

Recent Posts

• The Valley of Data Access - Part 5
• Charlie Wears Lead Boots
• The Valley of Data Access - Part 4
• The Valley of Data Access - Part 3
• The Valley of Data Access - Part 2
• The Valley of Data Access - Part 1
• The WebHandler Object
• Static File Handlers
• Charlie’s Birth
• Think Big