Once again, thanks for all the awesome replies. Let me add a few things: Most users have notifications pending, they just choose not to view them, so the option of storing if a user has notifications won't help us much as most users have them and we need to display them.
We are very heavy putting logic in T-SQL in terms of stored procedures and triggers. The problem, being, when notifications are created, they are actually created by a trigger. Also we mark notifications are viewed with a stored procedure. How then can a T-SQL trigger or stored procedure update memcache? Is that possible? It would be AMAZING if we could create and update memcache directly in T-SQL triggers and stored procedures. -- Justin On Mar 3, 7:16 pm, Mark Atwood <fallenpega...@gmail.com> wrote: > 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.
