SQL Server存储过程对200万条记录进行排序、分页和过滤

Question

我创建了一个从一个表中选择一些数据的存储过程

表名 ( T1 ( 列 ( ID ， a ， b (

CREATE PROCEDURE spGetData
@DisplayLength INT,
@DisplayStart INT,
@SortCol INT,
@SortDir NVARCHAR(10),
@Search  NVARCHAR(255) = NULL
AS
BEGIN
DECLARE @FirstRec INT, @LastRec INT
SET @FirstRec = @DisplayStart ;
SET @LastRec = @DisplayStart + @DisplayLength;
WITH CTE_Employees AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY
CASE WHEN (@SortCol = 0 AND @SortDir = 'asc') THEN id END asc,
CASE WHEN (@SortCol = 0 AND @SortDir = 'desc') THEN id END desc,
CASE WHEN (@SortCol = 1 AND @SortDir = 'asc') THEN a END asc,
CASE WHEN (@SortCol = 1 AND @SortDir = 'desc') THEN a END desc,
CASE WHEN (@SortCol = 2 AND @SortDir = 'asc') THEN b END asc,
CASE WHEN (@SortCol = 2 AND @SortDir = 'desc') THEN b END desc) AS RowNumber, 
COUNT(*) OVER () AS TotalCount, 
id, a, b
FROM
t1
WHERE
(@Search IS NULL 
OR id LIKE '%' + @Search + '%' 
OR a LIKE '%' + @Search + '%' 
OR b LIKE '%' + @Search + '%')
)
SELECT * 
FROM CTE_Employees
WHERE RowNumber > @FirstRec AND RowNumber <= @LastRec   
END

运行以下命令大约需要 20 秒

spGetData 1000 ,0,0,'desc'

这是非常慢的。稍后将从 ASP.NET MVC 项目调用此过程，结果将使用 jQuery 数据表显示。

如何提高它的性能？

Answer 1

首先，失去 CTE。对于 CTE 来说，这太简单了。

CREATE PROCEDURE spGetData
@DisplayLength int,
@DisplayStart int,
@SortCol int,
@SortDir nvarchar(10),
@Search nvarchar(255) = NULL
AS
BEGIN
SELECT 
COUNT(*) OVER () AS TotalCount,
id,
a,
b
FROM t1
WHERE 
(@Search IS NULL OR 
id LIKE '%'+@Search+'%' OR 
a LIKE '%'+@Search+'%' OR 
b LIKE '%'+@Search+'%')
ORDER BY
CASE 
WHEN @SortDir = 'ASC' THEN 
CASE @SortCol
WHEN 0 THEN id
WHEN 1 THEN a
WHEN 2 THEN b
END 
END desc,
CASE 
WHEN @SortDir = 'desc' THEN 
CASE @SortCol
WHEN 0 THEN id
WHEN 1 THEN a
WHEN 2 THEN b
END 
END DESC
OFFSET @DisplayStart ROWS
FETCH NEXT @DisplayLength ROWS ONLY
END 

这应该更快，但还有一句话。LIKE 在字符串中间搜索 ('%'+@Search+'%'( 不能使用任何索引，并且总是很慢 - 尤其是在 2M 行甚至更糟 - 在三个不同的列上执行此操作。它只需要执行全表扫描即可。 添加其他条件(不是 LIKE(将提高性能。