0

Right now, I have a program that works like this. An old legacy system puts a row into a database. That row is status 0. I have a Windows Console application in .Net that checks the DB ever 5 seconds, and sends a printing command to a printer.

This architecture is really chewing up a ton of resources on the computer where the console app runs. Essentially, there are a TON of empty commands that get run. Only once in a while is a printing command put into the DB. However, as soon as the DB gets the printing command the program needs to process it ASAP.

Any suggestions on how to make this work better? I think an on-demand thing might be better - IE the legacy system sending some kind of signal right to my program. Not sure how to accomplish this.

Any help would be AWESOME.

MPelletier
  • 16,256
  • 15
  • 86
  • 137
Jesse Rallo
  • 163
  • 1
  • 2
  • 9
  • What kind of DB is that? SQL Server has some options, don't know about others; – Kris Vandermotten Oct 22 '13 at 14:08
  • If possible maybe you can move the processing to the database and have a insert trigger run the printing process. – jpw Oct 22 '13 at 14:10
  • 2
    It isn't exactly efficient, but it seems surprising that checking for a record once every 5 seconds is using up any noticeable amount of resources. Are you sure the "chew" isn't occurring elsewhere? – Khan Oct 22 '13 at 14:11
  • You can use a trigger on the table to start your console application. – Peter Oct 22 '13 at 14:11
  • Can you guys explain the trigger functionality more? I have never used them. yes, its a SQL Server 2005 installation. I was also thinking if the legacy system could somehow send a signal right to the program, that would help. then the program just sits there, dormant, until it gets the signal and then wakes up and processes the command. Does that make sense? – Jesse Rallo Oct 22 '13 at 14:15
  • @JesseRallo "...send a signal right to the program [that] just sits there, dormant, until it gets the signal and then wakes up and processes the command" -> That's called a (web) service. – Kris Vandermotten Oct 22 '13 at 14:20
  • I could easily build a web service, the problem here is that the computer it is coming from is a legacy system. It has limited ways to connect to another service. – Jesse Rallo Oct 22 '13 at 14:37
  • What programming language is the legacy system? – UnhandledExcepSean Oct 22 '13 at 15:00
  • its an old programming language called Universe / Basic. Its built on an old Red Hat Linux core (9.0) – Jesse Rallo Oct 22 '13 at 15:05
  • How long does the polling command take? sounds like there is an index missing. Very easy to fix. – usr Oct 22 '13 at 15:37
  • Also, is the console polling and then closing? Or does it stay open with a connection to the DB? – UnhandledExcepSean Oct 22 '13 at 15:45
  • What about using a CLR method on the SQL Server to send a message to a web service? – UnhandledExcepSean Oct 22 '13 at 15:55
  • JPW - can you give me more info on the trigger concept? How would I send a message to the program using a trigger? – Jesse Rallo Oct 22 '13 at 19:48

1 Answers1

0

Use message queues?

Push notifications from SQL over MSMQ(?) to the app. Create and send a message from a trigger or if applicable from an stored procedure. Messages can be as simple as a string. An example can be found here

Lightweight and will do less harm to performance then direct communication with the app. Also removes the need from the app to poll for changes.

Next improvement could be to alter the console app to run as service, so it doesn't need a UI and can be expected (within bounds) to run always.

Further improvement could be to split the console app (or service) and distribute the performance heavy parts to specialized instances on different boxes. Just reroute the messages for those tasks, transparent from the SQL instance.

No need to change the legacy app and it allows to gradually grow a more lously coupled scalable solution.

lboshuizen
  • 2,746
  • 17
  • 20
  • ok so I did some research into notifications. I guess I don't know how to do this, and I'm confused. I read through a bunch of tech net articles but I'm still confused. So the SQL server would send my program a notification? this sounds like its exactly what I need but I'm unsure what I have to do to my SQL server to get it to start sending notifications. – Jesse Rallo Oct 22 '13 at 19:33
  • couldnt i do this with a trigger of some sort? – Jesse Rallo Oct 22 '13 at 19:39
  • SQL server send a message just [like] an email, it knows the address but nothing about the reader. The reader (your program) gets the message and can do something useful with it based on the message. Look at http://msdn.microsoft.com/en-us/library/windows/desktop/ms706253(v=vs.85).aspx – lboshuizen Oct 23 '13 at 10:16
  • Updated the answer with a link on how you could do it – lboshuizen Oct 23 '13 at 10:24
  • iboshuizen - i did some research on this but am still having trouble with an implementation. Is there a walkthrough on implementing this somewhere? I am using Visual Studio 2010 on the programming end, with SQL Server 2005 as the server. – Jesse Rallo Oct 23 '13 at 13:28
  • In my answer is a link to http://support.microsoft.com/kb/555070 It uses a stored procedure but applies to triggers too – lboshuizen Oct 23 '13 at 15:33