how to: retrieve values from database only when they are updated

Question

I have a scenario where I want to display a bunch of data on a web page.

The data gets populated into database by some process (which I have no control over) only at (7:00 AM) a specific time in the morning or manually starting the process. (P.S The same data is also being used in some other place on the web page in a different form, but from a different channel.)

I was thinking to put in some logic on page that

1. retrieve data into a xml file and read data from the xml file (to avoid trips to database)
2. retrieve data only if it is 7:10 am in the morning. But if someone manually starts the process database is updated, but my web page won't be updated till 7:10 AM. Even 1 second out of sync of data is not acceptable.

What is the best way to do this? Any smart solutions? I want to avoid round trips to database for same data again and again.

SQL server 2000, ASP.

Answer 1

You'll need some sort of polling solutions.

Just on top of my head, I'll do something like this.

1. Setup a trigger on the table (table A) that is being populated to update a certain control flag on another table upon inserts and udpates, lets call this the control table.

2. Setup your website to poll the control table say, every 1 second between 7am and 7:10am. If the control table indicates new data has arrived in table A, go grab them and update your page, otherwise, do nothing. However, although polling the control flag every second is too much of a big deal, you have to ask yourself is it really such a big issue if the data is a couple of seconds out of sync, say poll every 10 seconds instead of every second.

Answer 2

Ok if I'm reading this right...

If it is the same data, you can reduce the load on trips to the database by indexing the appropriate fields and massaging the data into perhaps a not so structurally ideal form in order to speed up the queries.

The problem is that the only way to tell whether the data has been changed or not is to query it, so you can't really avoid polling the DB. The safest bety would be to optimize the structure and the queries to make the taxing on the server as minimal as possible.

Answer 3

Can you use a SqlCacheDependency?

And BTW, not being 1 second out of sync is not a realistic goal.