How to implement synchronized Memcached with database

Question

AFAIK, Memcached does not support synchronization with database (at least SQL Server and Oracle). We are planning to use Memcached (it is free) with our OLTP database.

In some business processes we do some heavy validations which requires lot of data from database, we can not keep static copy of these data as we don't know whether the data has been modified so we fetch the data every time which slows the process down.

One possible solution could be

1. Write triggers on database to create/update prefixed-postfixed (table-PK1-PK2-PK3-column) files on change of records
2. Monitor this change of file using FileSystemWatcher and expire the key (table-PK1-PK2-PK3-column) to get updated data

Problem: There would be around 100,000 users using any combination of data for 10 hours. So we will end up having a lot of files e.g. categ1-subcateg5-subcateg-78-data100, categ1-subcateg5-subcateg-78-data250, categ2-subcateg5-subcateg-78-data100, categ1-subcateg5-subcateg-33-data100, etc.

I am expecting 5 million files at least. Now it looks a pathetic solution :(

Other possibilities are

1. call a web service asynchronously from the trigger passing the key to be expired
2. call an exe from trigger without waiting it to finish and then this exe would expire the key. (I have got some success with this approach on SQL Server using xp_cmdsell to call an exe, calling an exe from oracle's trigger looks a bit difficult)

Still sounds pathetic, isn't it?

Any intelligent suggestions please

Answer 1

It's not clear (to me) if the use of Memcached is mandatory or not. I would personally avoid it and use instead SqlDependency and OracleDependency. The two both allow to pass a db command and get notified when the data that the command would return changes.

If Memcached is mandatory you can still use this two classes to trigger the invalidation.

Answer 2

MS SQL Server has "Change Tracking" features that maybe be of use to you. You enable the database for change tracking and configure which tables you wish to track. SQL Server then creates change records on every update, insert, delete on a table and then lets you query for changes to records that have been made since the last time you checked. This is very useful for syncing changes and is more efficient than using triggers. It's also easier to manage than making your own tracking tables. This has been a feature since SQL Server 2005.

How to: Use SQL Server Change Tracking

Change tracking only captures the primary keys of the tables and let's you query which fields might have been modified. Then you can query the tables join on those keys to get the current data. If you want it to capture the data also you can use Change Capture, but it requires more overhead and at least SQL Server 2008 enterprise edition.

Change Data Capture

I have no experience with Oracle, but i believe it may also have some tracking functionality as well. This article might get you started:

20 Using Oracle Streams to Record Table Changes