03 September, 2010

Improvements In Last Post

Create Procedure [dbo].[MoreEfficient_Paging_Student]
@SortColumn varchar(50),
@SortDirection varchar(4),
@PageSize int,
@PageNumber int
AS

DECLARE @MaxRows int
SET @MaxRows = @PageSize * @PageNumber

DECLARE @Count int
SET @Count = (SELECT COUNT(*) FROM student)

IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )

IF(@SortDirection = 'ASC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM (
SELECT Top (@PageSize) ID, Name, Marks
FROM (
SELECT TOP (@MaxRows) ID, Name, Marks
FROM student
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END),
ID
)
AS Foo
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END) DESC,
ID DESC
)
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END),
bar.ID
END
ELSE IF(@SortDirection = 'DESC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM (
SELECT Top (@PageSize) ID, Name, Marks
FROM (
SELECT TOP (@MaxRows) ID, Name, Marks
FROM student
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END),
ID

)
AS Foo
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END) DESC,
ID DESC
)
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END) DESC,
bar.ID
END

Improvements:

(1) The last page in previous post was showing as much records as the page size. For example if there are 10 rows in table and page size is 3 and we are requesting 4rth page (pages in all these three posts are 1-based indexed), then its returning 8th, 9th and 10th row. It should be returning only the 10th row. This is corrected in this post by putting following lines:

DECLARE @Count int
SET @Count = (SELECT COUNT(*) FROM student)

IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )

In scenario above, @Count is 10, @MaxRows is 12, @PageSize is 3, @PageNumber is 4. The above lines do this:

@PageSize = 10 - ( (4-1) * 3)
@PageSize = 10 - (3 * 3)
@PageSize = 10 - 9
@PageSize = 1

Therefore only one row is shown in the 4rth page.

(2) Another problem in previous post was that if we input a page number greater than the maximum number of pages in the table, it still return the last few rows (as much rows as the page size). This get corrected automatically in above lines:

For page 5, (row numbers 13th, 14th, 15th), no rows exist (since table has 10 rows), so there must be an error. Using above code:

IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )

IF( (15 - 10) > 0) --condition is true
@PageSize = 10 - ( (5 - 1) * 3)
@PageSize = 10 - (4 * 3)
@PageSize = -12

That is, a negative number. When this is used in "top" statement in sql, it result in error because a "top" variable can't be negative.

(3) Efficiency (both in terms of memory and speed). The previous post was using two temporary tables, first of them might be as large as the entire table if page number is the last page number, second table was a only as large as the page size so no issue there. This post don't use any temporary table, instead the entire query run as a whole (though its quiet complex and long).

No comments:

Post a Comment