Other methods as noted in my other answer are not using the view, using a tvf instead, not using a window function to do the filtering, stored procedures, ....
My initial thoughts are that the view itself is the major issue, in that it is ranking over the entire data set. Also that there is no index that includes the DateCreated column, but I'm not sure how useful that is for sorting.
EDIT Your stored procedure
CREATE PROCEDURE [dbo].[_SyncData_Recent]
(
@CustomerId int = null
)
AS
BEGIN
WITH Ranked AS (
SELECT
Id,
CustomerId,
CategoryId,
ROW_NUMBER() OVER (PARTITION BY CustomerId, CategoryId ORDER BY DateCreated DESC) AS rn
FROM SyncData AS cb
WHERE CustomerId = @CustomerId
AND DELETED_FLAG = 0
)
SELECT Id, CustomerId, CategoryId FROM Ranked WHERE rn = 1 AND CustomerId = @CustomerId
END
GO
EXEC [dbo].[_SyncData_Recent] 600528;
Would also work to apply earlier filtering, but I would add this index:
CREATE INDEX IX_CustomerID_CategoryId_DateCreated_
ON dbo.SyncData([CustomerId],CategoryId,DateCreated DESC)
INCLUDE(DELETED_FLAG,ID)
WHERE DELETED_FLAG = 0;
to remove the sort operator.
And the last filter, WHERE rn = 1 AND CustomerId = @CustomerId could just be WHERE rn = 1.
My initial thoughts are that the view itself is the major issue, in that it is ranking over the entire data set. Also that there is no index that includes the DateCreated column, but I'm not sure how useful that is for sorting.
My initial thoughts are that the view itself is the major issue, in that it is ranking over the entire data set.
EDIT Your stored procedure
CREATE PROCEDURE [dbo].[_SyncData_Recent]
(
@CustomerId int = null
)
AS
BEGIN
WITH Ranked AS (
SELECT
Id,
CustomerId,
CategoryId,
ROW_NUMBER() OVER (PARTITION BY CustomerId, CategoryId ORDER BY DateCreated DESC) AS rn
FROM SyncData AS cb
WHERE CustomerId = @CustomerId
AND DELETED_FLAG = 0
)
SELECT Id, CustomerId, CategoryId FROM Ranked WHERE rn = 1 AND CustomerId = @CustomerId
END
GO
EXEC [dbo].[_SyncData_Recent] 600528;
Would also work to apply earlier filtering, but I would add this index:
CREATE INDEX IX_CustomerID_CategoryId_DateCreated_
ON dbo.SyncData([CustomerId],CategoryId,DateCreated DESC)
INCLUDE(DELETED_FLAG,ID)
WHERE DELETED_FLAG = 0;
to remove the sort operator.
And the last filter, WHERE rn = 1 AND CustomerId = @CustomerId could just be WHERE rn = 1.
My initial thoughts are that the view itself is the major issue, in that it is ranking over the entire data set. Also that there is no index that includes the DateCreated column, but I'm not sure how useful that is for sorting.
That is exactly it, the window function is applied to the whole dataset to then be filtered by CustomerId and rn=1. See my previous answer here for more information.
With the CustomerId filter predicate applied only when the data gets to the filter operator:
You could add OPTION(RECOMPILE) to the select query with the filter to see the CustomerId at runtime and filter before passing the data through the window function
SELECT * FROM vw_SyncDataRecent
WHERE CustomerId = 600528
OPTION(RECOMPILE);
Other methods as noted in my other answer are not using the view, using a tvf instead, not using a window function to do the filtering, ....
TVF example
CREATE FUNCTION dbo.[Fnc_SyncDataRecent]
(
@P1 INT
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
WITH Ranked AS (
SELECT
Id,
CustomerId,
CategoryId,
ROW_NUMBER() OVER (
PARTITION BY Id, CustomerId
ORDER BY DateCreated DESC) AS rn
FROM dbo.SyncData AS cb
)
SELECT Id, CustomerId, CategoryId
FROM Ranked
WHERE rn = 1 and CustomerId = @P1
SELECT * FROM dbo.[Fnc_SyncDataRecent](600528);
I would also add this index:
CREATE INDEX IX_CustomerID3
ON dbo.SyncData([CustomerId])
INCLUDE(CategoryId,ID,DateCreated);