How can I choose individual records to synchronize?

jimgreenjimgreen Posts: 3
edited January 25, 2007 2:53PM in SQL Toolkit Previous Versions
I have a table, it has about 100 thousand records, i used the follow way to individual records, but it's slowly: Is any others ways to individual records?

Create a new SelectionDelegate and point it at a function that will return true or false, indicating whether or not the record is to be synchronized. This function will accept a SynchronizationRecord object as the only argument. Pass the SelectionDelegate as the second argument to the GetMigrationSQL method of the SqlProvider object.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Jim,

    I suspect that the problem is that the comparison takes a long time to complete rather than the synchronization. Even if you use SelectionDelegate, you still have to compare all 100,000 rows of data before you even get there. The SelectionDelegate only applies to the production of the synchronization script after the comparison part has already been done.
  • if so, Is there any other solve way to individual record to synchronize? i want to optimize the code and make it run faster.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Jim,

    With the Red Gate solution, you would need to compare the contents of two entire tables before you could synchronize even one row of data. You may want to look into the builtin replication methods provided with Microsoft SQL Server (transactional, merge, log shipping, etc).
  • I can suggest something they may help you which i have been using since 2 years and it works.

    we have Live and Archive database and we archive table1 data from live to archive database periodically without duplicate data. The way i use as follows:
    on database 1
    1. table1 (this table keeps filling up every hour)
    2.tmptable1 (exact structure of table1 in database1)
    I first copy data in batchmode, first more 10,000 or more to tmptable1
    and delete from db1.table1 which already exists in tmptable1.

    then copy from tmptable1 to databse2 table1 with

    "insert into db2.table1 ()
    select (<field1>,<field2>) from db1.tmptable1 where <primarykey> not in (select db2.table1.<primarykey> from db2.table1)

    this will copy all the unique records from database1 tmptable1 into database2 table1 once copied you can delete from tmptable1 that exist in db2.table1.

    we do this process on 1 Million or 3-5 Million records databases. It takes 20secs to 2-5mins to complete depending on number of records to copy.

    Hope this helps..
    Arun
Sign In or Register to comment.