Justin: I don't see this as a good candidate for memcached using a standard SQL Server 2008. However, if you looked at this from the viewpoint of StreamInsight on SQL Server 2008 R2, you could set up a ComplexProcessing Query that would send something to the user when they get a notification -- bypassing a 60 times/hour query at all.
Unfortunately, since you always have to see if new notifications are coming in, you would always have to go to the database first -- to make memcached work for you, you need to be able to go to memcached first, and then only go to the database if what you are looking for is not in the cache -- but you are never going to know that, because you have to see if something new came in. Walt Crosby 34 Robinson Creek Rd Pembroke, MA 02359 (617) 529-1930 -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of JustinSD Sent: Wednesday, March 03, 2010 8:35 PM To: memcached Subject: Re: How To Use Memcache With SQL 2008 Hello everyone, thanks a ton for all the replies. So let me give a concrete example of something we do. I recently just ran a report in SQL 2008 and found the top query as far as total CPU time. The query basically pulls notifications for users every minute they are logged in. The query is specific to each user, so essentially something like: SELECT notification_id, notification_type, notification_description FROM notifications WHERE user_id = 23243 AND viewed = 0; So this seems like a good candidate for memcache, but do we then memcache a result for each user_id? Would we basically store the DataTable in memcache? Also wouldn't memcache needs to be refreshed when users get `new` notifications, and also when they view notifications? How is that handled? Thanks. On Mar 2, 5:45 am, "Walter Crosby" <[email protected]> wrote: > 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 -- > seehttp://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.
