Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-09-30 : 08:10:04
|
| Adam writes "SQL Server 2000 (latest everything: SQLXML, SOAPTOOLKIT...)Windows 2000 ServerC# M$.Net platformI need to maximize performance with respect to time and scalability.My delema: I need to send data from SQL to my handwritten whiz-bang win service on another machine. It's listening on a port and it isn't important what it does. Now, I need to either push data from SQL 2K or pull data by writing another service that will poll one table for new data. I am wondering if it would be better to use a trigger in conjunction with a DTS package to push the data to my service, or poll the table every second or so. Time is money with this app and I don't like the idea of possibly sacrificing a whole second. I am also more enclined to let SQL handle the IO and threading and all that, rather than code it into some custom windows service. Any ideas?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-30 : 08:16:13
|
quote:
I need to send data from SQL to my handwritten whiz-bang win service on another machine. It's listening on a port and it isn't important what it does.
How do we know it's not some kind of hacking routine that empties online bank accounts?  Seriously, why do people always seem to want help, but make a point of posting that they're NOT supplying all of the information? "Please help me build this house, but let me tie your hands first."quote:
Time is money with this app and I don't like the idea of possibly sacrificing a whole second.
Please, how much money can you actually lose in one second?I can only suggest, with the extrememly limited info you've provided, that querying the table as needed will probably be your best bet for the time being. DTS is probably unnecessary, and I don't believe it can deliver data to a "service", it work with files, databases, etc. You can put SOAP transfers inside a DTS package, but that doesn't make a lot of sense, DTS would just add overhead. |
 |
|
|
azastawski
Starting Member
3 Posts |
Posted - 2003-10-06 : 22:03:45
|
| Point Taken, It's not a matter of choice, stinking lawyers have made an industry of writing NonDisclosure Agreements. I will try to clarify.It's a matter of instant wireless messaging. It's a health care application where time is critical. When a web visitor posts a new message, it needs to be sent with a quickness to my message server. The message server listens on a port and will use a thread pool to follow a message to it's conclusion(failed or delivered). I have given thought to 3 diferent scenarios. I use a trigger or something to push data to the service using VB Script or a DLL. I have thought about having the message service poll the database table. Finally I have thought about having the trigger wake up the service and have it come get the data for sending out.I can't go into any more detail as we are dealing with wireless carriers, device manufacturers, and about 3 subcontractors. Does this help?Adam Z |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-06 : 22:38:26
|
| Well, I would question the need for having a database at all. If your queueing system doesn't already manage this kind of thing automatically, the overhead of storing it in a database is kinda high (compared to simply transmitting a message) and there's no real way to overcome it. And if you DON'T need to store the messages permanently, don't bother with a database layer.If you really need a database though, use it ONLY for storage. It does not make a good message relayer. The queue should be able to send and receive messages directly. Don't know what you're using, but I know MSMQ can handle this kind of thing on its own. The only database interaction would be to accept a copy of the message for storage, that's it. There's nothing that a trigger or scheduled job would do to help you here. In fact, I would suggest that the web app should post the message directly to the queue AND the database. The queue therefore would not (and should not) be bothered with the database side.FWIW I used to work for a wireless provider and we played around with two-way text paging to cell phones. We were able to send two-way pages but didn't quite get to the point where we could get the replies back. Actually a co-worker was able to develop it further and it works very well (he gets a two-way page if a job fails, and can restart it with a button push if he wants to) Considering that this was all done through an automated telnet session too! We didn't have any kind of interface like you're using. Responses were on the order of 20 seconds or so, which wasn't too bad for us. I mention this to point out that sub-millisecond response times aren't always necessary, and unless you're seeing a problem with lag now, don't assume any of the solutions you want to try will perform badly. Try them all and see which one works best.HTH |
|
|
|
azastawski
Starting Member
3 Posts |
Posted - 2003-10-07 : 15:42:01
|
| I agree with you and ironically I brought up the same point in the beginning. We do need a database for reporting and accountability on the messages/replies. We want to compartmentalize the application so we can consume the message service in other applications. My solution based on my testing over the past few days is to develop an XML web service out of the message server and consume the service in the web application. The individual web apps will keep the data in individual databases. The message service will likely serialize the actual message content and maintain only meta data about the messages/tracking info in a SQL2K Database.Adam Z |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-07 : 21:58:21
|
| Ahhhhhh, it helps when the whole picture is laid out in detail. Everything you mentioned sounds good and is likely to be the best way to go. |
|
|
|
|
|
|
|
|
Rapid Extract