Sql-server – One table to save all records vs create one table each day

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:

• Session-State Modes
• Session Management
• Potential enhancements to ASPState (some tips from @Aaron B. to improve performance if you are using ASP.NET with the SQLServer Session-State mode)

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.

You can try this:

SELECT m.vlanID, 
        v.vlan_name, 
        m.interfaceName, 
        count(1) as mac,
         n.nd_name, 
         p.interfaceDescription, 
         p.interfacePortType
    FROM  macs m 
    INNER JOIN network_devices n 
        ON n.nd_ip_address = m.ipAddr
    INNER JOIN vlans v  
        ON m.vlanID = v.vlan_id
    INNER JOIN ports p
        ON p.vlanID = m.vlanID
    GROUP BY m.vlanID, v.vlan_name, m.interfaceName, n.nd_name, p.interfaceDescription, p.interfacePortType

A few things about the changes:

1. The cartesian join to inner join switch is purely cosmetic, unless the mysql implementation you're using doesn't support the cartesian inner join syntax, which would be very odd.

2. Removing the Order By clause should provide a performance bump, but depending on the way you're using the results may not be useful.

3. If m.macAddress is sometimes null, count(1) != count(m.macAddress)

These small optimizations stated, there is not a whole lot to optimize about this query itself. There is something about the context of the query that is causing the exceptional runtime.

Indexes on n.nd_ip_address, m.ipAddr,m.vlanID, v.vlan_id, and p.vlanID would certainly help substantially. If you have to run the query multiple times, it would be well worth the time invested in creating the indexes even on temporary data.

It's even likely that re-creating the indexes every time the query runs would cause the overall runtime to be much lower.