Paging…. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Paging….

I know this has been discussed many times.
I have the following problem
I have 2 tables
Table T1
(
ID int PK
Subject nvarchar(120)
Date1 datetime
.
.
.
) Table T2
(
ID int FK
index int
Date2 datetime
.
.
.)
As you see T1 has a one-many relationship with T2. T2 has a PK of (ID,index) T1 has 1.2M records
T2 has 4.8M records im trying to get 1000 records in each page of data the sql would be
select * from T1 LEFT JOIN T2 ON T1.ID = T2.ID WHERE T2.Index=0 ORDER BY Date2 Date2 is indexed non-clustered. I have tried all the paging methods mentioned here
http://www.codeproject.com/aspnet/PagingLarge.asp The rowcount method would not work if i specified 2 different order by columns of different type for example (date2,T1.ID) and if i just specify date2 on the join then rowcount method takes 35 seconds for the 1000th page and the cursor method takes 3 minutes !! But the first page in rowcount method takes 0s and the cursor method takes 3 minutes !! What is going on. Is there any good method at all to do paging and is 35/50 seconds acceptable ??

Refer this
http://www.aspfaq.com/show.asp?id=2120 Madhivanan Failing to plan is Planning to fail
]]>