使用C#提高dacpac部署的性能 [英] Improve the performance of dacpac deployment using c#

问题描述

我们正在部署到MS SQL Server localdb进行集成测试.

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

我们构建一个数据库项目，并复制生成的dacpac文件以供IntegrationTests项目使用.到目前为止，我们有:

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

我们在IntegrationTests项目中有一个程序集设置，其中将创建一个新的新数据库并将dacpac部署到localdb.在TearDown中，数据库被删除，因此我们具有确定性的测试状态.

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.

这是部署dacpac的代码，该代码使用DacServices(Microsoft.SqlServer.Dac，System.Data.SqlLocalDb，System.Data.SqlClient):

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
        );
}

我们现在有几个数据库项目，部署每个项目大约需要 8s .这样会增加执行测试的总时间.

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.

是否可以通过某种方式提高dacpac的部署性能?

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

推荐答案

Gavin是正确的！

Gavin is right!

不要破坏数据库，而使用 Create New Database 选项，这样SSDT知道一个模型为空时，不必浪费时间比较两个模型.

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.

部署代码应更改为:

var dacOptions = new DacDeployOptions { 
               CreateNewDatabase = true
            };

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

如果设置此标志，则SSDT还有很多其他优化功能-如果您不愿意使用反射器，请查看Microsoft.Data.Tools.Schema.Sql.dll和Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.OnInitialize(SqlDeployment).如果设置了该标志，它将跳过与数据库的整个连接，并从部署的T-SQL到模型进行反向工程.

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.

这可能会导致潜在的错误，即人们将模型数据库更改为包括一些对象，然后使用SSDT部署模型数据库中的对象，但随着情况的发展，听起来很低！

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!

如TheGameiswar所建议的，还可以通过将代码与Parallel.Foreach并行化来改善多数据库情况下的性能.

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

这篇关于使用C#提高dacpac部署的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！