if the lookup are always done using the same criteria (you mentioned userid = @userid and viewed = @state), then just put an index on userid&viewed. i assume that your server has a lots of memory, so sql server will cache most of the data, and you'll have a very good (measured in msec) response time.
if the the rowse are "big" as in contain more than just some scalar fields here is a "trick" you can to: - add a field (_rowversion) to the table with the data type "timestamp". this is a database unique counter, and updated automatically by sql server. - you'll still need the indexes i mentioned previously. to improve the performance include the above rowversion field in your index. CREATE INDEX foo ON BAR (userid, viewed) INCLUDE (_rowversion). this will persis the field value in the index, sparing you a keyy lookup when querying only this field using a criteria whihc the index covers. - in your ORM layer (or somewhere near, but not in the DB server) create a helper class whihc will retrieve the states from the db using the following logic: 1) determine the use...@state&etc parameters 2) generate a unique cache key from these parameters (for example key = "version-" + userid + "-" + state;) 3) retrieve the cached row version from memcached using the above key 4) check if the DB row has the same version. this will be very fast (<10msec in my environment) 5) if the version is the same, generate a cachekey again, but this time for the actual data (key = "data-" + userid + "-" + state;) 6) get it from memcached 7) if found, process it, show it to the user, etc, then return 8) if either 3 or 7 fails, retrieve the data from the database, cacahe the version then the data using the above cache keys a. On Mar 4, 2010, at 5:25 AM, JustinSD wrote: > 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
