Where to do pagination/filtering? In the database or in the code?

Question

I have to write the 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 a web UI and must support pagination, sorting and filtering. The database (SQL Server 2008) has ~250,000 products. My question is the following: where do I implement the pagination, sorting and filtering logic? Should I do it in a T-SQL stored procedure or in the C# code?

I think that it is better if I do it in T-SQL but I will end up with a very complex query. On the other hand, doing that in C# implies that I have to load the entire list of products, which is also bad...

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

score 4 · Accepted Answer · edited May 23 '17 at 12:11

4

You would definitely want to have the DB do this for you. Moving ~250K records up from the database for each request will be a huge overhead. If you are using LINQ-to-SQL, the Skip and Take methods will do this (here is an example), but I don't know exactly how efficient they are.

edited May 23 '17 at 12:11

Community

1
1

answered Mar 25 '11 at 13:20

Fredrik Mörk

155,851
29
291
343

score 1 · Answer 2 · answered Mar 25 '11 at 13:26

I think other (and potentionaly best) option is to use some higher level framework that shield you from complexity of query writing. EntityFramework, NHibernate and LINQ(toSQL) help you a lot. That said database is typically best place to do it in your case.

score 1 · Answer 3 · answered Mar 25 '11 at 13:27

today itself I implement pagination for my website. I have done with stored procedure though I am using Entity-Framework. I found that executing a complex query is better then fetching all records and doing pagination with code. So do it with stored procedure. And I see your code line, which you have attached, I have implemented in same way only.

score 1 · Answer 4 · answered Mar 25 '11 at 13:30

I would definatly do it in a stored procedure something along the lines of :

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

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

score 0 · Answer 5 · answered Mar 25 '11 at 13:38

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

Sometimes you can mix things up a bit such as if you have the results returned from a database function (not a stored procedure, functions can be parts of larger queries in ways that stored procedures cannot), then you can have another function order and paginate, or perhaps have Linq2SQL or similar call for a page of results from said function, producing the correct SQL as needed.

If you can at least get the ordering done in the database, and will usually only want the first few pages (quite often happens in real use), then you can at least have reasonable performance for those cases, as only enough rows to skip to, and then take, the wanted rows need be loaded from the db. You of course still need to test that performance is reasonable in those rare cases where someone really does look for page 1,2312!

Still, that's only a compromise for cases where paging is very difficult indeed, as a rule always page in the DB unless it's either extremely difficult for some reason, or the total number of rows is guaranteed to be low.

Where to do pagination/filtering? In the database or in the code?

5 Answers5