Dacpac Upgrade database Times a little silly

Question

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..

What happens if you try to just generate a script from within SSDT itself? Does it still take a while to build/publish? Is it the build action or the publish action that takes the longest times? Do you have a lot of DB References? If so, have you considered stripping out unnecessary objects from them? How many objects do you have in your target DB / Project? The time will increase as more objects are added because you're comparing those. You may also want to try using SQLPackage.exe to push the changes and/or gen a script to see how that works. Not the end goal, but could help to troubleshoot — Peter Schott, May 10 '13 at 18:40
Hi @PeterSchott. Within SSDT it takes around 10 seconds to create the script, likewise if I create the script via sqlpackage.exe However if I use the deploy via a dacpac, or create diff script via the API using string the GenerateDeployScript(rather than .Deploy in the original Q) it takes in the region of 20 minutes. The database is relatively small (200 tables, 200 views, 150 functions, 700 sp's..)... nothing rediculous, & the target db is currently empty. The figures are when running on local machine so network isn't an issue either. — Fetchez la vache, May 13 '13 at 13:08
End to end process of creating a script via sqlpackage and deploying updates via sqlcmd is around 20s so I'll probably end up doing that... I'd just like to be able to understand the time differences. — Fetchez la vache, May 13 '13 at 13:08

score 7 · Accepted Answer · answered May 14 '13 at 14:44

7

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!

answered May 14 '13 at 14:44

Fetchez la vache

4,940
4
36
55

I have also been having the same problems. But my solution contains 7 databases with references to each other, and about 3500 stored procedures amongst them. Running through the debugger it takes about 2 hours to deploy! But if I run my compiled app, it takes 10 minutes. Thanks for the tip!!! – Mark Whitfeld Sep 18 '13 at 12:34
@MarkWhitfeld - 2 hours...yikes! – Fetchez la vache Sep 18 '13 at 13:49
1

@Mark - as I mentioned above check whether you are getting a lot of warnings as all the antlr ones are first chance exceptions which pause the app, pass control to the debugger (vs) which decides whether to break or continue - even if you have first chance exceptions disabled, that is what you get with the debugging api (visual studio has no control over it) - it is a massive overhead if there are alot of exceptions. – Ed Elliott Dec 03 '14 at 10:04

score 2 · Answer 2 · answered Dec 02 '14 at 23:59

2

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

answered Dec 02 '14 at 23:59

Ed Elliott

6,666
17
32

Thanks Eddie for the tip I'll check into that. It's been running live on 100's of clients for over 18 months now so it's not at the top of the priority list to check, and obviously they're running in release mode. We don't have a "0 warnings on compile" policy rightly or wrongly, so it's entirely possible warnings have been raised and ignored :| – Fetchez la vache Dec 03 '14 at 09:05

score 2 · Answer 3 · answered Jun 16 '16 at 12:26

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.

Good to know, Matt. Thanks – Fetchez la vache Jun 16 '16 at 15:21 — Fetchez la vache, Jun 16 '16 at 15:21

score 0 · Answer 4 · answered May 07 '13 at 09:09

0

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.

answered May 07 '13 at 09:09

Justin Caldicott

739
8
22

Hi @Justin, thanks for the reply. We'd taken a look at DM and alas the license requirements were not a good fit with our workflow. – Fetchez la vache May 08 '13 at 14:17

Dacpac Upgrade database Times a little silly

4 Answers4