Polling changes on a db, and copying changes to an

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Polling changes on a db, and copying changes to an

Author

Topic

thebends
Starting Member

11 Posts

Posted - 2008-04-03 : 15:06:39

Hi all, I'm in the process of cleaning up a very VERY poorly implemented system. One of the problems I'm tackling consists of :

1 - Database A
2 - Database B (different schema than A)
3 - A task which regularly polls A, checks for updated/new records and inserts them (with some transformation) into B

As you can imagine, the overhead of the polling is killing the system. What I want to know is :

What is the best way for A to notify B whenever there are changes to a particular table on A ?

I'm running 2000 but we will most probably upgrade to 2005.

thanks in advance to everybody
Omar

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-03 : 15:17:23

Using trigger.

thebends
Starting Member

11 Posts

Posted - 2008-04-03 : 15:33:44

is using a trigger OK if i'm writing to database B ? doesn't the trigger code execute in the same transaction, therefore could cause performance delays on A ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-03 : 16:02:21

Based on your original post it is the polling that is taking the time. Assuming your transformation is a simple one row on A to one row on B then a trigger may be ok. The trigger would eliminate the need for polling for changes. An other idea is to use a trigger to write the PK value to a processQueue table on A then let a scheduled job (outside the context of the user transaction) transform all the queued up data. The frequency of the job would depend on the rate of changes and the timeliness of changes needed on B.

Be One with the Optimizer
TG

thebends
Starting Member

11 Posts

Posted - 2008-04-04 : 05:03:26

Thanks, that definitely sounds like the way to go. So the polling process would be something like :

1 - Poll change log table
2 - Read last, say, 100 rows
3 - Process those rows
4 - Mark the rows as consumed / delete the rows

Does this sound ok ? I am worried about the cost of step 4.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources