September 25, 2008 at 10:30 am
I'm supporting vb6 application for monitoring and dispatching.
The way it was working every 10 sec the app made request to our SQL Server and refreshed dispatching screen.Sometimes we have 30 people using simultaneously this software.The problem is that the data changes alot (less then second)and we really need to refresh that often.I wrote some update and insert triggers to monitor changes in our database and I created MonitorTable with 6 fields -one for every table with triggers.So now everytime something changes triggers are fired and they are changing my MonitorTable.I wrote also function with timer that checks for changes is MonitorTable and now only if value of field in MonitorTable is changed then the dispatch query is executed.The problem is that since data in this tables is changing so fast this triggers are fired all the time(maybe 2-5 times per second).I'm not sure but I think that this triggers not only do not help but actually are hurting our SQL server performance.
What can I do? Can I make SQL server to update my VB6 variables on data change in tables instead of checking this from my app with timer?
Any advice will be appreciated.
Thank you.
September 25, 2008 at 11:13 am
You should remove the triggers as they're not helping. The people querying the variables will do as well against the table. You can query with a NOLOCK, and set a READ UNCOMMITTED value that might help.
Or you could investigate Query NOtification. Not sure that would help here or work with VB6.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply