I’ve uploaded my slide deck on AppFabric Cache and SQL Server 2008 as well as my demo-code that I used in my session.
I had a really great time at SQLRally and hope to be a part of more to come. I’ve got my eyes on SQLRally Nordic, or as I’d like to call the Cold Edition. I’m guessing that shorts and t-shirts would not be appropriate attire during the month of November in Sweden.
I will be presenting my talk on Windows AppFabric Cache and SQL Server 2008 on Friday, May 13th at SQLRally.This will be a 200-level session where I will go over some of the basics of caching technologies (NoSQL), who uses them, and why. I will then go into describing Windows AppFabric Cache and how its used to help with performance in concert with SQL Server. I will have a few basic demo’s that show how easy it is to integrate into an existing applications as well as its speed. My goal is to have you after the session thinking about ways to integrate this into your environment to solve some painful issues.
I’ve been notified that my Chalk-Talk session, AppFabric Cache and SQL Server 2008, has been selected for this years PASS summit. Here is the abstract that I submitted:
With all the hype about NoSQL going around in the SQL community its about time we looked at a Microsoft’s distributed cache-aside solution, AppFabric Cache. In this session we will briefly go over what AppFabric Cache is, what it can and cannot do, as well as some of the compelling features that make it a complimentary product to SQL Server. We will go over some real-world examples on how AppFabric Cache can co-exist with SQL Server and increase performance. In addition, we will compare the speed of the AppFabric Cache in comparison to SQL Server 2008 in both queries and inserts/updates. This chalk-talk will go more in-depth into some of the information presented in the SQLCAT Optimizing application workload & performance by leveraging AppFabric Cache along with SQL Server session.
Looks like I’ll be practicing nothing but AppFabric from now until the conference!
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) | ||||
| 1 | 10 | 50 | 100 | |
| Cache | .96 | 1.65 | 3.68 | 5.55 |
| SQL DB | 6.10 | 7.68 | 10.7 | 13.25 |
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) | ||||
| 1 | 10 | 25 | 50 | |
| Cache | 4.37 | 44.8 | 109.8 | 222.4 |
| SQL DB | 8.38 | 14.6 | 25.4 | 43.7 |
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.
Surfin’ the intertubez yesterday I ran across a NoSQL article that Brent Ozar (@BrentO) wrote last month which goes in much greater depth about NoSQL, and its relation to database administrators. I suggest that anyone looking for a brief primer on the subject, head on over.