Auto refresh data in a .NET application using firebird RDBMS

Question

Hi,

I’m developing a .NET application with an auto refresh feature. Every table in my database has a MODIFIED_DATE column that gets updated after each update or insert. At the moment the auto refresh feature will apply to only one table, however it may change in the future. I’m currently considering the following possible solutions:

1. Client application runs a query on a timer e.g. every 10 sec and brings back all rows that have MODIFIED_DATE greater than last modified date stored in my application.

2. Keep a dedicated, open connection on each client computer that listens to firebird events and then runs a query once an event has been fired. The event will obviously by fired by the database after each update/insert using POST_EVENT command.

3. Develop a service that will sit on the server listening to the firebird database events and then use .NET remoting to notify client applications.

Could anyone advise me which of the above solutions is better in terms of performance and scalability?

I want the solution to have the minimum impact possible on the database workload.

Answer 1

Actually one of the nice features of Firebird is the ability to write user defined functions (UDF's) you can call the UDF directly from a trigger and the UDF could in turn notify your app (using standard interprocess communication) that an update has occured, this then becomes a notification model rather than a polling model and much more scaleable.

Caveat: the UDF needs to be a native dll (I believe) C++ or Delphi, there would most likely be quite a bit of sample code out there in Delphi.

Answer 2

1. This solution is the worst in terms of scalability. Most of the time clients will query for changes and get nothing in return. Big waste. Not to mention the 10 second delay for the changes to appear in the clients.
2. I've done this in the past and had some scalability problems as well. The thing is, when and event is fired, every client will query the server at once. Not very good. You may want to create some scheme to prevent this.
3. Basically the same as 2, only with a lot more work involved. ;)

Answer 3

Just a slight modification to answer 2:

1. You can create a UDF in any language that can created a shared library (with the sole exception of Java)
2. If you're running Netware, you can't call a UDF from Firebird