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.
