Improve the performance of dacpac deployment using c#

Question

We are deploying to MS SQL Server localdb for integration testing.

We build a Database Project and the resulting dacpac file is copied in order to be used by the IntegrationTests project. So far we have:

DatabaseProject.sqlproj
    bin/debug/DatabaseProject.dacpac
IntegrationTests.csproj 
    bin/debug/DatabaseProject.dacpac

We have an assembly setup in the IntegrationTests project where a new fresh database is created and the dacpac is deployed to localdb. In the TearDown the database is deleted so we have a deterministic state for testing.

This is the code that deploys the dacpac, which uses DacServices (Microsoft.SqlServer.Dac, System.Data.SqlLocalDb, System.Data.SqlClient):

public void CreateAndInitializeFromDacpac(
ISqlLocalDbInstance localDbInstance,
string databaseName,
string connectionString,
string dacpacPath)
{

    using (var cx = localDbInstance.CreateConnection())
    {
        cx.Open();
        using (var command = new SqlCommand(
            string.Format("CREATE DATABASE {0}", databaseName), cx))
            command.ExecuteNonQuery();
    }

    var svc = new DacServices(connectionString);

    svc.Deploy(
        DacPackage.Load(dacpacPath),
        databaseName,
        true
        );
}

We are having now a couple of database projects, and it takes about 8s to deploy each one. That increases the overall time to execute the tests.

Is it possible somehow to improve the deploy performance of the dacpac?

Answer 1

Gavin is right!

Don't tear the database down instead use the Create New Database option so SSDT doesn't have to waste time comparing two models when it knows one is empty.

The code for deployment should be changed to:

var dacOptions = new DacDeployOptions { 
               CreateNewDatabase = true
            };

svc.Deploy(
    DacPackage.Load(dacpacPath),
    databaseName,
    true, 
    options: dacOptions
    );

There are loads of additional optimizations that SSDTcan do if you set this flag - if you can be bothered to use reflector have a look at Microsoft.Data.Tools.Schema.Sql.dll and Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.OnInitialize(SqlDeployment). If that flag is set it skips out the whole connect to a database and reverse engineer from the deployed T-SQL to the model.

This probably leads to a potential bug where people change the model database to include some objects and then use SSDT to deploy an object that is in the model database but as edge cases go, it sounds pretty low!

Performance can also be improved for the multiple database case by parallelizing the code with Parallel.Foreach, as suggested by TheGameiswar.