Memcached/Microsoft Velocity Performance Question

Question

Just a random query regarding Microsoft Velocity.

Scenario: Say I want ALL Orders from my database. In SQL, this is fine, I can do SELECT OrderId,TotalCost... from Orders. This is one round trip to my database, and everyone is happy.

Now, if I'm using Memcached or (as I'm using now) Microsoft Velocity (CTP3), there is no easy way to do this. The two options I see are (in pseudo code)

FOR EACH ORDER
     Order = cache.TryGet(OrderId)
     if Order is null
            Order = db.Get(OrderId)
END FOR EACH

which would be LOADS of roundtrips.

Also, consider I want to get orders by Customer

SQL: Select OrderId....TotalCost from Orders where CustomerId = MyCustomerId

One round trip, everyone is happy.

Using a cached solution there are two solutions I see really:

Solution 1:

DOES CustomerOrderIdsForCustomerId EXIST
     NO
           POPULATE CustomerOrderIdsForCustomerId FROM DATABASE
     YES
           FOR EACH OrderId IN CustomerOrdersForCustomerId
                  cache.TryGet(OrderId)
                  IF Order IS NULL
                        Order = db.Get(OrderId)
           END FOR EACH

Solution 2 is to hold a serialized list of all the customer orders in it's own cache object. Reduces round trips, but just seems lame.

Can someone shed light on this situation please?

Answer 1

Keep in mind that a cache is not supposed to be the permanent store for any data (orders in your case). In this case the cache can help in removing some of the load from your DB server, but something has to load the orders in the cache before you can retrieve them. With that being said, here's a couple of options to consider if you are using velocity that avoid having to loop through a collection. However, you will always have to figure out a way to deal with data that is not in the cache.

Option 1: Use Regions

You can create a region and get all the objects from that region with one call. In your scenario, you could create an Orders region where you can store all the orders and then use the GetObjectsInRegion method to get all the orders in the cache. Note however that this brings back all the orders in the cache... which might or might not have all the orders that you have in the database.

Option 2: Use Regions And Tags

Velocity lets you tag objects that you put in the cache regions and then retrieve them using those tags. So, in your scenario you could tag the order objects with an "order" tag and then use the GetObjectsByTag method to retrieve them. Since you can use multiple tags, you could also tag them with their customer id tag and then pull them out that way.

These 2 options come with some caveats, so be sure to read up on the documentation: Velocity Tag BasedMethods

Answer 2

Just because you have a cache doesn't mean you have to use it for every query! In this instance as you've already identified, it's not really helping you and I'd probably go straight through to the database for this sort of thing.
It depends a bit on your application though - if you think customers are regularly going to be looking at their order history, or you have some function that's analysing orders to see what products are hot, then you might want to use some caching to keep load off your SQL server. In that case, I'd probably go with holding in the cache either a DataTable of the orders, or a collection of Orders and query it with LINQ to show the orders for a customer.