Please confirm my design

[g_hickley]

Please could someone confirm my design approach:

I am designing a Windows service that processes rows in a SQL Server
2000 database table. The table has a datetime column. When the datetime
for a specific row is reached the service must perform an activity.
There may be multiple rows with the same datetime so the service will
have to assign individual threads to process each of these. I am
against the idea of the service constantly polling the database every
second as this seems a waste of resources.

This is my thinking:
1. The process that inserts the rows into the table also inserts a
message into MSMQ.
2. The Service listens to the queue and assigns a thread to each
incoming message.
3. The thread is set to sleep until the exact time.
4. As the thread awakens it checks the database to see the row still
exists before performing the required processing.

Is this the best approach? Otherwise any suggestions on a alternative
approach?

An alternative to 1. might to be use the new "SQL Service Broker" in
SQL Server 2005.
An alternative to the messaging approach might use a trigger to
activate a COM+ component on each insert/update to the table. The COM+
component could then communicate with the service to tell it to assign
another thread. Obviously in the event of a restart in the Service it
would have to collect all the table rows and assign threads to them.

 

[Marc]

Disclaimer: This is one guy's opinion...there will inevitably be 1000
ways to skin a cat
I see a couple things I would change, but all in all I think you are on
the right track. 1) I would avoid kicking off a lot of threads. there
are only so many threads on the processor and you don't want to try to
debug a detailed threading exception. Instead I would kickoff timers.
when each timer goes off then perform your check against the database
to ensure the record still exists. etc. 2) unless you are using a
distributed app, I would not overcomplicate things by using MSMQ. Try
using the Queue object instead. works just like MSMQ (FIFO) but
without the headaches. I've written a pretty simple form in 2.0 to
demonstrate. Good luck.

Marc
MCP.NET, MCAD.NET

http://www.statera.com

public partial class Form1 : Form
{
Queue<DoSomething> q = new Queue<DoSomething>();
public Form1()
{
InitializeComponent();
}
static void Main()
{
Application.Run(new Form1());
}
private void button1_Click(object sender, EventArgs e)
{
for (int i = 0; i < 5; i++)
{
DoSomething d = new DoSomething();
d.Name = "Process " + i.ToString();
d.StartTime = DateTime.Now.AddSeconds(30);
System.Threading.Thread.Sleep(2000); //for demo
purposes only so they all don't show up at once
q.Enqueue(d);
}
foreach (DoSomething d in q)
{
Timer t = new Timer();
t.Interval = (int)d.StartProcess.TotalMilliseconds;
t.Tag = d;
System.Threading.Thread.Sleep(2000); //for demo
purposes only so they all don't show up at once
t.Tick += new EventHandler(t_Tick);
t.Start();
}
}

void t_Tick(object sender, EventArgs e)
{

ListViewItem lvi = new
ListViewItem((((DoSomething)((Timer)sender).Tag).Name));
lvi.SubItems.Add(DateTime.Now.ToString());
listView1.Items.Add(lvi);
((Timer)sender).Stop();
((Timer)sender).Dispose();

}
}
public class DoSomething
{
private string name = string.Empty;
private DateTime startTime;
private TimeSpan startProcess;

public string Name
{
get { return name; }
set { name = value; }
}
public TimeSpan StartProcess
{
get { return startProcess; }
}
public DateTime StartTime
{
get { return startTime; }
set
{
startTime = value;
startProcess = startTime.Subtract(DateTime.Now);
}
}
public DoSomething()
{
}

}

 

[Richard Blewett [DevelopMentor]]

Please could someone confirm my design approach:

I am designing a Windows service that processes rows in a SQL Server
2000 database table. The table has a datetime column. When the datetime
for a specific row is reached the service must perform an activity.
There may be multiple rows with the same datetime so the service will
have to assign individual threads to process each of these. I am
against the idea of the service constantly polling the database every
second as this seems a waste of resources.

This is my thinking:
1. The process that inserts the rows into the table also inserts a
message into MSMQ.
2. The Service listens to the queue and assigns a thread to each
incoming message.
3. The thread is set to sleep until the exact time.
4. As the thread awakens it checks the database to see the row still
exists before performing the required processing.

Is this the best approach? Otherwise any suggestions on a alternative
approach?

An alternative to 1. might to be use the new "SQL Service Broker" in
SQL Server 2005.
An alternative to the messaging approach might use a trigger to
activate a COM+ component on each insert/update to the table. The COM+
component could then communicate with the service to tell it to assign
another thread. Obviously in the event of a restart in the Service it
would have to collect all the table rows and assign threads to them.

Without some idea of the projected volumes and other contraints it would be
reckless to recommend an approach. For example, the process that inserts the
record, what technology is that written in? Is the insert load predictable,
have a flat profile or peaks and quiet times? Are you using .NET 1.1 or 2.0?

You appear to have some level of control over all parts of the application,
so what are you actually trying to achieve, the requirements are unlikely to
state "one process must insert a record and another process perform
processing on it at a particular time"

What are the restrictions in terms of reliability, does it matter if some
records are not processed or must all record be processed at lease once, at
most once or exactly once?

Regards

Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk

 

[Marc]

"...it would be reckless to recommend an approach." How ridculous are
you?!? C'mon this is a news group to help people with their problems,
go out on that ledge...live a little "recklessly" and take a look over
the edge. Obviously, in order to fully understand anyone's
application, one would need to post use cases and system requirements
and unit tests etc. etc. At that point you would be screaming to not
post so much information. He asked for someone to review and make
suggestions, not to architect a flawless solution (that's his job ).
So why waste your time to tell him that you need more information.

 

[Richard Blewett [DevelopMentor]]

"...it would be reckless to recommend an approach." How ridculous are
you?!? C'mon this is a news group to help people with their problems,
go out on that ledge...live a little "recklessly" and take a look over
the edge. Obviously, in order to fully understand anyone's
application, one would need to post use cases and system requirements
and unit tests etc. etc. At that point you would be screaming to not
post so much information. He asked for someone to review and make
suggestions, not to architect a flawless solution (that's his job ).
So why waste your time to tell him that you need more information.

My point was you can recommend all you like, but without knowing more about
what he is trying to acheive, what the constraints are and what pieces are
under his control any recommendation is built purely on sand. I could argue
that BizTalk provides a solution, or as he says SSB or as you say
Systen.Collections.Queue or MSMQ or MQSeries, or Remoting. All of these
potentially might fit but which is appropriate is not really possible to
tell without some idea of the issues the solution faces (hence my
questions).

Personlly I like more information available before I judge whether a
proposed design is a good one or not. The issues I raised can fundementally
change the choices for a design.
Regards

Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk

 

[g_hickley]

Many thanks for all the replies.

Richard,

To answer your points:

The process that inserts would likely be written in asp.net but it
could be any scripting language. It would be independent of the service
that works the records in the database. The load would be variable, I
would expect peak periods and flat periods. As a rough guide, during
the peak periods there could be hundreds of requests to process in the
same minute and perhaps even 20 requests to process at the same time.

I would likely use .Net 2.0.

"one process must insert a record and another process perform
processing on it at a particular time" actually this is exactly
correct. The requests would come in from a website. HTML forms
submitted with all the details. These would then be inserted into a
database.

All records must be processed once and exactly at the time specified.

Gordon