CODEWITHSUNDEEP
sql-serversql-server-data-toolsdac
Fetchez la vache
Fetchez la vache

Reputation: 5230

Dacpac Upgrade database Times a little silly

Edit: Updated to state it isn't hanging, just takes AGES!

I'm trying to update an existing sql server database using a dacpac.

I can create a new SQL server database with the (stripped down) example below in 30 seconds. The issue I'm having is that using the same dacpac, rerunning the procedure (so it is updating an existing database rather than creating afresh) takes 20 minutes.

Is this kind if time difference what is to be expected? Having used redgate's SqlCompare comprehensively, I'm finding the time unpaletable.

The third param of the deploy method is UpgradeExisting which I'm setting to true - Is this all I need to do or am I missing something??

void Deploy(string TargetConnectionString, string TargetDatabaseName, string pathToSourceDACPAC)
{

    DacServices dacServices = new DacServices(TargetConnectionString);

    //Set up message and progress handlers
    dacServices.Message += new EventHandler<DacMessageEventArgs>(dbServices_Message);
    dacServices.ProgressChanged += new EventHandler<DacProgressEventArgs>(dbServices_ProgressChanged);

    //Load the DACPAC
    DacPackage dacpac = DacPackage.Load(pathToSourceDACPAC);

    //Set Deployment Options
    DacDeployOptions dacOptions = new DacDeployOptions();
    dacOptions.AllowIncompatiblePlatform = true;

    //Deploy the dacpac
    dacServices.Deploy(dacpac, TargetDatabaseName, true, dacOptions);

}

//Event handlers...
void dbServices_Message(object sender, DacMessageEventArgs e)
{
    OutputThis("DAC Message", e.Message.ToString());
}

void dbServices_ProgressChanged(object sender, DacProgressEventArgs e)
{
    OutputThis(e.Status.ToString(), e.Message.ToString());
}

NB the program disappears into the ether on the dacServices.Deploy line..

Upvotes: 4

Views: 5501

Answers (4)

Matt Frear
Matt Frear

Reputation: 54861

We had the same problem - dacpac deploys would perform fine when running tests but run very slow while debugging tests.

In my case, the dacpac had a bunch of .sql scripts which seeded some test data into the database. One of these scripts was autogenerated from SSMS, so it was 40,000 lines long, something like this:

INSERT [dbo].[Resource] ([CategoryId], [StoreCode], [LocaleCode], [ResourceName], [ResourceText]) VALUES (1, N'AU', N'en-AU', N'AD', N'Andorra')
GO
INSERT [dbo].[Resource] ([CategoryId], [StoreCode], [LocaleCode], [ResourceName], [ResourceText]) VALUES (1, N'AU', N'en-AU', N'AE', N'United Arab Emirates')
GO
INSERT [dbo].[Resource] ([CategoryId], [StoreCode], [LocaleCode], [ResourceName], [ResourceText]) VALUES (1, N'AU', N'en-AU', N'AF', N'Afghanistan')

etc, for 40,000 lines, i.e. 20,000 INSERT AND GO statements. I was able to see that script was causing the problem by querying my dbo.Resource table while I was debugging and seeing that the script in question was executing slowly, as the number of rows in that table was still increasing.

I rewrote that script using some Notepad++ macros to have fewer INSERT statements, and to insert 1000 rows on each INSERT, e.g.

INSERT [dbo].[Resource] ([CategoryId], [StoreCode], [LocaleCode], [ResourceName], [ResourceText]) VALUES 
(1, N'AU', N'en-AU', N'AD', N'Andorra'),
(1, N'AU', N'en-AU', N'AE', N'United Arab Emirates'),
(1, N'AU', N'en-AU', N'AF', N'Afghanistan'),
... 
GO

and that fixed the problem.

Upvotes: 2

Ed Elliott
Ed Elliott

Reputation: 6856

If you are finding it much slower under a debugger, do you have many warnings?

Warnings are generally generated by the antlr parser it uses and the parser throws exceptions which are very expensive.

Work to fix the warnings and the build time should come down.

Ed

Upvotes: 2

Fetchez la vache
Fetchez la vache

Reputation: 5230

OK, the silly times were experienced while running through the debugger (VS2012). Once compiled the times were 25 or so seconds when chosing the Memory DacSchemaModelStorageType, or 45 or so seconds when chosing the File DacSchemaModelStorageType .

I guess this just means that debugging is a pain in the whatsit!

Upvotes: 8

Justin Caldicott
Justin Caldicott

Reputation: 788

Hopefully someone will have some dacpac specific advice, but since you mentioned SQL Compare was faster, you may want to have a look at package based deployments as part of Red Gate's Deployment Manager tool, which has built-in support for SQL Server databases.

Upvotes: 0

Related Questions