<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
index is not working while using ROW_NUMBER
13 posts, Page 1 of 2
1
2
»»
index is not working while using ROW_NUMBER
Rate Topic
Display Mode
Topic Options
Author
Message
Anuj Rathi
Anuj Rathi
Posted Saturday, February 23, 2013 9:07 AM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, March 22, 2013 3:01 AM
Points: 93,
Visits: 163
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
GilaMonster
GilaMonster
Posted Saturday, February 23, 2013 10:28 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 37,660,
Visits: 29,913
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
Anuj Rathi
Anuj Rathi
Posted Saturday, February 23, 2013 9:14 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, March 22, 2013 3:01 AM
Points: 93,
Visits: 163
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
Anuj Rathi
Anuj Rathi
Posted Saturday, February 23, 2013 11:09 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, March 22, 2013 3:01 AM
Points: 93,
Visits: 163
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
Anuj Rathi
Anuj Rathi
Posted Saturday, February 23, 2013 11:25 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, March 22, 2013 3:01 AM
Points: 93,
Visits: 163
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
GilaMonster
GilaMonster
Posted Sunday, February 24, 2013 3:38 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 37,660,
Visits: 29,913
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
Anuj Rathi
Anuj Rathi
Posted Sunday, February 24, 2013 4:09 AM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, March 22, 2013 3:01 AM
Points: 93,
Visits: 163
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
GilaMonster
GilaMonster
Posted Sunday, February 24, 2013 7:33 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 37,660,
Visits: 29,913
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
Anuj Rathi
Anuj Rathi
Posted Sunday, February 24, 2013 9:25 AM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, March 22, 2013 3:01 AM
Points: 93,
Visits: 163
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
GilaMonster
GilaMonster
Posted Sunday, February 24, 2013 10:07 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 37,660,
Visits: 29,913
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 »
13 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
