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?

Deportation

Visual studio offers ways to find out track which statements are taking time,try to fine tune them.if you are not able to fine tune them,try to use parallelism to deploy multiple projects in paralle – Couchman

Do you really have to create the database in a separate step? Without testing myself, dacservices might be comparing all the objects in the dacpac to their counterparts in the empty database. – Calcicole

@GavinCampbell, TheGamesiwar, both your comments have been useful, and allowed me to decrease the overall time for 4 databases from 25s to 4.5s. – Deportation

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.

Doodlesack

Thanks, Ed. I have added the needed code. As I added in the previous comment, I went down from 25s to 4.5s in the initialization of 4 databases. – Deportation

Yeah, I wasn't sure if there were any "special considerations" for localdb that would stop this approach from working; clearly there aren't! – Calcicole

Minor note: DacPackage objects are disposable. I would advise disposing 'em. – Hakim