Justin: I'm not sure you want to put the database update and memcached update into a Trigger. Keep in mind that the trigger runs in the same transaction as the original insert -- and the insert will not be completed until the Trigger completes. Given that issue -- you may actually see your time climb, rather than reduce time in SQL Server.
You can have a trigger update memcached by putting the actual memcached update in a Web Service, and then write a Table-Valued Function that is called by the Trigger to send a SOAP Message to the Web Service. This is a pretty difficult item to do, and requires the wonderfully manual process of putting the XMLSerialization Dll Assembly into the CLR -- it cannot be automatically published into the database like other CLR-based code. It also requires you to have a high level of trust with the Web service. Walt Crosby VP of Development Interval Data Systems, Inc. -----Original Message----- From: memcached@googlegroups.com [mailto:memcac...@googlegroups.com] On Behalf Of JustinSD Sent: Wednesday, March 03, 2010 11:25 PM To: memcached Subject: Re: How To Use Memcache With SQL 2008 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.
