SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 Paging – The Holy Grail


SQL Server 2005 Paging – The Holy Grail

Author
Message
fred_ch9400_cn
fred_ch9400_cn
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 35
werner.broser said
Nice method, but works only for unique fields!
-- I agree.

When the order-expression does not bear an unique value, "The Holy Grail" can make mistakes:

DECLARE @t1 TABLE ([x] INT, [y] INT)
INSERT INTO @t1 ([x], [y])
SELECT 1, 1
UNION ALL
SELECT 1, 2

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [x] ASC)
+ ROW_NUMBER() OVER (ORDER BY [x] DESC)
- 1 AS [Sum]
FROM @t1) t



The query result gives
x y Sum
1 1 1
1 2 3
which obviously cannot satisfy us.

For rows over which order-expression gives the same value, their relative order will not change regardless of "ASC" or "DESC" option, but only following the order how they are physiclely stored. The previous example proves this.

We should know this limit and do not apply "The Holy Grail" in the wrong place.
peter.krickner
peter.krickner
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 2
Thank you for this. I've used it now, but haven't studied reads and performance gains (or losses) yet. Realized though that the column(s) used for sorting has to be unique in order for the total row count to be accurate. If there are several equal records in the sorted column, the total row count will not be accurate. You have to make sure that the sorting is 100% opposite to the initial sorting. Just a simple addition to the solution after trying it out. Thank you again!

/ Peter.
peter.krickner
peter.krickner
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 2
Sorry for stating an already noticed "feature". Only read the earliest posts...
th-spam
th-spam
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 19
Really nice solution for the total row count and paging problem; and you can't miss the analogy to the classical Gauss argument for the sum of the N first integers 1+2+..+N= (N+1)*N / 2

1 + 2 + ... N
N + (N-1) ... 1

Smile
ningaraju.n
ningaraju.n
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1046 Visits: 425
Hi Friend,
Can you get me the other alternative not using trigger
peter-757102
peter-757102
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3567 Visits: 2559
Just to throw in my 2c, I try to stay away from techniques that do not work reasonably efficient or completely break down in other ways when the query to be paged spans multiple tables. It is far easier to design paging for a single table then it is for multiple.

Thats is why I like the idea of doing first a limited run (top X) initial query that fetches all the PKs in the desired order. Followed by queries that each window over the fetched keys to quickly and efficiently fetch the actual data to display. This allows for 'browsing' over the results in a detail view if the user clicks on the grid.

If browsing is not required you can ** maybe ** suffice with recording only the keys of page starts. And then build a (more complex) query that uses a page start key as a reference to get the next PageSize-1 results for each page requested. Assuming the data to query has a low mutation rate.

But whatever method you use in complex queries, having a narrow index that covers the fields being filtered and sorded on is a must. Because other then that, there is (i think) no way to get to the minimum possible I/O.
mbarkell
mbarkell
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 25
I quite enjoyed reading this article. I found myself going come-on come-on the solution the solution, but then was pleased when the solution was presented. I find the keeping of the count of records within each record with a constant big O great.
alen teplitsky
alen teplitsky
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 4763
can someone give me a real world example of when this would be used in production? i've never seen it used, but it could be because a lot of our code was written for SQL 2000 and just migrated over with minimal changes
Mike Dougherty
Mike Dougherty
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 952
I haven't implemented this yet, but my thoughts on paging is this:

1) Store each unique combination of request parameters in a table (parent)
2) Run the query, store the results in another table with a foreign key to the parent table. Since this result is now read-only and there will be no provision for updating the rows, update the parent row's resultrowscount for easy access to the total.
3) Also set cache timeout values on the parent. Suppose large result sets that took a long time to run are given large cache timeout while smaller results are given shorter timeouts. Perhaps the data itself determines its life: a query on real-time data has is stale in 3 minutes while first quarter report (run in may) has a life of 30 days or more.
4) Pages are retrieved from the prefetched (and denormalized) child table.
Further requests for the same query are served from the prefetched result if the results are within the staleness tolerance. In the case the data is stale, the old key is marked obsolete and the process starts over. (This allows a nice audit/analysis to be done on cache hits and results rather than deleting old results)
If business rules allow for Top N to prevent absurdly large results then go for it, otherwise the cache at least mitigates the need to redundantly query the results from the source system.

I think a better question is to ask if users really need pages of data more than they need better results in general. "Eyeballing" long lists is a huge waste of time. Thanks to Google (et al.) we expect relevant results of search to be on the first page. If we're not sure what we're looking for, we might desperately look at page 2 or 3. After that, we hopefully have an idea how to search more specifically for better results. This makes search an iterative process of refining criteria rather than a brute-force perusal of pages of results. I was going to make this concept the focus of my seminar paper this semester but the day the topic proposal was due I read the SQLServerCentral article on genetic algorithms in T-SQL so I did that instead.

I urge anyone thinking of implementing results paging to consider how/why the user can be better served with a more conversational and iterative process. Our job should be to fetch what they really want from our database (even when they aren't sure what they want). Simply giving the user multiple pages of what they asked for makes us look lazy and makes them work much harder than they should.
Tony++
Tony++
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3363 Visits: 2102
To pull an idea from my ...um, thin air...
When I'm looking at the executed plan each component knows how many rows it handled, and the components used in the initial selection is the total rows.
What's the merit for returning the plan XML to the caller & letting the caller look into the XML for the total rows? It at least lets the database just handle the results, and moves the 'Total rows" workload out to the caller.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search