I have created a windows application that uses a MS SQL 2008 database server.
There are two types of the application:
1) Client Application
2) Host Application
The client application is able to send commands to the database that the host application polls and performs the command,
e.g. restart host server, restart host appliation, host check for application updates, host show debugs etc.
Once the host application picks up the command from the database the command is removed. The client application has a command task list that shows the outstanding tasks. Currently to keep this task list up-to-date (e.g. to remove tasks that have been completed) I have to query/poll the database every 3 seconds to get a refreshed dataset for the client application.
Is there a better way to poll the database, I am not sure querying the databse every 3 seconds is a good idea even if the query is very small.
Is there a way for the database to tell the application to refresh if a table is updated?
Thanks, Greg
Best Answer
Polling might be an appropriate design for what you're doing here. It lets you control and tell the user exactly how fresh/stale the state is though 3 seconds is probably too frequent. 15 seconds seems to be a common threshold used by a lot of commercial applications and with Microsoft's tools (E.g. performance monitor). Some of SQL Server's system threads waked up every 15 seconds also.
That said, you can consider using query notifications (https://technet.microsoft.com/en-us/library/ms175110(v=sql.100).aspx) if you want a way to notify the app when the data you care about has changed. Essentially, you tell SQL Server the query you want to track and when the results for that query changes, your app gets a message. It is a bit of work to implement but the concept and coding are relatively simple.