Click here to Skip to main content
14,976,254 members
Articles / DevOps / Load Testing
Posted 18 Apr 2007


19 bookmarked

Sample Session Providers with PostgreSQL

Rate me:
Please Sign up or sign in to vote.
3.50/5 (3 votes)
18 Apr 2007CPOL10 min read
Several custom Session State providers for TextFile, ODBC, and PostgreSQL.


In ASP.NET 2.0, Microsoft has opened up the Session State mechanism so that we can use their provider concept and create our own providers to store ASP.NET session state data.


Since the days of ASP, Microsoft has provided us with a nice mechanism to store data between page calls in an object called Session. For the longest time, Microsoft has controlled how the session stores its data. In ASP.NET 1.1, they gave us three options for storing session data: In Process, State Server, and SQL Server. In ASP.NET 2.0, the same options are available, but there's also a Custom option via their provider model. Microsoft has provided us with several Session State provider examples, but I had to see how well they worked.


If we made a custom provider to store session state data, would it work well enough to use in a high performance system? It would need to manage locking issues. It would need to support a web cluster. Is there anything else it needs to support? The Microsoft documentation provides us with a decent list of issues to manage with a custom session state provider.

The Providers

A quick look around found two providers nearly ready to go: an ODBC provider in the Visual Studio 2005 documentation, and "The ASP.NET 2.0 Provider Model" paper by Microsoft. I decided to give these a try to see if they would work. Surprisingly, they almost did. Once I had these working, I wanted to take a little leap to a great Open Source and free RDBMS: PostgreSQL. Since PostgreSQL supports Stored Procedures, I made a provider that doesn't use Stored Procedures and another that does.

Observations of PostgreSQL

Having worked with Sybase, Informix, SQL Server, Oracle, and a little DB2, I wanted to see how well PostgreSQL would mature even with MySQL around. After working with it for a little bit, it has matured very well. A little while back, I could easily say that it was ahead of MySQL, and I can still say that today. It's too bad more folks don't use it. Perhaps, if they read the license to MySQL (that I believe has recently changed) and the functionalities of the two products, they might have used PostgreSQL more.

So, what makes PostgreSQL cool? For folks using Oracle, you'll find the PostgreSQL native Stored Procedure language, PL/pgSQL, easy to change to from PL/SQL. You can even write Stored Procedures in Tcl, Perl, and Python. OK, they have something for the Java folks too. Sorry, the C# piece is still in the works. It has more index types to choose from than many of the mainstream RDBMSs. It has the similar concepts of Tablespaces and Schemas. A decent security strategy for users and roles. It supports cubes, custom types, GIS, full text searching, and I'm going to stop before I spoil its thunder. Did I mention it does handle terabyte databases? Did I mention it's free?

So, what makes PostgreSQL not cool? Well, there's the documentation. Luckily, the active community offers help with all kinds of issues. You can even get weekly emails of items they are fixing. ("Email of items they are fixing". Isn't that a cool thing?) I'm sure the documentation will improve with time, the question is can it keep up with all that's offered now and in the future. The UI administration tools are improving, but need a little more work. I felt like I was back in the days of Oracle 7 with the admin tools. I couldn't find an "out of the box" debugger for the Stored Procedures. By observation, on Windows XP SP2, I noticed several processes with the same name starting when I performed load testing, so it's somewhat hard to say if the use of threads has been optimized. Lastly, the free version of the .NET Data Provider does work, but needs a little more touching up to support Microsoft's Enterprise Library, and the documentation needs some touching up too. I actually think the output stream from PostgreSQL needs some touching up to work really well with .NET, but most RDBMSs need to touch up on this. For the majority of applications, these shortcomings are not major hurdles, and you can't beat the cost.

A Few Little Additions

In the sample providers, there's sample code to delete old sessions. The question to ask is, "how do you do this?" For SQL Server, Microsoft uses an agent to call a query to clean old sessions. I thought this was nice, but I wondered about using the application end event. In a default configuration, when there's no activity for 20 minutes, ASP.NET will cycle the site.

