CODEWITHSUNDEEP
c#winformssql-server-2008
Rocshy
Rocshy

Reputation: 3509

Paginate SQL query

On a winform, in a datagrid, I am displaying about 100k rows, selected from the DB. Showing all these records take a lot of time. Is there a way to make the select query faster or maybe load the first 200 records. And then if the users click the next button, the next 200 records will be displayed. Is this possible? I know mysql has LIMIT, but I need something to work for sql-server 2008.

Upvotes: 0

Views: 1075

Answers (4)

ABCD
ABCD

Reputation: 907

You can use the a Common Table Expression, with the Row_Number ranking function. Here is an example:

CREATE PROCEDURE PagingSample
  @PageNumber int,
  @PageSize int
AS

WITH Results AS (
  SELECT 
    ROW_NUMBER() OVER(ORDER BY MR.MRN ASC) As RowNumber,
    MR.MRN
FROM 
  dbo.SomeTable MR WITH (NOLOCK)    
)

SELECT 
  R.RowNumber,
  R.MRN
FROM
  Results R
WHERE
  RowNumber > (@PageNumber * @PageSize) - @PageSize 
  AND RowNumber < (@PageNumber * @PageSize) + 1

Now pass the page number and the size of your page to the sproc, like so:

Exec PagingSample @PageNumber = 3, @PageSize = 100

And you will get records 201 through 300

Upvotes: 0

Nilish
Nilish

Reputation: 1076

Stored Proc

Alter Proc Test
@PageNumber int,
@PageSize int

as

create table #t
(
    id int
)
insert into #t(id)values(1)
insert into #t(id)values(2)
insert into #t(id)values(3)
insert into #t(id)values(4)
insert into #t(id)values(5)
insert into #t(id)values(6)
insert into #t(id)values(7)
insert into #t(id)values(8)
insert into #t(id)values(9)
insert into #t(id)values(10)

declare @StartIndex int
declare @EndIndex int
declare @PageSizeIndex int

Set @StartIndex = ((@PageNumber - 1) * @PageSize) + 1
Set @EndIndex = @PageNumber * @PageSize

Select RowID, ID From
(
    Select ROW_NUMBER() Over(Order by id) as RowID, ID From #t
)K
Where K.RowID >= @StartIndex and k.RowID <= @EndIndex

Drop table #t

Testing Purpose Data testing

Test 1, 3

In addition to the above Stored Proc, You can implement Indexes to make the search fast or you can use SQL Profiler to check the reason for delay in execution time.

Upvotes: 1

Joe Enos
Joe Enos

Reputation: 40393

There's a method to it, but it's not pretty. If you're using Entity Framework, you can write LINQ to paginate results, something like:

var books= context.Books.OrderBy(b => b.Title).Skip(300).Take(100);

If you throw a SQL Profiler on it, the generated SQL will look something like the following, which you can use as a guide to build your own statement:

SELECT TOP (200) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Title] AS [Title]
FROM
(
    SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Title] AS [Title],
        row_number() OVER (ORDER BY [Extent1].[Title] ASC) AS [row_number]
    FROM [dbo].[Books] AS [Extent1]
)  AS [Extent1]
WHERE
    [Extent1].[row_number] > 100
ORDER BY
    [Extent1].[Title] ASC

Upvotes: 1

StuartLC
StuartLC

Reputation: 107247

There are several ways to do this, usually using a CTE or nested query and row_number(). See e.g. How to do pagination in SQL Server 2008

And in SQL 2012, there is now the ability to do this in a single query at last, viz OFFSET xxx ROWS FETCH NEXT XXX ROWS ONLY - see Row Offset in SQL Server

Upvotes: 0

Related Questions