Improve performance of object reference resolution from dacpac-dependencies during build by optimizing dacpac internals
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 460
- Forks
- 29
- Avg merge
- 4d 9h
- Merged PRs (30d)
- 7
Description
**Is your feature request related to a problem? Please describe.**
It takes too long to build a big `sqlproj` with many dacpac-dependencies. In some cases build time (in commandline or in VS) is a disaster and it's a [very](https://stackoverflow.com/q/38922948/5921826) [well](https://stackoverflow.com/q/75415711/5921826) [known](https://stackoverflow.com/questions/37437662/can-an-ssdt-project-build-use-multiple-cores) SSDT/DacFx [issue](https://the.agilesql.club/2015/06/why-is-ssdt-so-slow/).
Dacpac contains all the metadata needed for deployment of a database including DB-level `SET` options, permissions, filegroups, triggers. If another project/database needs to refer to this one project/database, developer must include first project's dacpac as a dependency into the second project. Now to build the second project we need to analyze the first project's model.xml beforehand. With all the information **_which cannot be referenced from another database_** by design! Even _public_ objects which can be referenced from outside of a DB are described in an extremely verbose manner which may be required for deployment but is not needed at all for reference resolution.
Besides `model.xml` is an XML file. Having deep trees, "cross references", elements+sub elements for describing a single scalar value - are strongly not recommended XML desing antipatterns, these approaches are performance killers.
Examples of model.xml verbosity
If you have partitioned tables with many partitions then your dacpacs contain hundreds or thousands of elements for each partition:
Here is an example of saying "this column is of type DECIMAL(18,8)":
```xml
```
**Describe the solution you'd like**
Here is a [post on SO](https://stackoverflow.com/a/66048027/5921826) where I described our crutch which decreases build time a lot while providing the same functionality: broken relations are still detected, correct relations don't produce any warnings.
From `model.xml` of dacpacs used as dependencies we remove these things:
- users, logins, roles
- permissions, rolemembership, authorization and execution context (user/login) info
- sensitive data and signatures
- file storage, partitions, data compression info
- triggers
- indicies
- default constraints
- object definition bodies (parameters and return type are described separately in model.xml)
- annotations, extended properties
- and so on
Pruned dacpac size is 10-50 times smaller then the original dacpac, build time reducing is different for each project, the _worst one_ has ~3K procs, ~1K tables and ~30 dacpac dependencies (legacy monster), we build it on CI in about 2 minutes. With regular dacpacs it takes 5-7 minutes.
IMO for improving ref resolution performance a `dacpac` file could contain something like `refs.xml`/`index.xml`/`public.xml` in addition to `models.xml`. Refs-file would contain only _public_ objects and some top-level DB options that may affect communication between databases. This file does not need triggers, permissions, indicies, SP body texts, `disambiguator` thing, only names of computed columns with no computation definition and other _private_ details. If possible, it could be a straightforward `json` file with a Key-Value storage where Key is an object name including schema. Build/compile would check if there is such file inside dacpac-dependency and analyze it instead of analyzing whole huge and verbose `model.xml` with so many details which have no meaning to another database.
Can't be sure, but it smells like by simplifying outer-reference resolution you could unlock multi-core utilization during build process.
**Describe alternatives you've considered**
As said, we already use our `remaster_dacpac.ps1` script which prunes dacpac `model.xml` internals, this script is a step in our regular pipeline. We have ~70 sqlproj * 3 long-living branches, each has two versions of dacpac: regular (with [full paths](https://github.com/microsoft/DacFx/issues/329) replaced back to relative ones) _for deployment_ and diff control (comparing dacpac built from master branch to prod server for anomaly detection purpose) and pruned dacpacs _for referencing_ from other `sqlproj`. We use pruned dacpacs for referencing in both: build process on CI servers and during development in VS. This trick reduces ref resolution and build time significantly.
**Additional context**
Out linter does not allow duplicate entries in `sqlproj` file, does not allow dangling files (not included into sqlproj), and the _worst_ project currently has only two warnings, so these things cannot be the cause of slow builds. `CmdLineInMemoryStorage` is already `true` (see the SO answer mentioned above).
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reviewing how dependency model.xml files are analyzed during sqlproj builds, along with the existing remaster_dacpac.ps1 workaround and the CmdLineInMemoryStorage setting. Compare the proposed refs.xml, index.xml, or public.xml approaches against the metadata currently needed for reference resolution. Done would mean a defined lightweight dependency format that preserves correct and broken-reference behavior while reducing build time.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, json, powershell, sql, xml
- Domain
- backend, databases, performance
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100