polling a database

  • Thread starter Thread starter Pascal
  • Start date Start date
P

Pascal

Hi everybody,
a suggestion, please. I have an application A that modifies the
records of an access database. I should write an application B
(running on the same machine) that monitor these changes, without
modifying application A. I think the only solution is polling the
database and check every record in the table. I realized an
application that reload periodically in memory all the records (I use
an oledbdatareader, stayng always connected) and compares them with
the previous loaded records. Unfortunately the database is very
large
and this procedure takes 2s on a Pentium IV. Do you know any better
method or some tips I could use to improve the performances? I tried
using a dataset and working disconnected, but it's slower (3s).
Thanks.
 
Hi Pascal,

I won't ask you for a reason to build such an application.
However, if it is possible to change the database structure I would add some
sort of timestamp field that changes on insert/modification to each table
you want to track.
So you would track only whether max(timestamp) has changed or something like
that - this will cover inserts and modifications.
As per deletes, I would rely on combination of count(*) and timestamp.
 
Let's say your datatable within the dataset is Customer with a primary key
of CustomerID. Also, let's assume you have a timestamp field in the
database named TimeStamper.

Your initial select would be SELECT * FROM Customer.

1) Now, change your select to SELECT * FROM Customer WHERE TimeStamper > ?
2) Clone the original datatable
3) Fill the cloned table with results of the above select where you set ? to
the maximum TimeStamper in the first table.
4) Merge the cloned table into the real table.

Works great and is really fast!
 
Let's say your datatable within the dataset is Customer with a primary key
of CustomerID. Also, let's assume you have a timestamp field in the
database named TimeStamper.

Your initial select would be SELECT * FROM Customer.

1) Now, change your select to SELECT * FROM Customer WHERE TimeStamper > ?
2) Clone the original datatable
3) Fill the cloned table with results of the above select where you set ? to
the maximum TimeStamper in the first table.
4) Merge the cloned table into the real table.

Works great and is really fast!
Click to expand...

Unfortunately i can't change the fields in the database tables, and
there isn't a timestamp field.
 
Back
Top