What is the best way from performance point of view while giving away the query results through HTTP?

Question

I need to give away results of the query in response to a following URL:

http://foo.com/search?from=20&perpage=10

What is the best practice from performance point of view?

1. Run the query every time with WHERE row > N and row <= K

2. Run query once and keep all the result for certain amount of time

3. What else could be done?

Answer 1

If your query will produce a very small amount of data you can consider to cache the results. But think about this:

• High performance pagination isn't trivial. It can be done database-side with better performance (if you're using Microsoft SQL Server 2012 it has its own extensions for this).
• Pagination is done by users and they need time to read data and click the "Next page" button. Performance hit of a new query won't be big and the database will handle its own cache better than what we can do.
• Cache a large amount of data is useless, memory consuming and less efficient than a new query.

Then my answer is run the query every time. Your database will handle that better than you and it'll scale with your data (otherwise what may work for 500 record will be broken when your system will have 1000000 records).

To summarize: database engines are more efficient than us to cache data and they scale better, (usually) a good index is all they need to perform well.