Justin: You really have to have just the right mix of static data in order to make this thing work for you. Some people have been able to come up with architectures that save the queries with an MD5 Hash for example, and then look for similar hashes -- and take the results. That may or may not work for you, depending on the amount of time that you go after a particular query -- basically, you have to do the same query over and over again for the results to work in your favor. However, if everytime you do the query, you could potentially be bringing in new data, then you would have to set the lifetime of the query relatively short.
For example, in your database, you say you have geo-coodinates and addresses. If you are doing reverse go-code lookups, like I was doing in a prior job, then this is a perfect way to use memcached, since most reverse geo-code lookups have relatively poor performance. For example, I was doing less than 4 reverse geo-codes per second on a big server with a Web Service. Once I memcached-enabled the Web Service, I was able to achieve 4-5 times the throughput, because I was able to cache for very long periods of time the addresses associated with latitude and longitude. My application was school bus tracking -- so the same stops occurred at the same lat/long day after day -- there was no need to keep reverse geo-coding from scratch every single day. Of course, the trick was a good algorithm, for which I used Jim Gray's Hierarchical Triangular Matrix method of lat/long encoding -- a real work of genius -- it worked like a charm... Maybe a better solution for you is the new StreamInsight Complex Event Processing Software in SQL Server 2008 R2 -- see http://www.microsoft.com/sqlserver/2008/en/us/R2-complex-event.aspx . Maybe what you want to do is act on the data as it comes in -- sending alerts and messages to your users as the data flows -- that in turn could reduce your query overhead. It would be interesting to hear more about what you are trying to accomplish -- there just might be a way to save you from impending doom with this application. Walt Crosby VP of Development Interval Data Systems, Inc. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of JustinSD Sent: Tuesday, March 02, 2010 2:44 AM To: memcached Subject: How To Use Memcache With SQL 2008 So currently have a decent sized web application running Windows IIS, .Net C#, and using SQL Server 2008. Basically we have approximately 150,000 records inserted a day, and our users then search and filter that data. Currently the table holding the data is just about 10 million rows. Our database is becoming a huge bottleneck, and currently we just keeping throwing memory and CPU at the problem, but I am wondering if caching is going to help us. I am unclear though how storing key value pairs translates into fetching complicated results from memory. For example we have queries which return back datetime, event_id, latitude, longitude, address, city, state, how does that all fit into a key value pair? Are u storing objects? How would this work for us? Our content inst very static, i.e. rows are inserted constantly, how would that work? So, just trying to get my head around it. Thanks greatly.
