为什么Visual Studio中的SQL Server对象资源管理器这么慢? [英] Why is SQL Server Object Explorer in Visual Studio so slow?

问题描述

我刚刚在Azure中创建了一个新的SQL Server数据库，然后使用Azure Portal中的链接在Visual Studio 2015中将其打开.我必须将我的IP添加到防火墙中，否则该过程进行得很顺利.

I just created a new SQL Server Database in Azure and then opened it in Visual Studio 2015 using the link in the Azure Portal. I had to add my IP to the firewall but otherwise the process went smoothly.

但是，当我尝试通过SQL Server Object Explorer与数据库服务器进行交互时，速度却很慢.展开数据库中的任何文件夹(例如Tables文件夹)需要10到30秒.该数据库是全新的，因此它拥有的唯一内容是实例化新数据库时Azure所创建的任何内容.

However, when I am trying to interact with the database server via SQL Server Object Explorer it is painfully slow. Expanding any of the folders in my Database (e.g., Tables folder) takes 10 to 30 seconds. The database is brand new, so the only things it has are whatever Azure creates when it instantiates a new DB.

这是我创建并尝试在Visual Studio中查看的第二个Azure数据库，并且都存在相同的问题.对于第一个，我以为也许我在设置过程中做错了什么，但是这次我确保按书进行所有操作.

This is the second Azure DB I have created and tried to view in Visual Studio and both have the same problem. With the first one I thought maybe I did something wrong during setup but this time I made sure to do everything by the book.

从Visual Studio中对数据库运行实际查询(右键单击数据库，New Query ...，select * from INFORMATION_SCHEMA.TABLES;)非常快，因此与我与Azure的连接似乎没有问题.

Running actual queries against the DB from within Visual Studio (right click the DB, New Query ..., select * from INFORMATION_SCHEMA.TABLES;) is very fast, so it doesn't appear to be a problem with my connection to Azure.

为什么它这么慢这么慢?我该怎么做才能使其更快?

Why is it so painfully slow? What can I do to make it faster?

我正在Windows 10(已完全修补)上使用Visual Studio 2015 Update 1(14.0.24720.00)，并且在创建数据库的过程中，我选中了使用最新版本的框.

I am using Visual Studio 2015 Update 1 (14.0.24720.00) on Windows 10 (fully patched) and during database creation I checked the box to use the latest version.

推荐答案

第一次连接数据库时，Visual Studio中的SQL Server对象资源管理器(SSDT)会构建整个数据库的架构模型.这是基于模型的数据库开发的概念，它提供了一些好处，例如无需修改实时数据库即可针对数据库验证脚本等.谈到Azure SQL DB，其中资源受数据库定价层限制，执行查询的性能提取数据库模型可能会有所不同.根据我们的性能测试，高于S1/S2的定价层提供的交互性与本地或本地SQL实例类似.

SQL Server Object Explorer (SSDT) in Visual Studio builds a schema model of entire database when you connect a database first time. This is a concept of model based database development which provides benefits such as validating scripts against database without needing to modify the live database etc. When it comes to Azure SQL DB where the resource is limited with your database's pricing tier, the performance of executing queries to fetch database model can vary. Based on our performance tests, pricing tiers above S1/S2 provides a similar interactivity as local or on-prem SQL instance.

更详细地讲，当前SSDT的机制是组合最少的查询数，以最大程度地减少与SQL Server/数据库的往返操作数，因此每个查询的执行时间更长.在执行查询时，它会影响SQL Server对象资源管理器的交互性.

More in details, current SSDT's mechanism is to composite minimum number of queries to minimize the number of round-trip operations with SQL server/database hence each query execution time is longer. It affects the interactivity of SQL Server Object Explorer while the query is being executed.

为增强功能，SSDT团队正在修订Azure SQLDB的设计，以提供更快的交互体验，同时保留基于模型的数据库开发的好处.

To enhance, SSDT team is revising the design for Azure SQLDB to provide a faster interaction experience while keeping the benefit of model based database development.

公开:我作为一名PM在SQL数据工具团队中工作

这篇关于为什么Visual Studio中的SQL Server对象资源管理器这么慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！