You can add the following to the system.web section of the Web.config to change the settings for when to cycle the server:

<!-- Add to system.web to control at application level -->

When a site shuts down nicely, the application raises its end event. To make sure all the providers here have a common method to remove old sessions, I added an interface to the providers with the method. The interface is a simple one:

public interface IThreeOaksSessionProvider
    void DeleteExpiredSessions();

Once we had a common method, it's easy to add a call to clear the expired sessions to the application end event in global.asax.cs. Here, I check the Web.config to see what the provider is to call the appropriate one.

protected void Application_End(object sender, EventArgs e)
    Debug.WriteLine("Application_End", "global.asax.cs");
    //We expect the application to know the provider they are using.
    //So don't do anything fancy to figure out which one's being used.
    Configuration cfg = 
    SessionStateSection configSessionStateSection = 
    if (configSessionStateSection != null)
        IThreeOaksSessionProvider sessionProvider = null;
        switch (configSessionStateSection.CustomProvider)
            case "PostgreSQLSessionProvider":
                sessionProvider = new PostgreSQLSessionStateStoreSP();
            case "OdbcSessionProvider":
                sessionProvider = new OdbcSessionStateStore();
            case "TextFileSessionStateProvider":
                sessionProvider = new TextFileSessionStateStore();
        if (sessionProvider != null)

Since you should know the provider you are using up front, the application end procedure becomes much easier. The following shows what it would be for PostgreSQLSessionStateStoreSP:

protected void Application_End(object sender, EventArgs e)
    Debug.WriteLine("Application_End", "global.asax.cs");
    IThreeOaksSessionProvider sessionProvider = new PostgreSQLSessionStateStoreSP();

For those thinking they could retrieve the Session State Provider from the Session or Request or Application, you may have a difficult time. Why should there be any session state provider around when the application ends? A session hasn't caused the application end event to fire. Using lots of Reflection, you may actually retrieve one, but it's not easy. Since in this sample we know our provider, I've kept it simple (KISS).


So which one worked best? It's somewhat of a tough call. I'm afraid I don't quite have the environment to perform the tests best. Basically, I'm using a wimpy laptop running Windows XP Professional SP2, when I really need a web cluster. I dare not run two or three virtual machines of Windows Server at the same time on my laptop.

In Process Provider:

Being an in-process, this provider should work the fastest on my wimpy laptop. For the test I set up, it ran around 340 to 360 requests per second with 5 connections. I'd hope our custom providers would match the SQL Server out of process provider that seems to run about 1/4 the requests per second as the in-process provider. See Offering a better (ODP Compliant) ASP.NET Session object for a few test results.

Text File Session Provider:

This provider originated from a Microsoft sample. It worked pretty well on my laptop. It ran between 100 to 120 requests per second most of the time with 5 concurrent sessions. The requests per second fluctuated a lot due to caching and such. In a web cluster, setting up a common area to write to may be a security issue. The set up of the hard drive may also be an issue since the OS is somewhat acting like the database. If you are going to test this one, keep an eye on the number of files made.

ODBC Session Provider:

I tried to keep this provider as close as possible to the Microsoft sample, so no Stored Procedures where used. It did not perform that well. I tried PostgreSQL as the database on Windows XP. It ran less than 20 requests per second. I tried with MS Access, and it didn't perform that well either. These results surprised me.

PostgreSQL Session Provider No Stored Procedures:

This provider ran fairly well. It ran between 100 to 150 requests per second most of the time with 5 concurrent sessions. Within a web farm, I'm sure it could handle many more requests per second. This provider is very close to the ODBC Session Provider, but uses the PostgreSQL .NET Data Provider, Npgsql.

PostgreSQL Session Provider Using Stored Procedures:

This provider ran fine. It ran between 100 to 120 requests per second most of the time with 5 concurrent sessions. Why it didn't perform better is a mystery right now. Since there's less traffic between the ASP.NET server and PostgreSQL server, it might do better than the PostgreSQL Session Provider with no Stored Procedures when ASP.NET and PostgreSQL are on different servers. I did normalize the session table into two tables, one to hold the session item and the other to hold the application name. I'd intended to make the table holding session items smaller and indexes on the table smaller and perhaps more efficient. I'm sure, in a web farm, this provider could handle many more requests per second.


