server side paging with business logic filtering

i need to show total number of rows at the grid title. the grid have to deal also with large amount of records. so i decide to use grid custom paging feature. i know how to do server side paging with sql2005 ROW_NUMBER etc. but my difficulty is with the complex row base filtering done at the business logic layer. i think that doing first the complex filtering (in order to know the items count) on the large amount of records will be not efficient and maybe can cause out of memory exception.

right now this project (asp.net web app) is on prod开发者_如何学编程uction with .net framework1.1,sql2005. next version on production will be with .net framework4.0. after that we will upgrade to sql2008.

please help me to find a solution for that problem? thanks.

I would say if you are afraid of Out of Memory exceptions in production either the HW is undersized for the amount of data you have or your code is really badly wrong :)

I would have everything done is a store procedure, including filtering, paging and sorting. Once you have this sorted out in the server and you have specified the page size and pag index you need to retrieve, the stored proc simply returns the single page of records you are looking for already sorted as well and you can bind this to your UI controls.

Is this what you wanted or did I get you wrong?

if you are using .net 4.0, IQueryable is a viable option. See here for details. Basically, IQueryable delays the execution of the query so you can apply the business logic and then fetch relevant data from the underlying data store (sql server in your case). But, I would do some micro-benchmarking of the query performance before going down this route.