Currently I am trying to load a large dataset to a gridview which of course is timing out because the amount of rows being returned is so large. Instead of trying to page in the gridview via memory, how might I page on the server to prevent the timeout that occurs from loading the dataset all at once? I have tried using the method described here, but the query still seems to hang. It may also be worth note that I have run my query against an execution plan and it has not suggested anything to consider. Please see my current implementation below:
with result_set as (select distinct row_number() over(order by a.date desc) as [row_number], a.date, vw.Name, a.accountNum,
a.action, z.loc, b.name, a.col
from tbl1 as a
inner join tbl2 as b
on b.id= ch.id
inner join tbl3 as z
on a.zip= z.zip
inner join tbl4 as vw
on a.accountNum= vw.accountNum collate database_default
where a.col is not null
) select * from result_set where [row_number] between 1 and 20
Am I the victim of poor indexing on my part? Or is something else i missed? Please share your thoughts.
