Is polling the only way for updating app's data from a database?

Question

An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the database?

I work with a MS SQL Server 2008 (and .NET applications + Entity Framework), but I'd like to get knowing about other types of databases as well.

Answer 1

Service Broker for SQL Server 2005+ can do this.

Sorry, I'm not sure of other RDBMS

Answer 2

In Oracle you can use the built in DBMS_ALERT package to facilitate this.

DBMS_ALERT supports asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can notify itself whenever values of interest in the database are changed.

Suppose a graphics tool is displaying a graph of some data from a database table. The graphics tool can, after reading and graphing the data, wait on a database alert (WAITONE) covering the data just read. The tool automatically wakes up when the data is changed by any other user. All that is required is that a trigger be placed on the database table, which performs a signal (SIGNAL) whenever the trigger is fired.

Answer 3

Certain database vendors also provide integrated message buses that your app can simply subscribe to:

• Oracle Advanced Queueing
• IBM DB2 with MQseries (now called WebSphere MQ)
• Sybase RTMS

An alternative would be to route the data into the database in the first place via a message bus like Tibco/RV and simply "branch" it, on stream going into the DB and one going to your application, or use a caching layer like Coherence between your app and the DB.

Answer 4

Another Oracle solution: We've developed applications using the dotnet framework from Microsoft that take advantage of Database Change Notification feature of Oracle in conjunction with ODP.Net (the Oracle data provider for dotnet). Using this, the database actually notifies the dotnet application when new data has arrived allowing us to avoid constant polling. The link I reference above is an Oracle tutorial for doing just that. Hope this helps you out.

Don't know about any other RDBMS's.

Answer 5

LISTEN / NOTIFY for PostgreSQL

http://www.postgresql.org/docs/9.0/static/sql-notify.html

Answer 6

For one of our applications (accessed trough Chrome and ONLY Chrome), we're using MySQL with sys_exec UDF. Basically, why Chrome - due to WebSocket support.

Once a critical update / insert / delete occurs, an external program is invoked trough newly added sys_exec functionality embedded in certain triggers. At that point, we've got all we need to relay the message to every connected client without any need for polling or multiple queries as everything happens real-time.