如何高效存储Asp.Net MVC 5.2视频直播平台的持续观看统计数据？

Hey there! Let's break down how you can handle viewing stats for your ASP.NET MVC 5.2 streaming platform—since you've got variable audience sizes (from dozens to thousands), we need a solution that's both scalable and accurate for tracking viewer counts and watch duration.

First, let's anchor on the two key metrics you need: real-time/historical viewer counts, and individual user watch duration.

1. Real-Time Viewer Count Tracking #

• For live online viewer numbers, SignalR is a perfect fit (it’s natively supported in ASP.NET MVC 5.2). Use it to establish bidirectional connections: when a user loads the stream page, trigger a connection and maintain an online user set on the server with ConcurrentDictionary to avoid thread-safety issues. When users leave or close the page, remove their entry from the set.
• To persist real-time data (e.g., checking peak viewers later), write the current viewer count to a database at regular intervals (like every 1 minute). Create a LiveViewStats table with fields: EventId, Timestamp, ViewerCount.

2. Individual User Watch Duration Tracking #

We need to handle both intentional exits and unexpected disconnections (network drops, browser crashes):

• Use SignalR's connection events to track session timings: record SessionStart when a user connects successfully. When they disconnect, calculate the duration (SessionEnd - SessionStart) and write it to a ViewerSession table. Include fields like UserId (if authenticated), EventId, SessionStart, SessionEnd, DurationSeconds.
• For anonymous users, generate a unique client ID (via cookie or local storage UUID) to link multiple visits from the same user, preventing duplicate or fragmented stats.
• For unexpected disconnections, SignalR's OnDisconnectedAsync event will fire. Set a 30-second timeout—if the user doesn’t reconnect within that window, finalize their session and log the duration.

3. Database Design Tips #

With potentially large datasets (thousands of users per event, hours of streaming), optimize your tables:

• Events table: Stores basic event details (EventId, Title, StartTime, EndTime, etc.)
• ViewerSession table: The core stats table—add indexes on EventId and UserId/ClientId to speed up queries for total watch time per event or average duration per user.
• LiveViewStats table: Index on EventId and Timestamp to generate smooth viewer count trend graphs later.

4. Performance Optimizations for High-Traffic Events #

• Avoid direct database writes on every connection/disconnection. Use an in-memory queue (like BlockingCollection) to batch records—insert 100+ session entries at once to reduce database IO pressure.
• If you’re running a load-balanced setup, use Redis to cache real-time viewer counts instead of in-memory collections. It’s more reliable and works across multiple servers.

内容的提问来源于stack exchange，提问作者Craig Poole