Select query frequency for a almost real time software

Question

I'm building a ERP like software, but only for the manufacturing and performance analysis portion. We are a 100 man company, the fastest manufacturing order takes about 10 min to 2 hours.

There are 8 workstations for multiple workers, and about 10 more for the engineering (Chart and reports).

We store the time spent working on a piece in the database in minutes.

I'm not sure i'm using the best practices in my database usage.

• Currently, I select each 5 seconds to get the time update, each 5 seconds I update all the times open with an Agent Job in SQL Server. This task is not critical since we use minutes and not seconds.

• There is a job available section, and this section is important, if a job is taken it need to disappear. I want it almost real time, if someone takes a job, it disappear from the other workstation. From my understanding, I need to do a select query and check if a piece is gone. Can I set up a check each 500 millisecond without overloading the Sql server ? If there is almost 20, that means 40 select per seconds.

Is there any better way to do those tasks ?

Answer 1

You need a server component. A master. One app to rule them all.

Let that single server application be the one that knows about the database, connects to it, and deals with record changes.

On each workstation you would have a client application. Use WCF to facilitate communication between server and its clients. The server should expose methods that allow clients to make record changes, request data from the database, ask for cached statues, etc.

Having a server layer also gives you a centralized point at which to cache information that the clients need on an instantaneous basis.

As for removal of an item, for example: Client A requests that the server remove Job #1 from the list (and from the database, I presume). The server then makes the necessary database changes and informs its client immediately of the change. Alternately, if you prefer one-way communication, it would be just as easy for the server to simply maintain the change data and each client can poll periodically to find out what changed.

Answer 2

The SqlDependency Class will go a long way to solving your problem if using SqlServer. Also I'd store the start and end times and the minutes spent can be a calculated value to save querying the database all the time.

Answer 3

• In your first point. You could keep the last DateTime it was used, so instead of querying every 5 seconds, you could just query once and then update the time spent in the client in real-time (by a simple substraction with current time).

• In your second point. Querying the SQL server every 500 milliseconds is not the way to go. You should implement some layer of communication between the workstations and the sql server (e.g. a webservice) and keep some data cached there, then you could make queries to the webservice without querying the database. Of course, then you'll need to notify that service when any data is removed.

I hope this helps