Saturday, April 26, 2014

Optimizing Pagination with MemoryCache

In web development pagination is fairly straight forward when using out of the box components. When building custom components on expensive queries, paging is not quick to implement.

The query I needed to implement paging against was an expensive query.  After fighting and fighting, I thought why not slam the whole thing in MemCached and page against the query.  Felt kinda weird about it.  After all this would have been a major fail 10 years ago.  Then listened to Rob Conery on DotNetRocks.  In Robs new project Biggy, he stuffs the whole database in memory using ICollectionOf<T>.  Feeling validated, started down the memory path.

I was not quite ready to use Biggy, but MemCached is appropriate.  MemCached was not setup.  Then found the MemoryCache class in .NET.  There is more than one version.  One for web and one for console apps.  Nice! I mainly wanted to use MemoryCache for the expiration policies.

The performance is significantly faster than the expensive sql queries.  Creating pagination across the collection is very easy using linq.

It has been so easy to code against, I've created a few filters; light search, return only certain fields, or exclude certain fields.

Disclaimer: There is a very good reason why I'm not using an ORM or a document database that does not have this baked in.