Where to paginate / filter? In the database or in the code?

Question

I need to write code for the following method:

public IEnumerable<Product> GetProducts(int pageNumber, int pageSize, string sortKey, string sortDirection, string locale, string filterKey, string filterValue) 

The method will be used by the web interface and should support pagination, sorting and filtering. The database (SQL Server 2008) has ~ 250,000 products. My question is this: where can I implement the pagination, sorting and filtering logic? Should I do this in a T-SQL stored procedure or in C # code?

I think it’s better if I do this in T-SQL, but I get a very complex query. On the other hand, doing this in C # implies that I need to download the entire list of products, which is also bad ...

Any idea what is the best option here? Am I missing an option?

Answer 1

You will definitely want the database to do this for you. Moving ~ 250 thousand records from the database for each request will be a huge overhead. If you use LINQ-to-SQL, Skip and Take will do this ( here is an example ), but I don’t know exactly how effective they are.

Answer 2

I think another option (and potentionaly best) is to use some higher level structure that protects you from the complexity of writing queries. EntityFramework, NHibernate, and LINQ (toSQL) help you a lot. This database is usually the best for your business.

Answer 3

Today I myself implement pagination for my site. I did with the stored procedure, although I use Entity-Framework. I found that running a complex query is better than fetching all the records and paging with the code. So do it with a stored procedure. And I see your line of code that you attached, I implemented exactly the same.

Answer 4

I will definitely do this in a stored procedure, something like:

 SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY Quantity) AS row, * FROM Products ) AS a WHERE row BETWEEN 11 AND 20 

If you use linq, then the Take and Skip methods will take care of this for you.

Answer 5

Definitely in the DB for preference, if at all possible.

Sometimes you can mix things up a bit, for example, if you have results returned from a database function (and not in a stored procedure, functions can be part of larger queries in ways that cannot be stored in procedures), then you may have another function order and paginate or possibly Linq2SQL or a similar call to the result page from the specified function, creating the correct SQL if necessary.

If you can at least get an order in the database and, as a rule, want only the first few pages (which often happens in real use), then you can at least have reasonable performance for these cases, since there are enough rows to skip and then take the required lines, you need to load from db. Of course, you should still verify that performance is reasonable on those rare occasions when someone is really looking for page 1.2312!

However, there is only a compromise for cases where paging is very difficult, as a rule, is always a page in the database, if for some reason it is either extremely difficult or the total number of rows is guaranteed to be low.