We want to use RedGate's SQL Data Compare to migrate user data from one DB to a DB on a completely different provider. This will be a time-sensitive operation as we do not want our site offline for too long and there will be a lot of data that needs to be moved. We do not want any I/O on the table during this time.

Has anyone using RedGate found that adding additional indexes on some of our comparison criteria columns (i.e. DateCreated, DateModified) will affect the time to perform this? Our initial testing is at about 25-30 min for comparison of the source -> target DBs at the moment.

  • 2
    How long is it taking? How long do you want it to take? Exploring optimizations without having an idea of what you want to achieve is a waste of time. If your comparison is taking an hour, and you're expecting it to be done in 5 seconds, then chances are you going to be disappointed. – Andy Lester May 5 '14 at 19:32
  • 1
    DO you mean SQL Data Compare? SQL Compare wouldn't be affected at all by indexes as far as I can think of since it only operates on the definitions of objects and not the data itself. In any event, I think you are barking up the wrong tree. – JohnFx May 5 '14 at 19:36
  • migrate user data from one DB to a DB on a completely different provider. can you provide more info on that ? There might be better way of doing it once you provide more info. – Kin Shah May 5 '14 at 22:30

The question suggests that the tool is being used to migrate data and not schema, which implies that you are referring to SQL Data Compare and not SQL Compare.

In which case, yes, indexes on the comparison key columns will speed on SQL Data Compare.

I've consulted the SQL Data Compare project manager, who provides the following insight: "Usually performance problems with SDC have more to do with the network connection speed to the server and/or the disk speed on the location of the temp files, neither of which are affected by having an index on the comparison key"

SQL Data Compare performance tips are part of the SQL Data Compare documentation.

  • Thanks - and yes, it is RedGate DATA compare. – rwcorbett May 6 '14 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.