优化SQL Server停车记录查询存储过程性能

问题背景 #

维护一张存储百万级停车记录的CarsParking表，执行自定义存储过程Report_Parking时，大数据量长时间段查询场景性能仍不理想。已为ID（主键）、查询涉及的CarID、ParkingTime字段添加索引，优化后查询10万条记录耗时从5分钟降至1分钟，但仍需进一步优化。

原存储过程代码 #

CREATE PROCEDURE [dbo].[Report_Parking]
@PageNumber        INT = 1, 
@PageSize          INT = 50, 
@StartTime         int = 0 , -- Unix时间戳
@EndTime           int = 0, 
@ColumnSort       NVARCHAR(50), 
@OrderSort         NVARCHAR(50),
@CarIDs          NVARCHAR(max)-- 车辆ID数组，逗号分隔

AS 
SET nocount ON 
SET fmtonly OFF 
BEGIN 
    
    CREATE TABLE #parkingTmp 
    (
        ID            int IDENTITY(1,1) NOT NULL,
        rownum            bigInt,
        CarID             NVARCHAR(23) NOT NULL,
        Start_Time        DateTime, 
        End_Time          DateTime,
        Parking_Time      NVARCHAR(50),
        Address       NVARCHAR(max),
        Long          NVARCHAR(20),
        Lat       NVARCHAR(20),
        Plate_Number      NVARCHAR(max),
        PageCounts    bigInt
    )
    DECLARE @query NVARCHAR(MAX)='';

    SET @query+= N'insert into #parkingTmp 
                SELECT   * FROM [dbo].CarsParking 
                WHERE   ((EndTimeParking BETWEEN '+cast(@StartTime as varchar)+' AND '+cast(@EndTime as varchar)+') and [CarID] in('''+@CarIDs +''')'
    exec(@query)

    -- 为未找到记录的车辆插入默认记录
    Declare @Id nvarchar(23)
    Declare @Ids Table (id nvarchar(23) primary Key not null)
    Insert @Ids(id) select * from [dbo].[SplitStrings_ToList](@CarIDs,',') 
    delete from @ids  
    WHERE Id IN (SELECT DISTINCT CarID FROM #parkingTmp)
    
    While exists (Select * From @Ids)
    Begin
        Select @Id = MAX(id) from @Ids
        
        INSERT INTO #parkingTmp 
        SELECT  top 1 *
        FROM CarsParking T
        WHERE CarID = @Id
        order by Start_Time DESC 
        
        Delete from @Ids Where id = @Id
    End
    --- 插入结束

    -- 分页查询最终结果
    DECLARE @FirstRec INT= (@PageNumber -1) * @PageSize; 
    DECLARE @LastRec INT= @PageNumber * @PageSize +1;
    
    declare @sql nvarchar(Max)=''
    SET @sql = N'WITH ctepaging AS (SELECT ROW_NUMBER() OVER(ORDER BY Plate_Number,'+@ColumnSort+' '+@OrderSort+') AS rownumber,* FROM #parkingTmp)'
    SET @sql += ' SELECT * FROM ctepaging '
    SET @sql += ' WHERE  rownumber > '+ CONVERT(NVARCHAR(12), @FirstRec) + ' AND rownumber < ' + CONVERT(NVARCHAR(12), @LastRec)
        
    exec sp_executesql @sql 
END

具体优化建议 #

1. 替换动态SQL拼接，使用参数化查询+表值参数 #

原代码直接拼接参数存在SQL注入风险，且无法复用执行计划。改用表值参数传递车辆ID列表，同时对时间参数做参数化处理：

• 先创建表值参数类型：

CREATE TYPE dbo.CarIDList AS TABLE (CarID NVARCHAR(23) PRIMARY KEY)

• 修改存储过程参数并重构动态SQL：

ALTER PROCEDURE [dbo].[Report_Parking]
@PageNumber        INT = 1, 
@PageSize          INT = 50, 
@StartTime         int = 0,
@EndTime           int = 0, 
@ColumnSort       NVARCHAR(50), 
@OrderSort         NVARCHAR(50),
@CarIDs          dbo.CarIDList READONLY -- 改用表值参数
AS
SET NOCOUNT ON
BEGIN
    DECLARE @query NVARCHAR(MAX)=''
    SET @query += N'INSERT INTO #parkingTmp (CarID, Start_Time, End_Time, Parking_Time, Address, Long, Lat, Plate_Number)
                    SELECT CarID, Start_Time, End_Time, Parking_Time, Address, Long, Lat, Plate_Number
                    FROM [dbo].CarsParking 
                    WHERE EndTimeParking BETWEEN @StartTime AND @EndTime 
                      AND CarID IN (SELECT CarID FROM @CarIDs)'

    EXEC sp_executesql @query, 
        N'@StartTime INT, @EndTime INT, @CarIDs dbo.CarIDList READONLY',
        @StartTime = @StartTime, @EndTime = @EndTime, @CarIDs = @CarIDs

2. 优化临时表设计，减少不必要开销 #

• 避免SELECT *：明确指定需要的字段，减少IO和内存占用；
• 优化字段类型：Long、Lat改用FLOAT类型替代NVARCHAR，节省空间且查询更快；NVARCHAR(max)字段若长度可控，改为固定长度或较小可变长度（如Address改为NVARCHAR(1000)）；
• 添加临时表索引：若临时表数据量较大，插入数据后针对排序字段创建索引：

CREATE NONCLUSTERED INDEX IX_Tmp_Parking_Sort ON #parkingTmp (Plate_Number, '+@ColumnSort+')

3. 批量处理无记录车辆，替换循环逻辑 #

原WHILE循环逐行插入效率极低，改用批量查询一次性处理：

-- 替换原有循环逻辑
INSERT INTO #parkingTmp (CarID, Start_Time, End_Time, Parking_Time, Address, Long, Lat, Plate_Number)
SELECT 
    t.CarID, 
    c.Start_Time, 
    c.End_Time, 
    c.Parking_Time, 
    c.Address, 
    c.Long, 
    c.Lat, 
    c.Plate_Number
FROM (
    SELECT CarID FROM @CarIDs
    EXCEPT
    SELECT DISTINCT CarID FROM #parkingTmp
) t
OUTER APPLY (
    SELECT TOP 1 * 
    FROM CarsParking c 
    WHERE c.CarID = t.CarID 
    ORDER BY c.Start_Time DESC
) c

4. 提前分页，减少临时表数据量 #

原逻辑先全量加载数据到临时表再分页，大数据量下开销极大。改为直接在原查询中完成分页，仅加载当前页数据：

DECLARE @sql NVARCHAR(MAX) = N'
WITH filtered_data AS (
    SELECT 
        CarID, 
        Start_Time, 
        End_Time, 
        Parking_Time, 
        Address, 
        Long, 
        Lat, 
        Plate_Number
    FROM CarsParking
    WHERE EndTimeParking BETWEEN @StartTime AND @EndTime 
      AND CarID IN (SELECT CarID FROM @CarIDs)
    UNION ALL
    SELECT 
        t.CarID, 
        c.Start_Time, 
        c.End_Time, 
        c.Parking_Time, 
        c.Address, 
        c.Long, 
        c.Lat, 
        c.Plate_Number
    FROM (
        SELECT CarID FROM @CarIDs
        EXCEPT
        SELECT DISTINCT CarID FROM CarsParking
        WHERE EndTimeParking BETWEEN @StartTime AND @EndTime
    ) t
    OUTER APPLY (
        SELECT TOP 1 * 
        FROM CarsParking c 
        WHERE c.CarID = t.CarID 
        ORDER BY c.Start_Time DESC
    ) c
),
ctepaging AS (
    SELECT 
        ROW_NUMBER() OVER(ORDER BY Plate_Number, '+@ColumnSort+' '+@OrderSort+') AS rownumber,
        *
    FROM filtered_data
)
SELECT * FROM ctepaging 
WHERE rownumber > @FirstRec AND rownumber < @LastRec'

EXEC sp_executesql @sql,
    N'@StartTime INT, @EndTime INT, @CarIDs dbo.CarIDList READONLY, @FirstRec INT, @LastRec INT',
    @StartTime = @StartTime, @EndTime = @EndTime, @CarIDs = @CarIDs,
    @FirstRec = @FirstRec, @LastRec = @LastRec

5. 优化索引策略，创建复合覆盖索引 #

现有单字段索引无法高效支持查询+排序场景，创建复合覆盖索引：

• 主查询覆盖索引：

CREATE NONCLUSTERED INDEX IX_CarsParking_CarID_EndTime 
ON CarsParking (CarID, EndTimeParking)
INCLUDE (Start_Time, End_Time, Parking_Time, Address, Long, Lat, Plate_Number)

• 无记录车辆查询覆盖索引：

CREATE NONCLUSTERED INDEX IX_CarsParking_CarID_StartTime 
ON CarsParking (CarID, Start_Time DESC)
INCLUDE (End_Time, Parking_Time, Address, Long, Lat, Plate_Number)

6. 数据类型转换优化 #

原表EndTimeParking为Unix时间戳（INT），若业务允许建议改为DATETIME类型，避免隐式转换；若必须保留INT类型，提前转换为DATETIME再查询：

DECLARE @StartDateTime DATETIME = DATEADD(SECOND, @StartTime, '1970-01-01')
DECLARE @EndDateTime DATETIME = DATEADD(SECOND, @EndTime, '1970-01-01')

内容的提问来源于stack exchange，提问作者Mahmoud Al-Ali