What you have here is you are polling an event queue every minute, said event being a notification. Maybe you would be better served actually using an event queuing system. There are a number of them in the open source world that fit this use case pretty well, including RabbitMQ and Resque. I don't know enough about the msft and .net ecologies to know what exists in this space there.
That said, there are a couple of things you could do using memcached. For example, if most of the time that query returns an empty set, you could put into memcached a key called something like "has_notification/$USER_ID" whenever a you insert something into the notification table for that user, and then check the memcached for that key before doing the select. That way you are not hammering your database looking for notifications that don't exist. And/or you could actually put the notification type&text into the memcached, under the keyname "notification/$USER_ID/$epochseconds", and then do a multiget on each of the past 60 possible time values. If you have more than one notification per second, this might not work, but it may inspire a similar solution. For example, if notifications have some sort of short unique ID, you could have a single key per user with a key name like "notification/$USER_ID" and do a value append of each notification to it as it happens, and then also have a key with a name like "notification/$USER_ID/$NOTE_ID". Then retrieve the master entry, which will give you a set of notification ids, and the then go back and mget each notification. With some careful use of CAS operations and automatic expiration, this can work pretty well. But the meta answer is that memcached is not a magic "go faster" button, you will need to start making real changes to your application to use it. (And anyone anywhere who is trying to sell you a magic "go faster" button is lying to you.) On Wed, Mar 3, 2010 at 5:35 PM, JustinSD <jkel...@fieldtechnologies.com>wrote: > 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" <wcro...@alum.mit.edu> 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: memcached@googlegroups.com [mailto:memcac...@googlegroups.com] 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. >
