What are the challenges you face when working across database platforms? Take the survey

Multiple Asynchronous Database.Register calls

Dave WaiteDave Waite Posts: 3
edited October 10, 2006 7:33AM in SQL Compare Previous Versions
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.


  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Dave,

    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.

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1

    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.
  • Options
    Thanks Brian, I agree this could be the cause but are the following also possibilities?:-

    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
  • Options
    Hi Dave,
    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
Sign In or Register to comment.