Slow comparison involving encrypted objects

JoshGreenWAUK

Our schema contains a couple of encrypted objects, and using the "Decrypt encrypted objects" option takes comparison time from 20 seconds to over 20 minutes. All of the extra time, the process is stuck on ExternalResource=ASYNC_NETWORK_IO indicating it's not the SQL Server tying things up.

There is also a large inconsistency in the time taken to compare depending on the database being targeted. One with <1500 objects takes around 20 minutes whereas one with >3000 objects (still containing encrypted objects) takes around 30 seconds. Is there anything I should be looking out for in the database that could cause this? Compatibility levels, dropping and recreating the encrypted objects etc.

I've seen a few questions posted on the forums before and the answer is usually to uncheck "Decrypt encrypted objects", but has anyone got any other solutions that would work?

RichardL

Hi @JoshGreenWAUK

Thanks for your post. 

Can you confirm which version of SQL Compare you're using? 

Kind regards

Richard Lynch. 

JoshGreenWAUK

Using 13.3.5.6244 currently. Also tried on 13.1.x before upgrading the other day to see if that would fix it!

Maybe something to note, but I notice it a lot more with DBs being compared across a network rather than being on my local machine. I could pull one of the networked DBs back to my machine to test this out, unless you might have another theory?

Tianjiao_Li

Hi @JoshGreenWAUK

Generally speaking, comparison will slow down if you have encrypted objects and  "Decrypt encrypted objects" is checked. The time varies and it doesn't only depend on the number of objects but also the complexity of your database. 

An unstable network connection definitely doesn't help in this situation and it makes sense to do the tests on a local machine. However, as SQL Compare needs to scan the entire database to understand the dependencies and work out the differences, it can take a while to finish.

JoshGreenWAUK

Pulled the exact database back from the networked location, performed the comparison again and it completed in 20 seconds, so I definitely think there is a problem here! When I say networked I mean LAN, not across the internet, so there isn't much network overhead.

Any further diagnostics I can gather to help out here?

RichardL

Hi @JoshGreenWAUK

Thanks for your reply. 

Can you specify exactly what the problem is? 
You can always send us a ticket if you have a support contract and this way a dedicated product specialist can help you out with your issue. 

Kind regards

Richard Lynch. 

JoshGreenWAUK

I've raised a support ticket for this, hopefully the outcome can be posted back to this discussion for others who come across this issue.

roquevera

Hi, I am having similar issues with a database with encrypted objects. Did you have a solution from the engineer?

JoshGreenWAUK

Not really, the issue seemed to resolve itself. I think network quality was part of the issue but it could have been one of the many SQL Compare releases that improved it.

Trying it again now it took around 2 mins (most of the time stuck on "Reading object text (99%)") to do a comparison to a server in our local network, but that's a lot better than the 20+ mins it took when I raised this initially.