Unanswered: Replication performance... huge number of delete records
We have developed a sales force system using SQL 2000 server in head-office and field using the HPC (SQL CE). Every day field representative makes some calls and replicate the data to head-office. Data of common clients from different users also get replicated.
Normally users inserts around 100 records and get back around 400 records from publisher, i.e. data of other users due to common clients.
HPC keeps the data of one month, means every day it deletes call data of dates less than months time. So maximum deletions will be around 500 records.
But when we see the agent history, it shows as follows.
Merged 14000 data changes (500 inserts, 13500 deletes)
Downloaded 13900 data changes (400 inserts , 13500 deletes)
Uploaded 100 data changes (100 inserts)
Every day this delete keep on increasing by 500 records. 1st day of replication was very fast, but day by day its taking longer and longer time.
Please suggest on how to keep this deletes in control or any other options to improve performance.