There are many great articles on the internet comparing MS SQL server and AppFabric, and all of them show how AppFabric has better performance over SQL server. It is completely understandable that with complex queries, AppFabric would out perform SQL server. But in an environment where you have a huge number of very small objects to be cached and looked up, AppFabric shows very weak results.
My test scenario consist of one machine, Windows 7 x64 with 8 GB RAM and 8 cores, running both SQL server and AppFabric Cache. (Note: when testing each software, no other load is on the machine, so SQL server does not hinder AppFabric while testing)
Same data set which exists in SQL server is preloaded to AppFabric cache. It consists of 586,000 SHA256 values and a corresponding integer.
Here is the result:
585,000 items in the database/AppFabric Cache
60,000 total lookups, 20,000 hits, 40,000 misses
Total lookup time:
* Direct SQL query: 8 seconds
* AppFabric Cache: 28 seconds
* PermaCache: 6 seconds
* SQL Server: 315 MB
* AppFabric Cache: 1,291 MB
* PermaCache: 120 MB
I ended up developing a high performance Cache Server called PermaCache. Above numbers now include test results with the same data on the exact same machine.