Sql-server – Manage multiple short-term tables with same schema or use single table for short-term data

sql serversql-server-2005

My team has proposed to store session state data for each open client session in a table in the database. The application server creates a new table for each client session as the user logs in via web browser. The data that needs to be kept for the session is created along with the table, and is not modified (except in rare circumstances). When the user logs out, the table is dropped. Orphan tables are purged regularly.

The application server manages these short-term tables. There won't be any database code that manages the tables or the data.

The data in these tables has relational links to other tables in the database. The tables will be joined in queries to other tables in the database.

I'd like to find out the pro's and con's of this design, in contrast to others. For example, all of the same functionality could be implemented in a single table that stored state data for all sessions. If done this way, there would be one clustered index on the session ID. Each access to the state data would read all of the data for exactly one session ID.

What are the scalability concerns for creating/dropping multiple tables rapidly? Assume this occurs on the order of 1K-10K time per hour.

Best Answer

I don't see much benefit of storing Web App Sessions per table:

  • Creating and dropping tables is a little more expensive than inserting and deleting rows

  • Depending on how many sessions can exist (current + inactive that have not been cleaned up yet), 10,000 tables is a bit more to deal with than 100,000 rows in a table

  • Temp tables are not an option. http is a connectionless / stateless protocol, hence the need for sessions ;-). Since temp tables -- local and global -- are automatically dropped when the session in which they were created ends, they wouldn't persist between calls.

  • Any app-based mechanism that can clean up obsolete tables can just as easily delete obsolete rows (i.e. an OnSessionEnd event and/or job that runs periodically).

IF, you are using ASP.NET, there is a somewhat built-in option to store Session State in SQL Server:

That might work with JAVA / JSP, but probably not?

If not using ASP.NET, then you can:

  • use a single table to store the serialized Session object. (this is one of the options mentioned in the question).

  • OR, possibly use a cache server, such as memcached, redis, or even Windows Server AppFabric Caching. They might be typically intended for longer-term caching, but I see some benefits to them, such as:

    • they are based in memory, not on disk (i.e. faster, which is why they exist in the first place)
    • they have built-in mechanisms for key expiration so you probably won't need app-based clean-up logic. Something to at least consider / look into.
Related Question