I am writing an application to look through an Interbase back office
database, detect any changes (by looking at a timestamp) since the last
'synch' and write them to the SQL Server 2000 DB. In general it works fine
with smaller quantities of data, but when I get into the 10,000s of record it
all grinds to a halt.
I'm assuming this is a transaction log problem? I've tried batching the
records into transactions (typically of 1000 updates) but this doesn't seem
to speed things up much.
To do the initial load of the database I used a DTS package - and that was
astoundingly fast. But in live use I need to trickle changes through rather
than truncate the table and bulk copy it.
Any ideas where I'm going wrong? Any help greatly appreciated!