Database Scheduled manipulation

Question

I've wrote a Restaurant Management application. I have a Database based on SQL Server 2005 which has one table named OrdersItems. Every 5 minutes I want to read all rows of this table and based on a specific criteria Update some fields.

I don't want to do that in my main application and I prefer to have an Alternative engine to perform this.

Which method is the best method to perform such task ? Also note that this Table (OrdersItems) is under process every time because main application must be always running and get new Restaurant Orders.

Are you using Express edition or one of the other editions? – Martin Smith Sep 13 '11 at 16:22 — Martin Smith, Sep 13 '11 at 16:22

score 3 · Answer 1 · edited May 23 '17 at 10:26

3

You can create a SQL Server Agent job that does the update every five minutes.

If you are using SQL Server Express edition, you can't use SQL Server Agent because it's only included in the "bigger" versions of SQL Server.
In this case, you can create your jobs manually using batch files and Windows Task Scheduler.

edited May 23 '17 at 10:26

Community

1
1

answered Sep 13 '11 at 16:22

Christian Specht

35,843
15
128
182

score 2 · Answer 2 · edited May 23 '17 at 12:06

2

I definitely agree with Christian and dougajmcdonald's points about using SQL Task/ Maintenance. However since you included c# in your tags an alternative is to create a Windows Service.

The benefits of this approach

Can run on a machine that doesn't have the SQL Server Agent installed (including express editions)
Can be run outside the context of a user login.
Has standard stop start pause continue mechanism that's well understood.
If the service itself fails will likely result in an event log

This answer contains a template for a windows service that periodically gets data and executes. You may simply want to change the DoStuff method to execute a stored procedure

edited May 23 '17 at 12:06

Community

1
1

answered Sep 13 '11 at 16:34

Conrad Frix

51,984
12
96
155

Thank you , I'm interested in this method, But I want to know how much this method reduces database performance? as I mentioned before , this Table is always under process. – Shahrokh Sep 14 '11 at 09:27
In terms of DB performance I don't think it matters which technology you use (Dialog timer, SQL Agent, Window service, cron job). You'll need to optimize the Update statement regardless of which one you use. – Conrad Frix Sep 14 '11 at 14:26

score 2 · Answer 3 · answered Sep 13 '11 at 16:39

Create a dialog timer and let it activate a stored procedure. This has the advantage of being fully contained inside the database (no external process), it does not require SQL Agent (runs on Express) and is completely crash resilient at the point it will survive database detach/attach and backup/restore operations (the scheduled job will run after recovery on the new restored database).

score 1 · Answer 4 · answered Sep 13 '11 at 16:23

I would expect a SQL Task / Maintenance plan would be the best for this.

You can set them up for whatever interval you want, specifying a SQL statement, maintenance task etc you want to run.

You can also setup alerts etc if you want to know when it fails for example.

score 0 · Answer 5 · answered Sep 13 '11 at 16:26

0

Deploy a cron job on a server with access to the database which is started every 5 minutes and processes your data, using transactions. I see one problem there: If the amount of data to be processed is large, it could quite work more than five minutes.

answered Sep 13 '11 at 16:26

MauganRa

494
6
8

3

cron jobs aren't exactly common in a C#\SQL Server solution – Conrad Frix Sep 13 '11 at 16:35

Database Scheduled manipulation

5 Answers5