At first, I thought Microsoft would have used a class with static methods. I also thought Microsoft would have written up how the Session State provider needs to manage thread issues. However, I didn't see this in the documentation. Using Reflection and walking through the various System classes, it appears Microsoft actually makes a new instance of the provider as needed and stores them for a period of time. My first thought was, "No way". So, I created another provider to test things out by tracing, and it definitely appears they do create a new instance of the provider as needed. ASP.NET will re-use an instantiated provider as needed.

Knowing ASP.NET re-uses Session State providers means we can look for little ways to speed things up. One way to speed things up is to limit the creation of complex objects like the Command object. For now, I've included PostgreSQLSessionSPCommon that re-uses Command objects, but not connections. Finding a way to speed up connections would really help. However, the current version of the PostgreSQL .NET data provider, Npgsql, needs a little more to make this happen well. I feel Npgsql in its next version will provide better support for connection management. We'll just have to wait and see. For the sample, depending on the load and user activity, re-using the command objects provides a nice little performance gain.

The Solution

The sample solution has two projects. The SessionProviders project contains the providers and dependencies. The TestSessionProviders project is the website I used for testing. The default page has various inputs to place into the session state. One button places the inputs into session state. Another button on the default page reads the values from session state and displays them on the screen. Another button gracefully shuts down the application on the server receiving the request. One link on the default page takes the user to a page where it reads the session state in read-only mode. The read-only page has a button to retrieve the values in session state. Another link on the default page takes the user to a page where it does not need the session state. The last link takes the user to a page where frames are used and each page needs the session state in read-write mode. The Web.config has a section for each of the providers; only one can be uncommented at a time. Since the providers use connection strings, there's a connection string section. You may need to adjust the paths to the appropriate locations for the text file provider and ODBC DSN-less connection strings. I have placed debug statements at the beginning of many methods to help observe what's happening. If you have a tool for observing code during execution, use it. At the Solution level, there's a solution folder containing the scripts to set up the PostgreSQL database for the providers here.

Parting Words

Well, here you go, a few Sesssion State providers to try out. Please take the above results with a grain of salt as there are many factors that can affect performance. Hopefully in the near future, I'll have a chance to see what's happened with Npgsql. I'm sure the next versions of PostgreSQL on Windows will produce even better results.

If you try the sample PostgreSQL providers, you will have to install PostgreSQL 8.2.3 and the PostgreSQL .NET Data Provider, Npgsql. The SQL to create the tables and Stored Procedures are a part of the .NET solution. You'll have to adjust the SQL as needed for your database.


None yet.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Tim Schwallie
Team Leader
United States United States
A biography in this little spot...sure.
I've worked at GTE HawaiianTel. I've worked at Nuclear Plants. I've worked at Abbott Labs. I've consulted to Ameritech Cellular. I've consulted to Zurich North America. I've consulted to International Truck and Engine. Right now, I've consulted to Wachovia Securities to help with various projects. I've been to SHCDirect and now at Cision.

During this time, I've used all kinds of tools of the trade. Keeping it to the more familier tools, I've used VB3 to VB.NET, ASP to ASP/JAVASCRIPT/XML to ASP.NET. Currently, I'm developing with C# and ASP.NET. I built reports in Access, Excel, Crystal Reports, and Business Objects (including the Universes and ETLS). Been a DBA on SQL Server 4.2 to 2000 and a DBA for Oracle. I've built OLTP databases and DataMarts. Heck, I've even done Documentum. I've been lucky to have created software for the single user to thousands of concurrent users.

I consider myself fortunate to have met many different people and worked in many environments. It's through these experiences I've learned the most.

Comments and Discussions

GeneralUm.. Pin
svoeller1029-Aug-07 12:27
Membersvoeller1029-Aug-07 12:27 
QuestionLicense? Pin
onkelborg8-Jun-07 4:37
Memberonkelborg8-Jun-07 4:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.