Reputation: 221
I created a stored procedure that select some data from one table
table name ( t1 ) columns ( 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
It takes about 20 seconds to run the following
spGetData 1000 ,0,0,'desc'
which is very slow. This procedure will be called later from an ASP.NET MVC project and the result will be displayed using jQuery datatables.
How can I improve the performance of it?
Upvotes: 0
Views: 1502
Reputation: 171
First off, lose CTE. This is way too simple query for 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
This should be faster, but one more remark. LIKE searching within middle of string ('%'+@Search+'%'
) can't use any indexing and will always be slow - especially on 2M rows and even worse - doing that on three different columns. It simply has to do a full table scan.
Adding additional conditions (which are not LIKE) would improve performance.
Upvotes: 1