querying DB in a loop continuously in java

Question

Is it advisable to query a database continuously in a loop, to get any new data which is added to specific table?

I have below a piece of code:

  while(true)
      try{
         // get connection
         // execute only "SELECT" query
       }
      catch(Exception e){}
      finally{// close connection

      }
         //Sleep 5 sec's
      }

This will cause a lot of overhead. Can your configuration handle that? — Stultuske
– Stultuske, Commented Jun 29, 2015 at 7:26
Depends upon how frequently you expect the data to change, whether you expect the data to change with uniform frequency, how frequently you need the updates, and whether any other mechanism (e.g. push notifications) is available to you, whether your DB can handle the load... — Andy Turner
– Andy Turner, Commented Jun 29, 2015 at 7:29
This is a naive approach, suitable perhaps only for simple applications with few users. A real system wouldn't unnecessarily query the database or have a latency of up to 5 seconds to view new data. — Kayaman
– Kayaman, Commented Jun 29, 2015 at 7:30
Thanks for suggestions but still i am not getting, how to do it. basically its a small application which will have 5 records only. my requirement is to detect the changed fields in 5 records, can u plz suggest how can do accordingly?? — Sagar H
– Sagar H, Commented Jun 29, 2015 at 8:55
What are you going to do when you get the data? It could help people day of this is advisable — WW.
– WW., Commented Jun 29, 2015 at 9:21

Jens Schauder · Accepted Answer · 2015-06-29 07:31:23Z

1

It is a simple approach that works in many cases. Make sure that the select statement you use doesn't put as little load as possible on the database.

The better (but more difficult to setup) variant would be either to use some mechanism to get actively informed by the database about changes. Some databases can for example can send information with some queuing mechanism, which again could be triggered using a database trigger.

answered Jun 29, 2015 at 7:31

Jens Schauder

82.6k35 gold badges199 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3134614 · Accepted Answer · 2015-06-29 07:39:26Z

0

Querying database in loop is not advisable but if you need the same you can daemonize your program.

answered Jun 29, 2015 at 7:39

user3134614

1601 gold badge2 silver badges15 bronze badges

Comments

Joop Eggen · Accepted Answer · 2015-06-29 08:00:45Z

0

If longer then 5 s a timer would be appropriate.

For a kind of staying totally up-to-date:

Triggers and cascading inserts/deletes can propagate data inside the database itself.

Otherwise before altering the database, issue messages in a message queue. This not necessarily needs to be a Message Queue (capitals) but can be any kind of queue, like a publish/subscribe mechanism or whatever.

answered Jun 29, 2015 at 8:00

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Comments

acesargl · Accepted Answer · 2015-06-29 08:13:24Z

On one hand, if your database has a low change rate then it would be better to use/implement a notification system. Many RDBMS have notification features (Oracle's Database Change Notification, Postgres' Asynchronous Notifications, ...), and if your RDBMS does not have them, it is easy to implement/emulate using triggers (if your RDBMS support them).

On the other hand, if the change rate is very high then your solution is preferable. But you need to adjust carefully the interval time and you must note: reading on intervals to detect changes has a negative collateral effect.

Using/implementing a notification system it is easy to inform the program what has been changed. (A new row X inserted on table A, a new updated row Y on table B, …).
But if you read your data on intervals, it is not easy to determine what has been changed. Then you have two options:

a) you must not only read but load/process all information every interval;

b) or you must not only read but compare database data with memory resident data to determine what has changed every interval.

Collectives™ on Stack Overflow

querying DB in a loop continuously in java

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related