<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
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Paul White
»
Optimising Server-Side Paging - Part I
56 posts, Page 2 of 6
««
1
2
3
4
5
»
»»
Optimising Server-Side Paging - Part I
Rate Topic
Display Mode
Topic Options
Author
Message
nabidavid
nabidavid
Posted Monday, April 26, 2010 6:52 AM
SSC-Addicted
Group: General Forum Members
Last Login: Tuesday, June 19, 2012 9:31 AM
Points: 421,
Visits: 78
Great article, Paul. I look forward to Part II.
If you are not already covering it in a future article, I am curious if you have any advice on the best way (as in best performance) to incorporate the total record count for calculating total pages in this method, especially when custom filtering is involved.
Of course, if you are already planning to cover this topic, I am happy to patiently wait until then.
Thanks!
Post #910325
Paul White
Paul White
Posted Monday, April 26, 2010 7:35 AM
SSChampion
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
nabidavid (4/26/2010)
If you are not already covering it in a future article, I am curious if you have any advice on the best way (as in best performance) to incorporate the total record count for calculating total pages in this method, especially when custom filtering is involved.
I do cover various methods for obtaining the total record count in part II, and part III is concerned with custom filtering and ordering. Of course it is never possible to cover every possible need - but I hope you will find it useful.
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #910371
Paul White
Paul White
Posted Monday, April 26, 2010 7:37 AM
SSChampion
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
peter-757102 (4/26/2010)
I scanned the article quicly and it is well presented.
Thanks very much.
But I do have to dive into this particular one as I feel something odd is happening. The optimized statement is still just one statement and the steps in the with clause are no more then inline views. I am interested in why this construct manages to forces a way better execution plan.
You are right to suspect that there is rather more to it than that
I hope you find time for a proper read of it very soon. Please also take a look at the attached scripts if you can.
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #910374
sknox
sknox
Posted Monday, April 26, 2010 7:40 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:22 AM
Points: 1,037,
Visits: 1,354
Good article. One thing I noticed is that you're using a contiguous identity for your tests, but relying on ROW_NUMBER() for the paging.
I'm assuming that ROW_NUMBER() is used because you're planning for missing identity values due to deleted rows? Given that, I'd like to see the tests run with some random rows deleted, to better reflect a real-world scenario.
Post #910379
Paul White
Paul White
Posted Monday, April 26, 2010 7:58 AM
SSChampion
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
sknox (4/26/2010)
Good article. One thing I noticed is that you're using a contiguous identity for your tests, but relying on ROW_NUMBER() for the paging. I'm assuming that ROW_NUMBER() is used because you're planning for missing identity values due to deleted rows?
The cluster on the identity column is primarily for architectural reasons - as you know, you can never depend on IDENTITY columns to be contiguous, regardless of whether rows are ever deleted.
The ROW_NUMBER is just used to assign a sequential number in selected key order, and to make the selection of keys for the page we want easy.
The only reason I order by the underlying key (post_id) rather than the ROW_NUMBER (rn) is purely a limitation of the optimiser. We both know that the order of ROW_NUMBER assignment matches the ORDER BY of the ranking function, but the current optimiser does not include that logic. Sorting on the key avoids a small extra sort - so it's all rather technical, but then so are many things with SQL Server
Given that, I'd like to see the tests run with some random rows deleted, to better reflect a real-world scenario.
The full test rig is available for download in the Resources section.
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #910388
WayneS
WayneS
Posted Monday, April 26, 2010 8:02 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:29 PM
Points: 6,367,
Visits: 8,226
Paul - very nice article. Looking forward to the next ones...
Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then
DON'T USE IT
on a production system! After all,
you
will be the one supporting it!
Links:
For better assistance in answering your questions
,
How to ask a question
,
Performance Problems
,
Common date/time routines
,
CROSS-TABS and PIVOT tables Part 1
&
Part 2
,
Using APPLY Part 1
&
Part 2
,
Splitting Delimited Strings
Post #910391
pbarbin
pbarbin
Posted Monday, April 26, 2010 8:31 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, December 13, 2012 7:23 AM
Points: 22,
Visits: 349
Paul, great post and even better timing. We had an issue with paging on a larger table just last week and I've spent a fair amount of time trying to just understand the problem. I've read the article once and I'm looking forward to working with the scripts to learn more.
Our paging strategy is being implemented by our development team using Entity Framework to create the sql. That means the DBAs don't have much control over the generated Sql. Our fallback is to force developers to use stored procs.
I've got other questions, but I'll wait until researching further before posting them. In the meantime, I'm interested to know if you or anyone else has comments on Entity Framework in general or in this specific case.
Thanks.
Paul
Post #910424
Paul White
Paul White
Posted Monday, April 26, 2010 8:59 AM
SSChampion
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
pbarbin (4/26/2010)
Paul, great post and even better timing. We had an issue with paging on a larger table just last week and I've spent a fair amount of time trying to just understand the problem. I've read the article once and I'm looking forward to working with the scripts to learn more.
Cool. Thanks.
I've got other questions, but I'll wait until researching further before posting them. In the meantime, I'm interested to know if you or anyone else has comments on Entity Framework in general or in this specific case.
I'm pretty sure some of the regulars on the Forums have some strong views on this - can I ask you to post any specific questions there - you'll get a better response too. Thank you.
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #910453
Trey Staker
Trey Staker
Posted Monday, April 26, 2010 9:10 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:52 PM
Points: 1,379,
Visits: 2,626
Nice article. I'm looking forward to the next 2 installments. I like how indepth you go with the query plans. Thanks.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Post #910462
Paul White
Paul White
Posted Monday, April 26, 2010 9:14 AM
SSChampion
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
Trey Staker (4/26/2010)
Nice article. I'm looking forward to the next 2 installments. I like how indepth you go with the query plans. Thanks.
Thanks Trey - I do try to keep the articles focussed, but I *do* love query plans!
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #910464
« Prev Topic
|
Next Topic »
56 posts, Page 2 of 6
««
1
2
3
4
5
»
»»
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.
