Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

index is not working while using ROW_NUMBER Expand / Collapse
Author
Message
Posted Saturday, February 23, 2013 9:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, Visits: 176
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.
Post #1423352
Posted Saturday, February 23, 2013 10:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 42,476, Visits: 35,545
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1423361
Posted Saturday, February 23, 2013 9:14 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, Visits: 176
Gail,

I have attached the required details.
& please ignore the data rows count because I have generated in local server not in production server.
Post #1423392
Posted Saturday, February 23, 2013 11:09 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, Visits: 176
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.
Post #1423394
Posted Saturday, February 23, 2013 11:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, Visits: 176
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?
Post #1423395
Posted Sunday, February 24, 2013 3:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 42,476, Visits: 35,545
Anuj Rathi (2/23/2013)
Which one is efficient?


Test them both and see?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1423403
Posted Sunday, February 24, 2013 4:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, Visits: 176
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.
Post #1423405
Posted Sunday, February 24, 2013 7:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 42,476, Visits: 35,545
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1423416
Posted Sunday, February 24, 2013 9:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, Visits: 176
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 ?
Post #1423420
Posted Sunday, February 24, 2013 10:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 42,476, Visits: 35,545
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1423422
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse