In my testing with AppFabric Cache I’ve been comparing its performance against SQL Server 2008 R2. In just about all use cases, AppFabric Cache is kicking SQL’s butt, except for one, in what I call the “BulkPut”.
In my testing scenario, I receive messages that I have to process which contain 1 to 100 items. There are two types of messages. One message contains all updates, and the other contains all queries. The schema of the database is similar to what gets put into the AppFabric Cache, in that I simply have a key and the object, which in this case is the serialized portion of the message. When I perform the updates in AppFabric Cache, I use the Put command, and when doing the queries, I use the BulkGet. In SQL Server 2008 R2, for both updates and queries I use a stored procedure which accepts a Table Valued Parameter (TVP). This allows me to send the 1 to 100 items in one shot to the stored procedure.
First, lets see the numbers for the queries:
|Items (time in ms)|
As you can see, both sets of data are fairly linear, and you can clearly see the cost of making the connection to SQL, translating the SQL request to get the key and its object, then returning it. Overall, AppFabric cache is 3 to 6 times faster in retrieving the same data.
Now, if we look at the numbers for the updates:
|Items (time in ms)|
Now we see that being able to supply the items in bulk to the TVP in SQL Server allows us to beat out the AppFabric Cache which has to do the Put command serially, or as my SQL folks out there would say RBAR (Row by agonizing row). This is why I would love to see a BulkPut command in AppFabric so I can jsut send it a bunch of changes that occur in the same region, similar to the BulkGet request.
Now I’m sure some of you could code circles around me and just use multi-threading or the parallelism in .Net 4.0 to make the updates in AppFabric Cache just as good if not better than the results I get out of the database, but that’s not the point of this exercise. I just wanted to use the best native way I knew how to get the data in and out of the cache and the database in the most efficient, single threaded way I knew possible. Hopefully, something like a “BulkPut” will be put into AppFabric Cache here sometime in the future.