SQL Data Compare 6 performance improvement suggestion

scottnelsonscottnelson Posts: 29
When comparing two tables, one with a lot of rows and another that's empty, it would be great if you could realize that the target table is empty and not spend a lot of time doing key-based comparisons to tell me the differences.

In this situation, all you need to do is a bunch of inserts. That would seem (to me) immensely faster.

E.g., source table contains 36,897,327 rows and the target contains 0. SQL data compare is spending a lot of time trying to figure out whether it should do inserts/updates/deletes and it should be obvious what it needs to do.

thanks.

Comments

  • Hi,

    Thanks for the post.

    We do do this intelligently - we really don't do a "SELECT * FROM target_db WHERE comparisonKey=SomethingOrAnother" for every row in the source ;-).

    The reason that a comparison with an empty database doesn't take zero time is that we still have to extract all the data from the source database in order to present it in the UI, and then further to generate the SQL synchronization script.

    Hope that helps,
    Robert
    Robert Chipperfield
    Red Gate
  • Thanks for the clarification.

    I did run into a problem yesterday whereby I ran out of disk space during the script generation phase because of the volume of data I was trying to copy.

    I wanted to copy without using a transaction, so partial syncing would have been fine.

    I wonder if you could find a way to deal with this situation? Perhaps by generating a partial script and then executing it, then repeating?

    As it stands, I'm going to have to open what script was generated, cut-and-paste using UltraEdit into query analyzer, execute, cut-and-paste some more, etc., then re-compare, repeat, repeat, repeat....

    thanks. Overall the product is great.
  • Hi,

    There's a couple of things that might help you here. First off, if you set add environment variable named RGTEMP, and set that to a path with more space available, then Data Compare will use that for temporary storage rather than just your system TEMP location.

    Secondly (if this isn't what you're doing already), you can use the WHERE clause editor to only compare a subset of the rows in the table, then synchronize those, then do the rest of the rows.

    Hope that helps,
    Robert
    Robert Chipperfield
    Red Gate
Sign In or Register to comment.