Try to Create Tables Only Once

I have to apologize.  In a lot of the sample code I’ve developed (including the blog source code I showed at PDC), I’ve used a pattern for table creation that amounts to “try to create the table every time.”  Because the sample storage client library gives methods like TryCreateTable and CreateTablesFromModel, this is a tempting pattern.

However, it turns out it has a cost I didn’t understand.  On the server, calls to create a table that already exists result in a number of exceptions being thrown (on both the server side and on the client side before being swallowed).  This is bad for performance, so a much better pattern is to ensure that you only try to create your tables once.

Probably the best solution is to have separate initialization code that creates your tables.  This is analogous to the pattern of having CREATE TABLE commands scripted in T-SQL which you run once to set up the database.

Another solution is to have code that runs only once during your application’s lifecycle.  Unfortunately, Application_Start in global.asax.cs (the first place many of us think of) won’t work.  You can instead use the pattern referenced in the RoleManager documentation:

The Windows Azure fabric runs IIS 7.0 in integrated mode. In integrated mode, the Application_Start event does not support access to the request context or to the members of the RoleManager class provided by the Windows Azure SDK runtime API. If you are writing an ASP.NET application that accesses the request context or calls methods of the RoleManager class from the Application_Start event, you should modify it to initialize in the Application_BeginRequest event instead.

For an example that shows how to use the Application_BeginRequest event, see the PersonalWebSite Sample that ships with the Windows Azure SDK.

In a comment in the PersonalWebSite code, there’s a link to this blog post which explains the pattern.

Here’s the snippet of code from PersonalWebSite that implements this “run once” pattern via Application_BeginRequest:

    void Application_BeginRequest(object sender, EventArgs e)
    {
        HttpApplication app = (HttpApplication)sender;
        HttpContext context = app.Context;
        FirstRequestInitialization.Initialize(context);
    }

    class FirstRequestInitialization
    {
        private static bool s_InitializedAlready = false;
        private static Object s_lock = new Object();

        public static void Initialize(HttpContext context)
        {
            if (s_InitializedAlready)
            {
                return;
            }
            lock (s_lock)
            {
                if (s_InitializedAlready)
                {
                    return;
                }

                ApplicationStartUponFirstRequest(context);
                s_InitializedAlready = true;
            }
        }

        private static void ApplicationStartUponFirstRequest(HttpContext context)
        {
            // This is where you put initialization logic for the site.
            // RoleManager is properly initialized at this point.

            if (!Roles.RoleExists("Administrators")) Roles.CreateRole("Administrators");
            if (!Roles.RoleExists("Friends")) Roles.CreateRole("Friends");
            if (Membership.GetUser("admin") == null)
            {
                Membership.CreateUser("admin", "admin", "admin@mypersonalsite.com");
                Roles.AddUserToRole("admin", "Administrators");
            }

            AlbumTable.Initialize();
            PhotoTable.Initialize();
        }

I believe the locking is not strictly necessary, since it’s okay if you try to create the table a couple times due to concurrency.  (It’s just bad to do it very frequently, like on every request.)

Thanks to Jai on our storage team for pointing this out to me!

UPDATE: Mark Seemann created a little PowerShell script to create tables.