August 14, 2001 at 9:55 am
i have a table that has event infomation inserted as they are created. i have and app that polls the table I.e does a select every 30 sec. to see if their are any events for it. is their any way to stop the polling and rather push the event entry to the client pc /app as it is inserted in the table.
August 14, 2001 at 10:30 am
The only way would be to have a trigger "send" a message to the app. This may involve a COM type object, but I would recommend against it. If the trigger gets hung for some reason, it may "lock" your system.
Steve Jones
August 14, 2001 at 7:46 pm
What's your ultimate goal of removing or moving the polling? To lighten the traffic?
August 14, 2001 at 8:25 pm
The only way to avoid polling is to raise an event (COM or COM+), or fake it by sending email and processing that in an event client side. COM+ has some interesting capabilities with loosely coupled events, but I not good enough I think.
I agree with Steve that you should be careful about what code you run in your trigger. I'd suggest building a "queue", either a table or a real MSMQ one, just have the trigger load the queue. Then you can either check the queue from your app (still polling, but should be faster?) or run a job that if there is anything in the queue table, send email to the client.
Im curious about why you dont want to poll. Its not the most elegant solution, but it works pretty good! Main thing is to make your polling lightweight and only do as often as you need to - do you really need to go every 30 seconds.
Interesting case - give us more info!
Andy
August 17, 2001 at 2:35 am
The reson is to cut down traffic. as of late things have been getting slow on the network. we have upgraded the backbone to gigabit links and we are about to upgrade sql servers net cards to give us 200mb to the switch. but the location of these machines are still on 10mbs link and difficult to upgrade. some of them are still on coaix but are going to be upgraded. another problem is the event table is huge and because of the design we cannot create a unique key on it making it difficult to create decnt indexes on it. we cannot redesign the db as the events inserted into it is by a dll from our production equipment which to get the manufacturer to change at this time would be lenghtly and costly procedure. so what i am trying to achive is less activity to the event table. hope this helps.
August 17, 2001 at 5:04 am
So, you just have one client machine polling or many? You really have two issues:
1) Reduce bandwidth usage
2) Optimize the selection process
For #1 (assuming only one client polling) I don't think you'll get much return by moving to an event model, possibly even worse depending on what mechanism you build. Right now its one query every 30 secs, may or may not return data. Doing it a different way might just generate MORE trips from server to client, though you would save the client to server request. But really, how much traffic is that?? A simple hack would be to just increase the polling interval. Do you really need to poll every 30 seconds?
On #2, putting a trigger on the table that will identify records that need to be processed and adding them to a "queue" table will speed up the select a lot. Once you process a record from the queue, remove it. Why cant you create a unique key? You can't add an identity or uniqueidentifier to it? Even if the app is generating its insert from the schema directly but is too dumb to understand what identity is, rename the table and create a view with the original table name.
A combo of my suggestions for 1 and 2 will probably net you some performance gain. You can eliminate network traffic altogether if you put the client app on the server. Perfectly valid solution in some cases - if the server has the capacity.
Andy
August 20, 2001 at 9:34 am
Thanks for the help. I think You have provided me with some solutions to look into. just to note their are about 25 machines polling.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply