Howto: fetch values from database only when they are updated
I have a scenario where I want to display a bunch of data on a web page.
The data is populated into the database by some process (which I do not control) only at (7:00 AM) at a certain time in the morning or manually start the process. (PS The same data is also used elsewhere on the web page in a different form but from a different channel.)
I was thinking about adding some logic on the page that
- extracts data to XML file and reads data from xml file (to avoid trips to the database)
- fetch data only at 7:10 am. But if someone manually starts the process database, it refreshes, but my web page won't refresh until 7:10 AM. Even 1 second from data synchronization is not acceptable.
What's the best way to do this? Any smart solutions? I want to avoid repeated trips to the database for the same data over and over again.
SQL server 2000, ASP.
source to share
You will need some polls.
It's just that on my head I'll do something like this.
-
Set a trigger on the table (table A) which is populated to update a specific control flag in another table on inserts and udpates allows that control table to be called.
-
Set up your site to poll the control table, say every 1 second between 7 AM and 7:10 AM. If the checklist indicates that new data has arrived in Table A, go grab it and refresh your page, otherwise do nothing. That said, while polling the check flag every second is too much of a problem, you have to ask yourself if it really is such a big problem if the data for a couple of seconds is out of sync, say polling every 10 seconds rather than every second.
source to share
Ok if I'm reading this correctly ...
If these are the same data, you can reduce the burden of traveling to the database by indexing the appropriate fields and massaging the data, possibly in a less structurally perfect form, to speed up queries.
The problem is that the only way to tell if the data has changed or not is to query it, so you cannot avoid polling the DB. The safest option would be to optimize the structure and queries to make the least taxation on the server.
source to share