February 23, 2013 at 9:07 am
Hi,
I have used Row_Number() to implement the paging in my stored procedure. Paging is working fine. But problem is, after implementing the Row_Number(), indexes does not work & a Clustered index SCAN happens even if I use the primary key column in order by section.
below is the sample query:
SELECT TOP (@insPageSize) A.RowNum, A.AdID, A.AdTitle, A.AdFor, A.AdCondition,
A.AdExpPrice, A.CreatedDate, A.ModifiedDate, A.AdUID
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY vaa.AdID DESC) AS RowNum,
vaa.AdID, vaa.AdTitle, vaa.CityID, vaa.AdFor, vaa.AdCondition,
vaa.AdExpPrice, vaa.CreatedDate, vaa.ModifiedDate, vaa.AdUID
FROM Catalogue.vwAvailableActiveAds vaa
WHERE vaa.CategoryID = @intCategoryID AND vaa.CountryCode = @chrCountryCode
AND vaa.CreatedDate > DATEADD(dd, -90, GETUTCDATE())
AND vaa.StateID = @inbStateID AND vaa.CityID = @inbCityID
) A
WHERE A.RowNum > (@insPageSize * (@insPageNo - 1))
if I try to execute only inner query:
SELECT ROW_NUMBER() OVER (ORDER BY vaa.AdID DESC) AS RowNum,
vaa.AdID, vaa.AdTitle, vaa.CityID, vaa.AdFor, vaa.AdCondition,
vaa.AdExpPrice, vaa.CreatedDate, vaa.ModifiedDate, vaa.AdUID
FROM Catalogue.vwAvailableActiveAds vaa
WHERE vaa.CategoryID = @intCategoryID AND vaa.CountryCode = @chrCountryCode
AND vaa.CreatedDate > DATEADD(dd, -90, GETUTCDATE())
AND vaa.StateID = @inbStateID AND vaa.CityID = @inbCityID
It does not use any index. AdID is primary key & there is another non clustered index which covers all where clause. But index scan occurs.
If I remove the Row_Number() from inner query & check its execution plan, now index works but again StateID & CityID display as "predicate" while they are in non clustered index.
I have read that ">" comes under SARGable, then why StateID & City is not coming in seek predicate list.
Please give me some guidance to solve my both problems.
February 23, 2013 at 10:28 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2013 at 9:14 pm
Gail,
I have attached the required details.
& please ignore the data rows count because I have generated in local server not in production server.
February 23, 2013 at 11:09 pm
I found a bit better situation.
DECLARE @intPageNumber INT = 1, @intPageSize INT = 20;
WITH SQLPaging
AS
(
SELECT TOP(@intPageNumber * @intPageSize) ROW_NUMBER() OVER (ORDER BY vaa.CreatedDate) AS RowNum,
vaa.AdID, vaa.AdTitle, vaa.CityID, vaa.AdFor, vaa.AdCondition,
vaa.AdExpPrice, vaa.CreatedDate, vaa.ModifiedDate, vaa.AdUID
FROM Catalogue.vwAvailableActiveAds vaa
WHERE vaa.CategoryID = 1 AND vaa.CountryCode = 'GB' AND vaa.CreatedDate > DATEADD(dd, -90, GETUTCDATE())
AND vaa.StateID = 1737 AND vaa.CityID = 86
)
SELECT * FROM SQLPaging
WHERE RowNum > (@intPageSize * (@intPageNumber - 1))
If I use CreatedDate in place of AdID in Row_Number order by clause, then it uses the index seek till Created date. After that it looks for the predicate StateID & CityID.
I can not move the createddate before stateid & cityid becaue stateid & cityid are optional parameters in dynamic query & categoryid, country & created date are fixed conditions.
February 23, 2013 at 11:25 pm
another version is:
DECLARE @intPageNumber INT = 1, @intPageSize INT = 20;
SELECT A.AdID, A.AdTitle, A.AdFor, A.AdCondition, A.AdExpPrice, A.CreatedDate, A.ModifiedDate, A.AdUID
FROM
(
SELECT TOP(@intPageNumber * @intPageSize) ROW_NUMBER() OVER (ORDER BY vaa.CreatedDate) AS RowNum,
vaa.AdID, vaa.AdTitle, vaa.CityID, vaa.AdFor, vaa.AdCondition,
vaa.AdExpPrice, vaa.CreatedDate, vaa.ModifiedDate, vaa.AdUID
FROM Catalogue.vwAvailableActiveAds vaa
WHERE vaa.CategoryID = 1 AND vaa.CountryCode = 'GB' AND vaa.CreatedDate > DATEADD(dd, -90, GETUTCDATE())
AND vaa.StateID = 1737 AND vaa.CityID = 86
) A
WHERE A.RowNum > (@intPageSize * (@intPageNumber - 1));
Which one is efficient?
February 24, 2013 at 3:38 am
Anuj Rathi (2/23/2013)
Which one is efficient?
Test them both and see?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2013 at 4:09 am
Both are using same execution plan.
but I am still concerned about "CreatedDate" filter.
because all the filters which comes after date, are not coming under seek predicate.
February 24, 2013 at 7:33 am
Well the row number can't be a seek predicate, because there's no index on the row_number.
As for the other columns, incorrect index order?
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
Also there's a chapter in Pro SQL 2012 Practices about predicates and seek predicates
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2013 at 9:25 am
thanks Gail !
This link is really very helpful.
But I am not able to understand that if I use AdID (which is primary key & Clustered Index also) in Row_Number order by clause then why this is a SCAN rather than SEEK & even if I have used non clustered index too.
I have googled several topics but I haven't found why It is a SCAN ?
February 24, 2013 at 10:07 am
You're not filtering by AdID. If you were, that would be SARGable. You're filtering by a function on AdID, and that, like all other functions, is not SARGable, hence no index seek. As to why the cluster, probably because the query is not covered by any other indexes and hence the clustered index is the more efficient one to use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2013 at 10:15 am
thanks for this valuable advice.
February 24, 2013 at 9:19 pm
If I use any other column which is in NCI (& not PK), then this is a seek.
NCI is same in both situations. Difference is only is Row_Number order by column. Please check the attached two execution plans. One is Row_Number with PK & second one is Row_Number with any other column.
one shows SCAN & another one shows SEEK. Difference is only in Row_Number order by column.
February 25, 2013 at 1:56 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Pictures of the plan are pretty useless.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply