Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

(UPDATE: If you are using SQL Server 2005 or above, there is a much easier and more efficient way to page your data.)

Here is a completely dynamic version of my paging technique in the form of a stored procedure.  Just pass in a short SQL statement, the Order By clause, and the start row and end row you'd like to return in the resultset.  The stored procedure then will process your SQL in the order specified and return only the rows indicated.  It is based on the same principals I presented in my blog several months ago, in which the paging technique is described.

The basic idea is to process as few rows as possible; this means finding the starting point, and then returning all rows “past“ that starting point until the desired number of rows has been returned.

The stored procedure is called “ReturnPage“ and uses the following arguments:

  • @Select = the select statement to return
  • @OrderBy = the order by clause; don't include the “ORDER BY“ part, just the columns.  You must include ASC or DESC for each column in the sort
  • @StartRow = the first row to return
  • @EndRow = the end row to return

A minor tweak in the code could allow for a @RowCount argument instead of @EndRow if you wish.

Let's start with some examples from northwind:

returnpage 'select contactTitle, City, CustomerID from customers', 'ContactTitle ASC, City DESC, CustomerID ASC', 1, 10

returnpage 'select * from Orders','EmployeeID ASC, OrderDate DESC, OrderID ASC',12,31

returnpage 'select * from [order details]','productID ASC, Quantity DESC, OrderID asc',30,45

And a couple of caveats / notes about this procedure:

  • First off, it is still in beta testing so use at your own risk.  Please give me some feedback !
  • As written, the ASC/DESC designation is required for each column in the @OrderBy argument, unlike in a regular SELECT, in which ASC is the default
  • Currently, all column names in the @OrderBy argument must consist of only 1 word; even using [ ] around a column name will not work.  I didn't feel like spending too much time on the parsing routine, but feel free to improve upon it in your own implementation.
  • Feel free to print out the @SQL statement generated by the routine to see how it works
  • As a reminder, a requirement of my algorithm is that the sort columns must form a unique constraint for the resultset; you cannot sort only by “Customer Name“ if it is not always unique for each row -- you would need to include “CustomerID“  as a secondary sort.  Notice how I have done this in all of the Northwind examples.
  • There should not be any Null values in the @OrderBy columns for this technique to work

I hope you enjoy and it works fine for you.  It might seem like this routine is doing a lot of work to parse the arguments and generate the T-SQL to execute on each call, but it is all done on the server and the parsing is extemely quick since it requires no I/O or database access -- it's all just in-memory manipulation of variables. 

Anway, like the other paging implementations, it's not perfect, but it is truly a 100% server side implementation and it will process as few rows as theoretically possible for a dynamically sortable paging solution.

Final note: if anyone is interested, please let me know and I can provide some more information about how this procedure was written and how it works.  To keep the code somewhat compact, I'm sure it's not very clear.  The algorithm to generate the WHERE clause was one of the more challenging ones I've written recently.

The original post describing the paging technique can be found here: http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx


 

CREATE PROCEDURE ReturnPage(@Select varchar(1000), @OrderBy varchar(1000),

                            @StartRow int, @EndRow int)

AS

BEGIN

 

declare @ColList varchar(2000);

declare @Where varchar(2000);

declare @i int; 

declare @i2 int;

declare @tmp varchar(1000);

declare @dec varchar(1000);

declare @f varchar(100);

declare @d varchar(100);

declare @Symbol char(2);

declare @SQL varchar(5000);

declare @Sort varchar(1000);

 

set @Sort = @OrderBy + ', '

set @dec = ''

set @Where  = ''

set @SQL = ''

 

set @i = charindex(',' , @Sort)

while @i != 0

 begin

  set @tmp = left(@Sort,@i-1)

  set @i2 = charindex(' ', @tmp)

  set @f = ltrim(rtrim(left(@tmp,@i2-1)))

  set @d = ltrim(rtrim(substring(@tmp,@i2+1,100)))

  set @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))

  set @i = charindex(',', @Sort)

  set @symbol = case when @d = 'ASC' then '>' else '<' end +

                case when @i=0 then '=' else '' end

 

  set @dec = @dec + 'declare @' + @f + ' sql_variant; '

  set @ColList = isnull(replace(replace(@colList,'>','='),'<','=') + ' and ','') +

                 @f + @Symbol + ' @' + @f

  set @Where = @Where + ' OR (' + @ColList + ') '

  set @SQL = @SQL + ', @' + @f + '= ' + @f

 end

 

set @SQL = @dec + ' ' +

           'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +

           'SELECT ' + substring(@SQL,3,7000) + ' from (' + @Select + ') a ORDER BY ' +

           @OrderBy + '; ' + 'SET ROWCOUNT ' +

           convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +

           'select * from (' + @Select + ') a WHERE ' +

           substring(@Where,4,7000) + ' ORDER BY ' + @OrderBy + '; SET ROWCOUNT 0;'

 

exec(@SQL)

END

 

Print | posted on Monday, March 22, 2004 10:13 PM | Filed Under [ T-SQL Paging Data ]

Feedback

Gravatar

# Paging recordset in SQL

Jeff Smith: Efficient and DYNAMIC Server-Side Paging with T-SQL. Jusa - check that one out - looks interesting....
3/23/2004 12:14 AM | InnerGeek
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Works great so far. I'll have time to do additional testing a little later this week.

SQL coding isn't my main area, but I really appreciate those who are more adept than I at it.
3/23/2004 10:18 AM | Chad Humphries
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Great work Jeff!!

A very re-useable paging technique. I have mixed feelings about the SQL input param But definately see the advantages. When I have some time I will play around with it some more as I would like to compare this technique with the temp table technique.

Thanks for sharing.
3/23/2004 2:19 PM | Jay
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Works like a charm Jeff..

I probably won't use it much in production...we care so much ;-) we personalise the solution on as a needed basis...
3/23/2004 5:55 PM | DavidM
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

yeah ... I'm not sure how I feel about this piece of code in production either ... that would be a tough call.
3/24/2004 3:20 PM | Jeff
Gravatar

# Paging a recordset - SQL Server side.

3/29/2004 9:02 PM | Dim Blog As New ThoughtStream(Me
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Excellent features except that I have to do it many times I need to open a RS from front-end..

RAMAN BASU
4/13/2004 12:35 AM | RAMAN BASU
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

I tested on a table with 20 million rows and a clustered index on an "int" column. The performance degrages as the "startRow" gets bigger.
4/20/2004 2:04 PM | mward
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

YES -- it indeed does .

Of course, hopefully you aren't in a situation where your app is returing 20 million results to the users and the user is finding data by making requests such as "can i browse rows 1,369,230 to 1,400,849 -- i bet it's in there somewhere"!

:)

You're always allowed (and encouraged!!) to filter !!

But very true -- as @StartRow increases, the amount of time it takes to locate the starting point increases as well, since you need to process the rows in order to find the starting point.

Thanks for the feedback
4/22/2004 9:08 PM | Jeff
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

I didn't try it, but I was wondering why you have to parse anything?

Couldn't you just select the rows into a table variable with an identity column and select the rows you want?

Alternatively you could Join your qry with itself using two "top n" selections...

Ie.
Select top 10 A.* from myTable as A
Left join (Select top 10 Z.* From myTable as Z Where Params = @params) as B
On A.id = b.id
Where Params = @params
and b.id is null

It seems like a lot of work i guess... and dangerous
5/28/2004 4:27 PM | Seventhnight
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

thanks for the feedback. The reason why I don't do it that way is for performance; the whole idea here is to avoid temp tables and the top 10...bottom 10 approach is horribly ineffecient as well.

I should have been more clear in that I fully understand the normal ways of doing paging, it wasn't a general solution I was searching for; rather, the goal is to present the most efficient method possible. And I haven't seen one beat this yet.

make sure you read the other article first that explains what I'm doing with this technique, and how it is attempting to process only the exact set of rows needed to return the page requested.

Also, of course, the query you posted will not work since you are not doing any sorting, but I assume you were just posting a basic example.
5/28/2004 4:40 PM | JeffS
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Very cool query. I tried customizing a bit for myself, but failed miserably. I was wondering if you could explain the query a bit. Especially the last portion with the select upon selects.

Thanks
marc
5/28/2004 6:59 PM | Marc
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

When I try to query a view with
set @Select = 'SELECT msg_id, msg_from_address FROM summaryView WHERE client_id =300 and msg_from_address = 'XXXX' or address = 'XXXX'
set @OrderBy = 'msg_id DESC';
set @StartRow = 0;
set @EndRow = 100;

I only get one row of the 10 that I expect.
Any ideas?

Thanks
Marc
5/28/2004 8:54 PM | Marc
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL


I have one question, As long the heavy work would be on the server, why you wouldn't just
create a temporary table and benifit from the
newly inserted rows ?
5/30/2004 4:44 AM | Tareq Khalil
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Marc -- did you see the link to the original article that explains the technique? all of the code in the stored proc essentially just builds a few T-SQL statements "on the fly" that make use of that method. I will probably post a follow-up to explain more and to answer some common questions.

Tareq -- not sure what you are asking. Can you expand on that with an example? And, again, read the article that explains the alogorithm to see what I am doing; the goal is to avoid temporary tables to keep the query as efficient as theoretically possible.
5/30/2004 10:49 AM | JeffS
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

I read the original article with description of the technique, and I did a small test with your procedure versus the top 10/ top 10 method, and I have to disagree with your justification of the parsing as 'effiency based'. While the sproc is much easier to (re)use, I think you will find it hard to beat writing a customized procedure for each situation. In my test on a 7.5 million row table getting the 1st page (20 rows) of info using the sproc took 6:51 while the top 20/top 20 took 1:23.

If 'the goal is to present the most efficient method possible', then I don't think you have it right... if the goal is the most dynamic, then you might be right...

Corey Aldebol

6/1/2004 1:16 PM | Seventhnight
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

show us the queries you used to compare ... I am not what you are saying with your test by "first page" -- it took 1-6 minutes to return 20 rows? starting from where? sorted how?

Show us your sample table, what indexes you had, and what you did as a comparison between the two techniques. Then we can take a look and see which way is most efficient.
6/1/2004 1:41 PM | JeffS
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

I have a table called 'paydata', it contains the following columns: SSN nvarchar(9), ClientId int, SiteId int, PayYear int, FiscalYear int, a few datefields and a few float fields.

I used the sproc you provided as:
paydata.dbo.returnpage 'select * From paydata.dbo.paydata', 'SocialSecurityNumber ASC, ClientId ASC, SiteId ASC, PayYear DESC, FiscalYear ASC', 1, 20

and the top/top version I used was:
Declare @page int,
@pSize int,
@sqlStr nvarchar(4000)

Set @page = 1
Set @pSize = 20

Set @sqlStr = 'select top ' + convert(nvarchar,@pSize) + ' A.* From paydata.dbo.paydata as A
Left Join (Select top ' + convert(nvarchar,(@page-1)*@pSize) + ' * From paydata.dbo.paydata
Order By SocialSecurityNumber ASC, ClientId ASC, SiteId ASC, PayYear DESC, FiscalYear ASC) as B
On A.SocialSecurityNumber = B.SocialSecurityNumber and A.ClientId = B.ClientId and A.SiteId = B.SiteId and A.PayYear = B.PayYear and A.FiscalYear= B.FiscalYear
Where B.SocialSecurityNumber is null
Order By A.SocialSecurityNumber ASC, A.ClientId ASC, A.SiteId ASC, A.PayYear DESC, A.FiscalYear ASC'

Exec (@sqlstr)


The primary key is: SocialSecurityNumber, ClientId, SiteId, PayYear, FiscalYear

This was run on the same table on the same server in development.

Run time for the sproc: 6:17

Run time for top/top: 1:42

(numbers differ as I had to redo the experiment)

Corey Aldebol
6/1/2004 4:42 PM | Seventhnight
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

thanks, Corey -- i will take a look. that seems odd that any method would take so long to return the first 20 rows ... i will try to recreate your table and do some testing. One thing I am curious about is the performance of SET ROWCOUNT n versus SELECT TOP n in a general sense. It appears that TOP might be faster. The intial algorithm and design was to allow paging w/o dynamic SQL, but of course as soon as I generalized it in the stored proc dynamic SQL was the only way to implement it.

I will post back soon -- thanks for providing the info !
6/1/2004 9:27 PM | Jeffs
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

By the way I wanted to point out that I do think over 6 minutes is ridiculus, but it is running on a dual 400mhz (development) so it is going to be slower than normal. I wasn't so concerned about the 6 minutes, just the difference between the 2 processes... I am looking forward to hearing what you find!

Corey
6/2/2004 11:10 AM | Seventhnight
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

I am almost ready to post some testing results, which I will do in a new blog. but i have noticed two things:

1) using TOP is faster than SET ROWCOUNT. as mentioned, i orginally used SET ROWCOUNT to do paging non-dynamically; now that this stored proc IS dynamic, it appears TOP is a little faster.

2) the reason why they both are so slow? one column is sorted DESC and the rest ASC ... I recreated your table (assuming a clustered PK index) and changes the one DESC column to ASC -- and both methods executed in milliseconds, as opposed to a few seconds.

I am still preparing some testing data, but there's some observations for now. Thanks for all of your feedback, Corey .
6/2/2004 11:27 AM | JeffS
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

I came across this particualr articel ( code included) which has vastly generalised this entire procedure. Check the URL i have given.
I am in the middle of developing a 4 tier web app on MVC architecture using .NET and was wondering how to enable paging in my stored procedures. Am surely gonna use this code, looks pretty handy to me !!But before you can access the code and the complete article you have to register with the site. Its free but yeah the e mail validation thing is a pain.
Feel free to mail me if you wanna get the code :-)
nikhilg@solutionsny.com
6/14/2004 1:24 AM | Nikhil Gupta
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

I have to agree with seventhnight..I also ran a great deal of data using this algorithm and using the normal top/top algorithm. The top/top was always faster, whether the table consisted of 1000 rows or 500,000 rows. Interesting...interesting article, however.
6/23/2004 2:21 PM | Howie
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Jeff,
I'm new to creating & using stored procedures and have been experimenting with this one in an attempt to create a dynamic procedure I can pass search & sort parameters to (if they are present). This procedure works great, with the exception of how nulls are handled. (as you noted above) Currently, the procedure only returns the rows containing the null values, when sorted on a column containing null values, insteaad of the rowcount passed to the procedure. Are there any work-arounds you are aware of to handle nulls?
Thanks in advance from a newbee,
Pat
7/14/2004 4:01 PM | Pat
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Ashish --

as mentioned in my article, your sort columns must define each row uniquely in your result set. which is required for paging to be deterministic and not random.

if my data is :

1 A
1 B
1 C
2 D
3 E

and you wish to sort by the first column and return a letter for page 2, with a size of 2, which should it return? B,C ? or A,B? or A,C? if you say A,B then you are implying that you should have a secondary sort of the second column. which is what you should explicitly state. and if you state that, then the method works perfectly.

if you DON'T state the secondary sort, then the sorting alogorithm doesn't guarantee consistent results from call to call.
7/16/2004 10:00 AM | JeffS
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Ashish -- no offense intended, but your routine only sorts on 1 column, which must be an identity, which must already exist in that table.

The example you mentioned as a flaw in my routine doesn't work for your alternate solution at all.

Thanks for the feedback, though.
7/16/2004 10:03 AM | Jeff
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

well it works if you have the indexes set up correctly but i have not tested it fully so there may be some cases where it may fails - sorry
7/16/2004 3:52 PM | Ashish Jaiman
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

There is additional info on paging at:
http://aspfaq.com/2120

Also, you might want to explore sp_cursor* which would easily allows dynamic paging at the server.

8/27/2004 2:49 PM | oj
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

I was not able to get this to work with the query being a store procedure vs a actual select statement.
9/21/2004 9:02 PM | Steven
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Excellent article Jeff!

I tweaked it a little to allow for nulls, to allow order by columns to have table qualifiers, and to not require ASC.

If you'd like I can email it to you

mailto:nick.connor@hcahealthcare.com
10/19/2004 3:50 PM | Nick
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

gives problems when using joins and non unqiue fields
7/28/2005 10:52 PM | shivam
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Please make the necessary changes such that the procedure executes without order by clause and pls send it to my email id : joydip.kanjilal@holool.com
8/16/2005 2:12 AM | Joydip Kanjilal
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

==============
thanks for the feedback. The reason why I don't do it that way is for performance; the whole idea here is to avoid temp tables and the top 10...bottom 10 approach is horribly ineffecient as well.
==============

The person you responded to suggested Table Variables which are much more efficient than Temp Tables. They do not need to be dropped.

I prefer the Table Variables method over what's shown here.
8/29/2005 12:00 PM | Chris T.
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Any way to get the recordcount out of this?
9/14/2005 10:11 PM | Dave
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Wow ... lots of great feeback!

>>There is additional info on paging at:
>>http://aspfaq.com/2120

Great link! Well done article. Unfortunately, I could not find where the author of the ROWCOUNT method (the one I presented here) original published or posted his idea, so I can't tell who came up with it first. The good news -- according to that article, my method is the fastest.

>>gives problems when using joins and non unqiue fields

uh .... Yes. It does. As explained. And as it should when you are working with a RELATIONAL database.

>>Please make the necessary changes such that the procedure executes without order by clause and pls send it to my email id : joydip.kanjilal@holool.com

Huh ??? I appreciate the feedback, but I really don't know where some of these people are coming from. You can't really return a page of data without ordering. Again, boys and girls, we are working with a relational database here, not Excel.

>>The person you responded to suggested Table Variables which are much more efficient than Temp Tables. They do not need to be dropped.
>>I prefer the Table Variables method over what's shown here.

Something tells me you haven't tested both methods .... and, again, this method does not need a temp table OR a table variable. Thanks for the input though!

>>Any way to get the recordcount out of this?

Not sure what you are asking ... are you familiar with SELECT COUNT(*) FROM ... ? A pretty good way to get rowcounts. If you want to return or display the rowcount of a resultset, do it at the client side. Otherwise, please be more specific and/or check out Books OnLine for more information on aggregate functions such as COUNT().

- Jeff
9/14/2005 10:40 PM | Jeff S
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Hey Jeff

Am familiar with count(*) but was wondering if there was a more efficient way to return recordcount in the procedure without asking for count(*) in the query. I tinkered a bit, couldn't find one, and ended up grabbing count(*) using a subquery so I wouldn't have to do group by on all the fields.

My sql ended up looking like this, where strQuery is the WHERE clause:

strSQL="SELECT field1, field2, field3, (SELECT COUNT(*) FROM TABLE "&strQuery&") as nRecs FROM TABLE "&strQuery&""

9/16/2005 2:50 PM | Dave
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

The easiest and most efficient way to display the count after returning a set of rows from a stored proc is to do it at the client-side. Otherwise, you need to process the data twice within T-SQL.

If you are returning data to a client, then any Totals, subtotals, running totals, grouping, row numbers, total counts, etc -- all should be done at the client side. All report writing tools have these features, and it is pretty easy to get these values or calc these results using programming languages like ASP or ASP.NET.

9/16/2005 3:02 PM | Jeff
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

THANKS THANKS THANKS !!!

Tonight u save my life....
Works fine with 2000/3000 rows (ASP+SQLServer 200 / ADO sux)
Now i can go to bed

Greeting from France.
9/29/2005 3:44 PM | Samish
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

hello, i have sybase database
how can this procedure be transformed to sybase.
thank in advance for your help
10/10/2005 9:00 PM | dany
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Great work... Thanks

This is really fastest version among all Paging techniques but I found a bug in this SP

If you do sort by ASC for a column which has several NULL values then this SP wont work.

e.g.

ReturnPage 'select * from FIELDSETTINGS','CONTROLWIDTH',1,30

Where CONTROLWIDTH has NULL values in more than 50% records and ReturnPage is not returning any record.

Thanks,
Nayan
10/16/2005 10:16 PM | Nayan
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Thanks, Nayan -- the NULL situation is mentioned in the spec, so I don't know if I would call it a bug.

To handle NULLS, you'd need to alter the WHERE condition generated so that

@Col1 = Col1

becomes

(@Col1 = Col1) OR (@Col1 is null AND Col1 is null)

I'll leave that as an excercise for the readers! :)
10/18/2005 12:06 PM | Jeff
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

There is a problem if the sort by columns have a sequence of same values - specifically across the page boundary records. The where clause will not be able to filter starting from the correct record
10/26/2005 7:14 PM | Anoop John
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

>>There is a problem if the sort by columns have a sequence of same values - specifically across the page boundary records. The where clause will not be able to filter starting from the correct record

Make sure you have read the article, including the post that describes the original (non-dynamic) technique. Then, re-read the comments here, especially my reply to Ashish.

To do any paging in a relational database using ANY method, your sort columns MUST uniquely identify each row in your resultset since RDBMS's by definition have no row ordering. Let me know if you need further info ....
10/26/2005 8:09 PM | Jeff S
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

We use this way of paging with one difference: we generate the SQLs in C#. After some searching on the net, I had decided that this approach was the best one performance wise, and had dynamic filtering and sorting capabilities. We extended it to support group by and having clauses, nulls, and a special query for the first page.
We also observe performance degradations while retrieving the last pages with millions of rows. But still I don't think other approaches will perform better (I am talking about the ones with dynamic sorting and filtering capabilities).
The other problem is you have to run a COUNT(*) which takes time with millions of rows. We modified our UI to work without knowing the number of pages/records.
Does anyone have numbers and examples backing up their claims that other approaches perform better?

10/27/2005 2:27 AM | Ertugrul
Gravatar

# When sort columns do not uniquely identify a row

I am not undermining the brilliance of this strategy - I am just pointing out there is a problem when the sort by columns dont uniquely identify a table. Yes I see that you have mentioned in the first article why you are adding the primary key column in the sort.
I was trying to create a wrapper class for pagination in ASP. Developers were using a normal sort order property and it was failing. The work around was to force the developer in entering the primary key column information for the class.
As an aside - the ADO rst.Move(startpos) strategy using server sider cursor - has comparable performance and works without uniqueness for sort columns.


10/27/2005 3:32 PM | Anoop John
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Thanks, Anoop John, good stuff. As mentioned in the comments earlier on, TOP does seem to be more efifcient than ROWCOUNT so that's a good alteration.

But, again, you seem to be failing to grasp a key concept in relational databases. What if I have this data:

ID, Val, State
1, A, MA
2, B, CA
3, B, IN
4, B, RI
5, C, ME

And let's say we specify a page size of 2, and I want to return the 2nd page, ordered by the "Value" column. Which states gets returned? Remember, we are ordering ONLY on the Value column. Which rows should a relational database return in this scenerio?
10/27/2005 4:25 PM | Jeff
Gravatar

# Modified SP with TOP and handling NULL columns

Jeff - Regarding your question, if the query without pagination returns

ID, Val, State
1, A, MA
2, B, CA
3, B, IN
4, B, RI
5, C, ME

then second page should be
3, B, IN
4, B, RI

Yes I understand that you have to have some of unique key or unique combinations of columns to be able to identify a specific record/row and thereby the start of a page.
consider the scenario where we dont have that. u run a query to get a set of records, if we rerun it without changing the data - we WILL get the same set of records in the SAME order - by virtue of the physical storage location of the records being referred to or by virtue of the indexes on the columns. Now this brings us to a concept of a virtual unique key - the rownumber. if the data does not change - the row number remains same. and we are entitled to get rows specified by the rownumbers if the server supports that. something similar to this is happening through rowcount and top. now the current strategy tries to retrieve a unique key from the data given the virtual primary key ie the rownumber. this requires that a primary key column or key formed by combination of columns should exist in the data and uses this to get the next n records from that given record. yes i truly agree to what you are saying.

Here is the complete procedure working condition handling null and also using the top n

CREATE PROCEDURE DBO.CREA_RST_PAGINATE(@Select VARCHAR(5000),
@OrderBy VARCHAR(1000),
@StartRow INT,
@EndRow INT)
AS
BEGIN

DECLARE @ColList VARCHAR(5000);
DECLARE @Where VARCHAR(5000);
DECLARE @i INT;
DECLARE @i2 INT;
DECLARE @tmp VARCHAR(1000);
DECLARE @dec VARCHAR(1000);
DECLARE @f VARCHAR(100);
DECLARE @d VARCHAR(100);
DECLARE @Symbol CHAR(2);
DECLARE @NullBoundaryCheck VARCHAR(1000);
DECLARE @SQL VARCHAR(8000);
DECLARE @Sort VARCHAR(1000);

SET @Sort = @OrderBy + ', '
SET @dec = ''
SET @Where = ''
SET @SQL = ''
SET @i = CHARINDEX(',' , @Sort)

WHILE @i != 0
BEGIN
SET @tmp = LEFT(@Sort, @i-1)
SET @i2 = CHARINDEX(' ', @tmp)
SET @f = LTRIM(RTRIM(LEFT(@tmp, @i2-1)))
SET @d = LTRIM(RTRIM(SUBSTRING(@tmp, @i2+1, 1000)))
SET @Sort = RTRIM(LTRIM(SUBSTRING(@Sort, @i+1, 1000)))
SET @i = CHARINDEX(',', @Sort)
SET @symbol = CASE WHEN @d = 'ASC' THEN '>' ELSE '<' END +
CASE WHEN @i = 0 THEN '=' ELSE '' END
SET @NullBoundaryCheck = CASE WHEN @i = 0
THEN CASE WHEN @d = 'ASC' THEN '@' ELSE '' END + @f + ' IS NULL'
ELSE
'@' + @f + ' IS ' + CASE WHEN @d = 'DESC' THEN '__NOT__' ELSE '' END + ' NULL AND ' +
@f + ' IS ' + CASE WHEN @d = 'ASC' THEN '__NOT__' ELSE '' END + ' NULL'
END
SET @dec = @dec + 'DECLARE @' + @f + ' SQL_VARIANT; '
SET @ColList = ISNULL(REPLACE(REPLACE(REPLACE(@colList, '>', '='), '<', '='), '__NOT__', '') + ' AND ', '') +
'((@' + @f + ' IS NOT NULL AND ' + @f + ' IS NOT NULL AND ' + @f + @Symbol + '@' + @f + ') OR (' + @NullBoundaryCheck + '))'
SET @Where = @Where + ' OR (' + @ColList + ') '
SET @SQL = @SQL + ', @' + @f + '= ' + @f
--PRINT @COLLIST
--PRINT @WHERE
--PRINT @SQL
END
SET @Where = REPLACE(@Where, '__NOT__', 'NOT')
SET @SQL = @dec + ' ' +
'SET ROWCOUNT ' + CONVERT(VARCHAR(10), @StartRow) + '; ' +
'SELECT ' + SUBSTRING(@SQL,3,7000) + ' FROM (' + @Select + ') A ORDER BY ' +
@OrderBy + '; ' +
'SET ROWCOUNT 0;' +
'SELECT TOP ' + CONVERT(VARCHAR(10), (1 + @EndRow - @StartRow)) + ' * FROM (' + @Select + ') A WHERE ' +
SUBSTRING(@Where,4,7000) + ' ORDER BY ' + @OrderBy + '; '
--PRINT @WHERE
--PRINT @SQL
EXEC (@SQL)
END
GO
10/28/2005 6:56 PM | Anoop John
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Oh dude! Thank you!
12/12/2005 6:46 AM | Wei Sun
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Hi and thanks, it works very good, even with complicated views...great work, thanks to author...
4/17/2006 11:40 AM | L.Holota
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

The slowest and the dumbest way to do paging. :( Why is it "efficient"? Probably because of relatively short and omni - used SP? The shorter T-SQL the more efficient it is? ;)
The server won't be able to use cached execution plan with dynamic SQL, so I don't see anything efficient here.
6/9/2006 9:20 AM | Max
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with T-SQL

Hi Max -- I don't know if it is the "fastest" (I don't think I claimed that), but it is pretty efficient. I am definitely willing to learn from others if any of my code can be improved ... could you explain to me why this techinique is the "slowest and dumbest" ? That's a pretty strong statement, don't you think?

I do hope that you understand that by definition this proc is design to work with any sql passed to it ... so of course it will not cache execution plans! If you want that, then use the non-dynamic technique (which I linked to). Otherwise, I hope you will agree that the time needed to generate an execution plan is minimal compared to the time needed to calculate how to return rows x-y from a very large resultset.

Anyway, I would definitely appreciate any more specific insight that you could provide .... Thank you for your feedback!

- Jeff
6/9/2006 9:47 AM | Jeff
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

I am new to SQL Server and I really liked your solution.
Solved big problem for me..
Thanks.
6/4/2007 7:39 AM | Sangram
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Great work Jeff, it worked the first time I compiled it and ran it...excellent effort.
7/12/2007 1:58 AM | Ray
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

The work around was to force the developer in entering the primary key column information for the class.
As an aside - the ADO rst.Move(startpos) strategy using server sider cursor - has comparable performance and works without uniqueness for sort columns.
9/3/2007 5:18 PM | seo yarışması
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

There is a problem if the sort by columns have a sequence of same values - specifically across the page boundary records. The where clause will not be able to filter starting from the correct record
9/3/2007 5:30 PM | kuresel isinma
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

kuresel isinma -- Welcome to the wonderful world of relational databases ... read the article.
9/3/2007 5:48 PM | Jeff
Gravatar

# lost 1 record or duplication one record in the start or end of the page

Dear Sir

First, thanks a lot. It is really great in use.
But found somtime will lost 1 record or duplication one record in the start or end of the page.
Any suggestion?

returnpage 'Long SQL', 'Date asc, Time asc', 3945, 3973
returnpage 'Long SQL', 'Date asc, Time asc', 3916, 3944
returnpage 'Long SQL', 'Date asc, Time asc', 3887, 3915

In case of missing, the record is in DB, it is supposed to appear in the 1st line of next page, but is missing in the next page.

returnpage 'select * from gensum where ((Date&amp;amp;amp;gt;''2007-09-01'' or (Date=''2007-09-01'' and Time&amp;amp;amp;gt;''00:00:00'')) and (Date&amp;amp;amp;lt;''2007-09-11'' or (Date=''2007-09-11'' and Time&amp;amp;amp;lt;''16:26:09'')))', 'Date asc, Time asc', 3945, 3973
9/11/2007 10:27 PM | Turbot
Gravatar

# Iave increaese the definition

Not sure if it affect it.

declare @ColList varchar(8000);
declare @Where varchar(8000);
declare @i int;
declare @i2 int;
declare @tmp varchar(8000);
declare @dec varchar(8000);
declare @f varchar(1000);
declare @d varchar(1000);
declare @Symbol char(20);
declare @SQL varchar(8000);
declare @Sort varchar(8000);
9/11/2007 10:51 PM | Turbot
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Turbot -- did you read the article? What is the primary key of the result set that you are paging?

What does this return:

select [Date], [Time], count(*)
from
( -- your long sql statement here --) x
group by [Date], [Time]
having count(*) > 1

If it returns any rows, then your data cannot be paged deterministically by those two columns alone.
9/12/2007 8:57 AM | Jeff
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

There should not be duplication since it will precise to milli second level, but I will try it on site today.

The primary key of the table is gensumid, but since some late data will arrive first, it cannot be used for sort.
9/12/2007 9:51 PM | Turbot
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Sorry, it do have some duplication from other errors.

Now I add the primary key 'gensumid' to the sort field as below and the one lost record come out.

returnpage 'Long SQL', 'Date asc, Time asc, gensumid asc', 3945, 3973

So it should be ok, right, thanks a lot.
9/13/2007 2:45 AM | Turbot
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

I Agree With Kuresel Isınma :):)
Gravatar

# hayko cepkin

The slowest and the dumbest way to do paging. :( Why is it "efficient"? Probably because of relatively short and omni - used SP? The shorter T-SQL the more efficient it is? ;)
The server won't be able to use cached execution plan with dynamic SQL, so I don't see anything efficient here.
10/13/2007 8:12 PM | hayko cepkin
Gravatar

# kavak yelleri

kavak yelleri There is a problem if the sort by columns have a sequence of same values - specifically across the page boundary records
10/13/2007 8:15 PM | kavak yelleri
Gravatar

# ibrahim tatlıses

So it should be ok, right, thanks a lot.
10/13/2007 8:17 PM | ibrahim tatlıses
Gravatar

# kuruyemiş

thank you.
11/1/2007 12:34 PM | bahariye
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

The work around was to force the developer in entering the primary key column information for the class.
As an aside - the ADO rst.Move(startpos) strategy using server sider cursor - has comparable performance and works without uniqueness for sort columns.
6/20/2008 4:03 PM | Karadeniz
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

very nice
6/22/2008 7:07 AM | iyinet webmaster forumu 2008 seo
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Hi, I would like to know if it is possible to make a query with NULLs parameteres:

I had the following query:

SELECT *
FROM
vw_Articulos
WHERE
Denominacion LIKE ISNULL('%' + @Denominacion + '%',Denominacion)
AND EsComercializacion= ISNULL(@EsComercializacion,EsComercializacion)
AND EsServicio= ISNULL(@EsServicio,EsServicio)
AND EsProduccion= ISNULL(@EsProduccion,EsProduccion)
AND GrupoArticulo= ISNULL(@GrupoArticulo,GrupoArticulo)
AND LineaArticulo= ISNULL(@LineaArticulo,LineaArticulo)
AND MarcaArticulo= ISNULL(@MarcaArticulo,MarcaArticulo)
AND Eliminado= ISNULL(@Eliminado,Eliminado)
ORDER BY
Denominacion

If @Denominacion comes null it is not filtered by this parameter, is replaced by Denominacion so it is like no filter exists.

But I have problem to do that with the Pagination Store Procedure, I tried modifing it, it looks like this:

SET @SENTENCIA = 'SELECT *
FROM
vw_Articulos
WHERE Denominacion LIKE ISNULL(''%' + @Denominacion + '%'',Denominacion)'

exec returnpage2 @SENTENCIA, 'Denominacion ',1,2

It works when I pass a not null parameter, but if it is null nothing return.

Anybody knows what is wrong ? Can anybody provide me an example with parameters ?

Thank you!!


7/9/2008 3:25 PM | Francisco Gambino
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Francisco --

see: http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

Don't use ISNULL() to handle NULL parameters; use boolean logic.
7/17/2008 9:11 AM | jeff
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Jeff,
thanks for your answer, I tried using boolean expresions but I am still having problems. i will try to explain you my problem:

I have the following Store Procedure with a Dinamic Sentence:

CREATE PROCEDURE [dbo].[spArticulosGetByFilter2]
(
@Denominacion as nvarchar(255)= NULL,
@GrupoArticulo as int= NULL,
@LineaArticulo as int= NULL,
@MarcaArticulo as int= NULL,
@EsServicio as bit= NULL,
@EsComercializacion as bit= NULL,
@EsProduccion as bit= NULL,
@Eliminado as bit= NULL,
@Paginado as bit
)
AS
BEGIN
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL ON
DECLARE @Err int
DECLARE @SENTENCIA VARCHAR(3000)

IF @Paginado = 1
BEGIN

SET @SENTENCIA = 'SELECT *
FROM
vw_Articulos
where ('''+ @Denominacion +''' IS NULL or Denominacion LIKE ''%'+ @Denominacion + '%'')'


exec returnpage2 @SENTENCIA, 'Denominacion ',1,5

print @SENTENCIA

END
END


If i execute the SP:

spArticulosGetByFilter2 'es',null,null,null,null,null,null,0,1

I did a print for the @SQL, and the query is:

SELECT *
FROM
vw_Articulos
where ('es' IS NULL or Denominacion LIKE '%es%')

and it works fine.

But if instead of 'es' I pass null parameter the @SQL is Blank with SET CONCAT_NULL_YIELDS_NULL ON
and with CONCAT_NULL_YIELDS_NULL OFF the query is: (with the error)

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'AND'.
SELECT *
FROM
vw_Articulos
where ('' IS NULL or Denominacion LIKE '%%')

So, my problem is when a null parameter comes all the string is null (I think because something + null is null always, but I need to avoid it)

Do you have any other suggestion?

Thanks a lot!


7/17/2008 11:38 PM | Francisco Gambino
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Francisco -- do not use dynamic SQL! you are greatly overcomplicating something that is very simple. In your example, all you need is:


SELECT *
FROM
vw_Articulos
where Denominacion like '%' + isnull(@Denominacion,'') + '%'

That's it! No dynamic SQL is needed!
7/18/2008 9:45 AM | jeff
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Jeff, if I don't use Dinamic Sql, how I am going to pass the @Select varchar(1000) to the procedure that you posted here to do the pagination.
You procedure needs as first parameter a varchar parameter.

Maybe I am confused.
Please let me know if I am wrong.
7/18/2008 3:37 PM | Francisco Gambino
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Ah, I see, you are using the dynamic paging technique, forgot about that.

Don't use boolean logic to do optional paraemters if you are building the SQL from scratch -- just build the SELECT the way you need it.

i.e.,

set @SQL = 'SELECT * FROM vw_Articulos'

if (@Denominacion is not null)
set @SQL = @SQL + ' where Denominacion like ''%' + @Denominacion + '%'''


And so on, for any other optional parameters. Just build your condition dynamically based on the parameters, since the entire SELECT is dynamic anyway.
7/18/2008 4:16 PM | jeff
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Thanks Jeff, it is working now!!
I am very grateful for your help!

Thanks!
7/19/2008 3:53 PM | Francisco Gambino
Gravatar

# memur

It’s such a simple thing to fix that the only justification for not doing so must be legacy application support.
8/15/2008 9:59 AM | memur
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Jeff, if I don't use Dinamic Sql, how I am going to pass the @Select varchar(1000) to the procedure that you posted here to do the pagination.
You procedure needs as first parameter a varchar parameter.

Maybe I am confused.
Please let me know if I am wrong.
8/15/2008 12:45 PM | sikiş
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

I was playing on comparing a few paging algorythms (also found the page linked to somewhere here in this comments) and tried two more approaches: Cursors and Exclusion

Currently still in dev with the cursor method, but the extremely simple Exclusion method, which I added for reference as probably worstcase, turned out to have a very suprising performance. My sample data was 10.000 records of a fairly large table, and I'm sorting it on 3 columns, mixing indexes and non-indexes. I implemented Exclusion, Top-Bottom-Resort and a slightly adjusted version of Jeff's here. (adjustment is to use TOP in the 2nd query over SET ROWCOUNT, as per suggestion in this discussion here that TOP in itself is more efficient as ROWCOUNT).

Hereby, merely as the odd suggestion: Exclusion

SELECT * FROM Geraete ORDER BY Inst, Lieferant DESC, SerienNummer

Gets modified by a little dynamic SQL into to select the 4901 to 5000 records:

SELECT TOP 100 FROM Geraete WHERE uid NOT IN (
SELECT TOP 4900 uid FROM Geraete ORDER BY Inst, Lieferant DESC, SerienNummer
) ORDER BY Inst, Lieferant DESC, SerienNummer

Seriously expected the NOT IN clause over 4900 records to be horrendous in its performance, but in fact it performed comparably with Jeff's more complex approach in around .200 sec (slow lousy laptop to dev on), while the widely suggsted Top-Bottom-Resort approach took well over 3 secs.
8/28/2008 8:44 AM | SartriX
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Guys,

What will happens if I want to select fields from different tables using INNER JOIN?

For example:
Table Customers : CustomerTitle
Table Orders: OrderID, CustomerID

Then the input SELECT statement will be like this:

SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Customers.ContactTitle FROM dbo.Orders INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID
ORDER BY dbo.Customers.ContactTitle ASC, dbo.Orders.CustomerID ASC, dbo.Orders.OrderID ASC

One of the method is to create a view and get the SELECT command. However, as far as I know view does not support sorting feature.

Any solution for this?
10/16/2008 6:51 PM | Joy
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Customers.ContactTitle FROM dbo.Orders INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID
ORDER BY dbo.Customers.ContactTitle ASC, dbo.Orders.CustomerID ASC, dbo.Orders.OrderID ASC

eror?
11/15/2008 9:42 PM | vps
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

One of the method is to create a view and get the SELECT command. However, as far as I know view does not support sorting feature.
11/26/2008 12:08 PM | Rize
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

I will not claim to be smart or understand the procedure, I am not a database programmer
but noticed that the last row is not returned
I changed convert(varchar(10), 1 + @EndRow - @StartRow)
to convert(varchar(10), @EndRow ) where @EndRow is page size and it works now
hope i did not say something stupid :)
thanks for the code
12/15/2008 10:41 PM | antoniu
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

THANKS

Tonight u save my life....
Works fine with 2000/3000 rows (ASP+SQLServer 200 / ADO sux)
Now i can go to bed

Greeting from France.
1/18/2009 4:01 AM | Atmaca
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

than kyou admınn
4/20/2009 10:39 AM | Denizli web tasarım
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Thanks admin
5/16/2009 11:18 AM | cinsel chat
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

THanks very good
5/16/2009 11:19 AM | canlı sohbet
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

It’s such a simple thing to fix that the only justification for not doing so must be legacy application support.
7/9/2009 9:50 AM | memur
Gravatar

# re: Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

thanks you
You will have to crawl very nice,owe you gratitude..
1/28/2010 2:47 PM | sikiş
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET