优化聚合查询：视图突发性能下降的优化方案咨询

Alright, let's tackle this slow view issue head-on. With 15k rows in @dispatchPallet and 135k in @pickLog, the biggest culprits here are likely missing indexes or SQL Server's limited ability to optimize queries using table variables. Here's how to fix it:

1. Add Proper Indexes to Your Table Variables #

Table variables don’t come with indexes by default (except for a primary key if you define one), which means SQL Server has to do full table scans every time it joins or filters them. For your scenario, add clustered primary keys and non-clustered indexes on columns used for joins or filters.

Example of indexed table variables:

DECLARE @dispatchPallet TABLE ( 
    [PICK_PALL_NUM] [bigint] NOT NULL PRIMARY KEY CLUSTERED, -- Clustered PK for fast lookups
    [PALLET_PLACEMENT] [nvarchar](4) NOT NULL, 
    [SHIPMENT_ID] [nvarchar](255) NULL 
)

DECLARE @pickLog TABLE ( 
    [LINE_NUM] [int] NOT NULL PRIMARY KEY CLUSTERED, 
    [QTY_PRE] [numeric](9, 2) NULL, 
    [QTY_SUF] [numeric](9, 2) NULL,
    -- Add non-clustered index on the join column (adjust to match your actual join field)
    [PICK_PALL_NUM] [bigint] NOT NULL,
    INDEX IX_pickLog_PICK_PALL_NUM NONCLUSTERED ([PICK_PALL_NUM])
)

This tells SQL Server exactly how to quickly find matching rows between the two tables, avoiding costly full scans.

2. Check the Execution Plan #

Before making more changes, run your view and look at the actual execution plan (in SSMS, hit Ctrl+M before executing). Look for:

• Full Table Scan operators on either table variable (a clear sign of missing indexes)
• Hash Match joins that take up most of the query cost (this often means the optimizer lacks good stats to choose a better join type)
• Sort operators with high cost (if you’re ordering results, add an index that includes the sort columns)

The execution plan will pinpoint the bottleneck—don’t guess at what’s slowing things down!

3. Switch to Temporary Tables (Instead of Table Variables) #

Table variables have extremely limited statistical information (SQL Server usually assumes they only have 1 row), which leads the optimizer to make poor choices for large datasets like your 135k-row @pickLog. Temporary tables have proper stats, so the optimizer can generate a far better execution plan.

Example with temporary tables:

-- Create temp tables with indexes
CREATE TABLE #dispatchPallet ( 
    [PICK_PALL_NUM] [bigint] NOT NULL PRIMARY KEY CLUSTERED, 
    [PALLET_PLACEMENT] [nvarchar](4) NOT NULL, 
    [SHIPMENT_ID] [nvarchar](255) NULL 
)
INSERT INTO #dispatchPallet SELECT ... -- Your existing data insertion logic

CREATE TABLE #pickLog ( 
    [LINE_NUM] [int] NOT NULL PRIMARY KEY CLUSTERED, 
    [QTY_PRE] [numeric](9, 2) NULL, 
    [QTY_SUF] [numeric](9, 2) NULL,
    [PICK_PALL_NUM] [bigint] NOT NULL,
    INDEX IX_pickLog_PICK_PALL_NUM NONCLUSTERED ([PICK_PALL_NUM])
)
INSERT INTO #pickLog SELECT ... -- Your existing data insertion logic

-- Use temp tables in your view logic
SELECT ... FROM #dispatchPallet dp JOIN #pickLog pl ON dp.PICK_PALL_NUM = pl.PICK_PALL_NUM

-- Cleanup (optional; SQL Server auto-drops temp tables when the session ends)
DROP TABLE #dispatchPallet
DROP TABLE #pickLog

For datasets over 100k rows, temp tables almost always outperform table variables.

4. Fix Implicit Conversions & Bad Join Conditions #

Double-check that columns used for joins have matching data types. For example, if @dispatchPallet.PICK_PALL_NUM is bigint but @pickLog.PICK_PALL_NUM is int, SQL Server will have to convert one type to match—this invalidates indexes and slows down the join.

Also, avoid using functions in join/where clauses (like UPPER(SHIPMENT_ID) = 'XYZ'). These prevent index usage—instead, transform the data when inserting into the table variables, or create a computed column index.

5. Filter Data Early #

If your view only needs a subset of the data, don’t load all 15k/135k rows into the table variables first. Add a WHERE clause to your INSERT statements to filter out unnecessary rows before processing:

INSERT INTO @pickLog SELECT LINE_NUM, QTY_PRE, QTY_SUF, PICK_PALL_NUM
FROM YourSourceTable
WHERE SHIPMENT_ID = @TargetShipmentID -- Filter early to reduce row count

Less data to process = faster queries.

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