Disable indexes on views during BACPAC import
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 460
- Forks
- 29
- Avg merge
- 4d 9h
- Merged PRs (30d)
- 7
Description
I have a database that contains some indexed views. When I export it as a BACPAC and then import the BACPAC to SQL Server 13.0.5026.0 using SSMS 18.10 (not sure which DacFx version that uses), the import is significantly slower while writing to tables that are referenced by an indexed view than while writing to other tables. I've looked at the execution plan and confirmed that the indexes are being updated as part of the bulk insert.
I've noticed that DacFx disables indexes on tables while importing; I think it would be good if it also disabled indexes on views at the same time.
As a workaround I can drop all views before exporting to BACPAC and then use a DACPAC deploy to recreate them after importing.
This blog post appears to be describing the same problem: https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-120-what-is-the-impact-of-having-an-indexed-view/ba-p/1113256
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 reproducing the BACPAC import on SQL Server 13.0.5026.0 with tables referenced by indexed views, then inspect the bulk-insert execution plan and compare it with a table without indexed views. Done means view indexes are disabled during import and the indexed-view overhead is avoided, with the import completing successfully.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100