Wrong serverside paging when order column(s) are not different in value

Posts   
 
    
dotprof
User
Posts: 20
Joined: 19-Feb-2010
# Posted on: 28-Oct-2011 19:11:07   

Hello, I am using LLblgen Pro version 3.1 version 7 Feb 2011.

I discovered a strange behaviour when using a LLblgen datasource with a Telerik Radgrid on a .Net webpage. I had paging enabled on datasource and grid. The table had 13 records and pagesize was 10.

The 11th record never showed up. In fact the 10th record showed up at the bottom of page 1 and at the top of page 2.

The order by used was on a column that mostly contained the value 0. Only when I added a second (unique) column in the sort expression the grid behaved properly.

I could reproduce the error with the traced sql statements.

I think it has to do with the different (sort) methods llblgen uses for the 1 page (pageindex=0) and the other pages of the the table. See the profiler results for the 1 page and 2 page.

page 1: A select statement with Top 10 is used:

exec sp_executesql N'SELECT TOP 10 [DotprofReunie].[dbo].[MessageTemplate].[Title] AS [Title] 
 FROM [DotprofReunie].[dbo].[MessageTemplate]  
  WHERE ( ( ( [DotprofReunie].[dbo].[MessageTemplate].[PortalID] = 0)))
   ORDER BY [DotprofReunie].[dbo].[MessageTemplate].[DisplayOrder] ASC'

Page 2: A table expression, row_number function and order by current_timestamp is used

exec sp_executesql N'WITH __actualSet AS (
 SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt 
 FROM (SELECT TOP 20 [DotprofReunie].[dbo].[MessageTemplate].[Title] AS [Title]
 FROM [DotprofReunie].[dbo].[MessageTemplate] 
           WHERE ( ( ( [DotprofReunie].[dbo].[MessageTemplate].[PortalID] = 0)))
            ORDER BY [DotprofReunie].[dbo].[MessageTemplate].[DisplayOrder] ASC) AS _tmpSet) 
            SELECT [Title]
            FROM __actualSet 
            WHERE [__rowcnt] > @p2 AND [__rowcnt] <= @p3 ORDER BY [__rowcnt] ASC',N'@p2 int,@p3 int',@p2=10,@p3=20

I think that for page 1 it is also neccessary to introduce the sort by current_timestamp , or for page 2: use also the sorter column, e.g. DisplayOrder in this example when determining rownumbers?

thanks

Piet

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 28-Oct-2011 20:15:27   

This is expected and at some point it's unavoidable. Here are some discussions about the same situation: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=15653 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=13353 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8388

The best you can do is to put some unique column in the sorter, as you already found.

David Elizondo | LLBLGen Support Team
dotprof
User
Posts: 20
Joined: 19-Feb-2010
# Posted on: 29-Oct-2011 07:58:39   

Thanks daelmo. Reinvented the wheel (again...) Next time you can answer with a reference to four links simple_smile