SQL Server中跨400张表统计31万用户各自记录数量的实现方案咨询

Alright, let's tackle this problem head-on—you're dealing with a massive scale (310k users across 400 tables) so efficiency is non-negotiable here. Let's break down the best approaches to get those counts without grinding your server to a halt.

1. Use Dynamic SQL with Set-Based Operations (Top Recommendation) #

The worst thing you could do is loop through every user and query every table individually (that's 310,000 * 400 = 124,000,000 queries—yikes!). Instead, we'll generate a single dynamic SQL script that aggregates counts for all target users across each table in one go.

Example Code #

First, assume you have a table (YourUserListTable) storing all 310k user IDs, and all 400 tables have a UserId column to filter on:

DECLARE @DynamicSQL NVARCHAR(MAX) = N'';

-- Build a UNION ALL query for each target table
SELECT @DynamicSQL += N'
SELECT 
    UserId,
    ''' + QUOTENAME(t.NAME) + ''' AS TableName,
    COUNT(*) AS RecordCount
FROM ' + QUOTENAME(t.NAME) + '
WHERE UserId IN (SELECT UserId FROM YourUserListTable)
GROUP BY UserId
UNION ALL'
FROM sys.tables t
WHERE t.NAME IN ('Table_001', 'Table_002', ...) -- Replace with your 400 table names, or pull from a table of table names

-- Remove the trailing UNION ALL
SET @DynamicSQL = LEFT(@DynamicSQL, LEN(@DynamicSQL) - 10);

-- Execute the query and store results (save to a permanent table for reuse!)
EXEC sp_executesql @DynamicSQL;

Why This Works #

• Set-based efficiency: We scan each table exactly once, filtering only the users you care about, and aggregate counts in bulk. This cuts your total queries from 124 million to 400.
• Scalable: If your table list changes often, you can replace the WHERE t.NAME IN (...) clause with a join to a table that stores your target table names (e.g., TableList with a TableName column).

2. If You Must Iterate Per User (Not Recommended) #

If for some reason you need to process one user at a time (e.g., real-time per-user reporting), use this approach sparingly—it’s far less efficient, but manageable with optimizations:

-- Create a temp table to store results
CREATE TABLE #UserTableCounts (
    UserId INT,
    TableName NVARCHAR(128),
    RecordCount INT
);

DECLARE @UserId INT;
-- Use FAST_FORWARD cursor for minimal overhead
DECLARE UserCursor CURSOR FAST_FORWARD FOR
SELECT UserId FROM YourUserListTable;

OPEN UserCursor;
FETCH NEXT FROM UserCursor INTO @UserId;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @UserSpecificSQL NVARCHAR(MAX) = N'';
    
    -- Build count queries for the current user across all tables
    SELECT @UserSpecificSQL += N'
    INSERT INTO #UserTableCounts (UserId, TableName, RecordCount)
    SELECT ' + CAST(@UserId AS NVARCHAR(10)) + ', ''' + QUOTENAME(t.NAME) + ''', COUNT(*)
    FROM ' + QUOTENAME(t.NAME) + '
    WHERE UserId = ' + CAST(@UserId AS NVARCHAR(10)) + ';'
    FROM sys.tables t
    WHERE t.NAME IN ('Table_001', 'Table_002', ...);

    EXEC sp_executesql @UserSpecificSQL;
    FETCH NEXT FROM UserCursor INTO @UserId;
END

CLOSE UserCursor;
DEALLOCATE UserCursor;

-- Retrieve your results
SELECT * FROM #UserTableCounts;
DROP TABLE #UserTableCounts;

Critical Note #

This will tax your server heavily—only use it if you have no other option. Even with a fast cursor, 310k iterations will take significant time and CPU.

3. Non-Negotiable Optimizations #

No matter which approach you choose, these tweaks will make a huge difference:

• Index Every UserId Column: Add a non-clustered index on UserId for each of the 400 tables. This turns full table scans into fast index scans, drastically reducing count time.
• Persist Results: Save the final counts to a permanent table (e.g., UserTableRecordCounts) instead of recalculating every time. Refresh it periodically if your source tables update.
• Batch Large User Lists: If your dynamic SQL gets too long (NVARCHAR(MAX) has limits), split your user list into batches (e.g., 10,000 users at a time) and run the dynamic SQL for each batch.
• Enable Parallel Query: Make sure your SQL Server instance has parallel query enabled (it’s default, but double-check) to leverage multiple CPU cores for large aggregation queries.

内容的提问来源于stack exchange，提问作者Cole Wittrock