if database table changes

  • Thread starter Thread starter Joe C.
  • Start date Start date
J

Joe C.

hello all, thanks for reading.

i am trying to find a better way to keep track of changes in a ms sql
database table.

we have a ticketing system in the office, and a small app was written
to display a count of all outstanding tickets.

this is achieved by sending a "select count(rowName) from tableName"
query to the database every three seconds via the Timer control in
vb.net.

there's 7 people using this application, which means there's 7
database queries every second.

having conferred with our IT people, this load is still a manageable
one. however, once the application is widely distributed, the sql
server will definitely be stressed.

so, the question is, is there a better way to monitor the state of a
database table (getting a count of rows) that doesn't involve sending
a query on a timed increment?

any help would be greatly appreciated.

-Joe
 
hello all, thanks for reading.

i am trying to find a better way to keep track of changes in a ms sql
database table.

we have a ticketing system in the office, and a small app was written
to display a count of all outstanding tickets.

this is achieved by sending a "select count(rowName) from tableName"
query to the database every three seconds via the Timer control in
vb.net.

there's 7 people using this application, which means there's 7
database queries every second.

having conferred with our IT people, this load is still a manageable
one. however, once the application is widely distributed, the sql
server will definitely be stressed.

so, the question is, is there a better way to monitor the state of a
database table (getting a count of rows) that doesn't involve sending
a query on a timed increment?

any help would be greatly appreciated.

-Joe
Click to expand...

Well... Maybe a more distributed architecture would be called for. You
could use a webservice to actually return the count. You could have the
service do the query on a timer - that would at least centeralize the
query so that no matter how many clients, your still only doing the
query once. But, a better way - especially if this is sqlserver 2005 -
is to make use of sqlserver's query notification feature. That way the
service only has to qeury the database when an actuall change is made to
the data, eliminating the timer.

Anyway, just a thought.
 
Thanks Tom for the suggestion, I'll definitely look into the
notification feature.

Thanks!
 
Back
Top