Retrieving schema takes hours
wombel
Posts: 4
Hi,
I just tried to run datacompare, but the "Retrieving schema" takes hours. Source is a backup file on my local machine, target is on of our database servers.
What can I do to speed things up?
TIA
Wombel
I just tried to run datacompare, but the "Retrieving schema" takes hours. Source is a backup file on my local machine, target is on of our database servers.
What can I do to speed things up?
TIA
Wombel
Comments
I wonder if you could let me know some more details about the databases you're working with (rough numbers only, of course)
- How big is the backup?
- Are there a very large number (thousands, tens of thousands, that sort of number) of tables or views?
- Is the backup a "native" SQL Server backup, or a Red Gate SQL Backup backup?
Also, when you're waiting for it to retrieve the schema, what sort of CPU and hard disk activity are you seeing? Chances are one of those is the limiting factor, but it could be either.
Thanks,
Robert
Red Gate
the backup file is about 10 GB
There are about 500 tables in the database
It is a RedGate SQL Backup
CPU on my local machine is between 60 % and 95 %, sometimes 100 %.
Harddisk is quite busy.
Cheers
Thomas
Thanks for the reply. I suspect it's the initial parsing of the backup that's taking the majority of the time. I've just given it a quick test with a 3GB SQL Backup File (native backup would be about 10GB, original database about 30GB), and it took about five minutes to register and retrieve the schema.
I'm afraid it does take a while on larger backups - unfortunately the layout of the backup file isn't deterministic, so we have to do an initial scan of the whole file before we can get any information out.
Depending on the relative speed of your processor and disk, you might find some compression levels are better than others - on a fast CPU, higher compression will be quicker since there will be less disk access, but on a slower CPU, the disk won't be the critical factor but the CPU will.
Another thing you might want to try is using multiple threads in your backups - especially if your machine is dual-processor/core/hyperthreaded. The effect isn't particularly huge on the main comparison process, but more threads could speed up the initial registration. Again, it depends on how saturated your CPU is.
Hope that helps,
Robert
Red Gate
I had it run over night and this morning it was done. Now went to "tables and views" and selected the one table I wanted to compare and started the comparison. It started doing so, passed "Registering databases", passed "Mapping" and the came up with the following message:
Comparing databases
Comparing [dbo].[member_bs]
(Red cross in front)
Error message at the bottom of the messagebox: Object reference not set to an instance of an object.
Hmm... that's obviously not meant to happen! Are you able to compare the live database to itself? That should hopefully establish whether it's the backup half of things that's causing the problem.
Assuming the compare of live to live works OK, I wonder if you could create a folder named "logs" within your SQL Data Compare installation directory, ensuring that your user has permissions to write to it. Restart Data Compare, and try running the comparison from the backup again (if you save the project, you won't have to wait for it to retrieve the schema every time to let you select only the one table).
This should result in some files being left in the logs directory. If you could email these to me (robert dot chipperfield@red-gate.com), that'd be really useful, and I can try and track down what's going on.
Sorry this hasn't worked for you straight out of the box...
Robert
Red Gate
We've just released Data Compare 6.1, which fixes a number of bugs. This is a free upgrade if you're currently a Data Compare 6.0 user, so it might be worth a try if you're still having problems. You can download the latest version by going to Check for Updates on the Help menu.
Robert
Red Gate