The ways to paging

In projects, you must have the experience of how to paging datas. you would have below options:

1. Fetch all the data first, then do the paging in BE logic.

    such as Linq:

    List<string> results = (from c in lists
                               where !string.IsNullOrEmpty(c)
                               select c).Skip(skip).Take(count).ToList();

 

2. Use the feature of existed framwork, such as NHibernate,

    IQuery q =  Helper.CurrentSession.CreateQuery( getHQL )
                             .SetMaxResults( pageSize )
                             .SetFirstResult( startIndex )
                             .SetCacheable( true );

 

3. Use store procedure, TSQL. use dynamic sql in store procedure.

    @count int

    DECLARE @SQLString NVARCHAR(500);

    SET @SQLString = N'SELECT TOP ' + CAST(@count AS NVARCHAR(10)) + ' * FirstName, LastName, JobTitle' + CHAR(13);

    EXEC sp_executesql @SQLString;

 

4. Use logic in sql, combine the row number in a temp variable.

CREATE PROCEDURE GetItems
@count int = 0
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ItemID
    FROM   
    (SELECT ROW_NUMBER() OVER(ORDER BY ItemID) as Id,
           ItemID,
      FROM dbo.MediaVideos
     ) a
     WHERE Id <= @count 
END
GO

 

For the perfomance point, if the records amount in table is not very big, the #4 option will be idea one.