6

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

bjan
  • 2,000
  • 7
  • 32
  • 64
  • A few questions. Is this for Oracle or Sql Server, or must it be both? Are the table(s) involved changing rapidly or slowly? Do you have any budget at all for this? – tbone May 07 '15 at 19:16
  • @tbone It should be for both, and it should work even for any other DB if we plan to support that one as well. Changes are rapid and Real Time. No budget for now as i am proposing this solution on my own – bjan May 09 '15 at 09:08
  • 1
    Too bad you require a "generic" solution that must work with both database platforms, there are certainly things you can do in the Oracle setup that could help (result cache, using SSD, etc). Also, if this is a rapidly changing table, I'm not sure an app side cache is the best approach, as you'll constantly invalidate your cache and need to reload it anyway. – tbone May 11 '15 at 14:47
  • @tbone I found that in a given condition, fetching data from database takes ~115 milliseconds while fetching data from cache takes ~0. That is why i want to use cache with the logic that whenever any cached data is updated, it must be expired. – bjan May 11 '15 at 18:00
  • I get it, but my point is that fetching from a loaded cache is expected to be fast, its the constant syncing thats the issue. If your data is constantly changing, and you always need to refresh your cache, your overall time may be longer than not using a cache. From my experience anyway, app side caches work best on slowly changing data (I used ehcache, but this point is still valid) – tbone May 11 '15 at 19:40
  • @bjan, You can use Hazelcast aswell. It has a .NET client afaik. You can easily integrate Oracle Database Change Notification and MS SQL Change Tracking features with it. – Orcun Yucel May 14 '15 at 12:05
  • @Orchun Oracle's change tracking is supposed to remain at database side while i want my app to be notified (without using triggers), SqlDependency will slow the DB down by ~43%. I haven't tried. Am i wrong? I tried trigger based implementation of cache expiry but it caused CPU usage increased by 100% and delay in CRUD by 200%. – bjan May 14 '15 at 14:57
  • @bjan i don't understand what you mean by "Oracle's change tracking is supposed to remain at database side". You can register an SQL to Oracle and whenever the result changes for that sql, your app gets notified asap. It's java but please check this [example](https://www.chemaxon.com/forum/dbchangenotification-download8158.java) – Orcun Yucel May 14 '15 at 15:24
  • @Orchun link is broken – bjan May 14 '15 at 15:30
  • @bjan it's a link to DBNotificationChange.java file. It's downloading on my side. I've pasted the content to [here](http://pastebin.com/HTcW1nnr) – Orcun Yucel May 14 '15 at 15:33
  • @Orchun, It seems like [OracleDependency](http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleDependencyClass.htm), do you know the impact of such registration on rapidly changing OLTP? – bjan May 14 '15 at 15:41
  • @bjan OracleDependency seems to be a lightweight library built by Oracle. Actually performance depends on your transaction count and the Producer-Consumer pattern on your app. You can handle a lot of transactions may be but if your consumer is not fast enough than you need to look at performance optimizations. So, you'll have a 3 parts on your app that can lack on performance. You've to measure those 3 parts well before deep dive into coding. – Orcun Yucel May 14 '15 at 15:48
  • @Orchun i will try a pilot project – bjan May 14 '15 at 15:50

2 Answers2

3

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

Sevle
  • 3,109
  • 2
  • 19
  • 31
Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
  • It looks Change Tracking remains at the database level while i want my app should be notified about the change. – bjan May 09 '15 at 09:22
  • That is correct, it is only a means of collecting the data changes. You would have use queries to collect the changes on a timed interval. The benefit is that you don't have to check for the changes, you only have to query the exact records that changed since your last check, so it's fast. It's also robust since it's all transactional; you won't miss any changes. If you don't want your app doing the checking, you could offload that to either another service program or just run a stored procedure on a timed interval and then use some other notification method like Service Broker message queues. – Brian Pressler May 09 '15 at 17:05
  • @BrianPressler, Oracle has [Database Change Notification](http://docs.oracle.com/cd/E11882_01/java.112/e16548/dbchgnf.htm#JJDBC28815). I've used it in one of my apps with Java. – Orcun Yucel May 14 '15 at 11:58
3

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.

MatteoSp
  • 2,940
  • 5
  • 28
  • 36
  • From [this](http://stackoverflow.com/questions/14203704/using-sqldependency-vs-periodic-polling-of-a-table-performance-impact) and [this](http://stackoverflow.com/questions/22660825/net-sqldependency-with-many-notifications-vs-high-rate-polling) it looks it will not be a good solution for an OLTP database with high rate of changes. [This one](http://www.codeproject.com/Articles/144344/Query-Notification-using-SqlDependency-and-SqlCach) even states that it will slow down the DB by 43%. – bjan May 09 '15 at 09:06
  • Interesting links, but no caching solution can be the perfect solution when data changes frequently. – MatteoSp May 09 '15 at 10:03