0

I have a large table having millions of records. I am binding this table to gridview in my application. Since data is large, we are retrieving data using paging concepts. like if i set the gridview page size to 2000, then i am fetching only 2000 records from table. i am using following Query for this

Select * from (select *, Row_Number() over (order by id) as Row_Index) a 
where
Row_Index > @start_index and Row_Index < @End_Index

This query run fast for first few millions of records but as the start and end index increases performace degrades drastically. How can i improve this query

Tarec
  • 3,268
  • 4
  • 30
  • 47
Rajeev Kumar
  • 4,901
  • 8
  • 48
  • 83

2 Answers2

2

Make your unique column be index (either clustered of non-clustered) like your ID column in table is a good candidate if it has no duplicate..

Or add an AutoIncremented Column ID.

You may also use query like this

Select  top 2000 *
from    t
where ID >= @start_index
order by ID
Jade
  • 2,972
  • 1
  • 11
  • 9
  • `id` is already a primary key column in table over which i am creating `row_number()` – Rajeev Kumar May 30 '14 at 11:06
  • I need to fetch 2000 records every time, Id may be break in between. It is not neccessay that Id have value 1,2,3 and so on. Some rows may be deleted in between then in that case you end with fetching rows less than 2000 – Rajeev Kumar May 30 '14 at 11:09
  • Say your iniital id is 93, it will pull id 93 and higher of up to 2000 records regardless of ID value (say 10000) as long as it is inside 2000(N) records to be pulled out. – Jade May 30 '14 at 11:12
  • Good for you, sometimes simple solution is underneath. :) – Jade May 30 '14 at 11:17
0
DECLARE @From  int ,@Thru  int

--  Example here would be the second set
SET @From = 1
SET @Thru = 1000

SELECT <columns>
 from (select <columns>, row_number() over (order by <PrimaryKey>) Ranking
        from MyTable) xx
 where Ranking between @From and @Thru
Systematix Infotech
  • 2,345
  • 1
  • 14
  • 31