Multiple Asynchronous Database.Register calls
Dave Waite
Posts: 3
Has anyone tried to use asynchronously register databases?
What we've found is that when we asyncrhonously register 2 databases
using a delegate for the Database.register method and callbacks for the delegate completion, performance benefits are only obtained if the 2 database objects refer to databases on distinct servers. If the database objects are on the same server (different databases obviously!), no benefit of running the register process in parallel is obtained.
Can anyone explain why this should be. My only guess is that there is some form of lock going on in the depths of Database.register method which in effect blocks the 2nd parallel registration attempt until the first completes.
The reason for wanting to do this is that we actually require to sync 3 databases and with a time penalty of 40s per database registration, by running registration in parallel, we can save 80s of waiting around time for the user.
Any input would be greatfully appreciated.
What we've found is that when we asyncrhonously register 2 databases
using a delegate for the Database.register method and callbacks for the delegate completion, performance benefits are only obtained if the 2 database objects refer to databases on distinct servers. If the database objects are on the same server (different databases obviously!), no benefit of running the register process in parallel is obtained.
Can anyone explain why this should be. My only guess is that there is some form of lock going on in the depths of Database.register method which in effect blocks the 2nd parallel registration attempt until the first completes.
The reason for wanting to do this is that we actually require to sync 3 databases and with a time penalty of 40s per database registration, by running registration in parallel, we can save 80s of waiting around time for the user.
Any input would be greatfully appreciated.
Comments
Sorry but I don't know the answer to this off the top of my head. I've asked one of our development staff to contact you about this.
Thanks!
I think the answer could have something to do with locks on the system tables. If two registrations are accessing the same server tables at the same time, I'd imagine there could be a waits on one process or the other.
1. Lack of connection pooling with the underlying registration process?
2. Does the API use the COM interopability layer - it seems to require an STA thread for licencing verification which might mean that the standard .NET threadpool for asynchronous callbacks is not a viable mechanism for asynchronous processing?
I hope this makes sense
I think that Brian is correct. I donot believe that SQL Server will cache the the system tables that define the database (unless you request the information loads and loads for that database). As a result SQL will be forced to go back to disk to retrieve the information. Because you are requesting the information from one SQL Server instance the information will be disk bound, and as a result the registering of databases on the same sql server instance will end up by being sequential. (I donot know what would happen if you put your databases onto two physically different disks? Also if you have multiple SQL Server instances on one physical machine, this may also suffer in the same way.)
We are not pooling our connections, however we will only use one connection per registering each database.
We are not using the threadpool in our SQL Compare Engine code.
If you really want I can show you a way to pool connections. However I believe that in the past this level of optimization has caused considerable problems and Red Gate does not currently recommend it.
Hope that helps
David Connell