SQL Azure性能降低 [英] SQL Azure slow performance

问题描述

在将某些数据转换为SQL Azure时，我注意到了意外的行为. 我做了一个快速测试，这是结果:

While transforming some data to SQL Azure, I have noted an unexpected behavior. I did a quick test and this is the result:

我在数据库中有一个表，其中有1M条记录，数据库大小为230MB，其中没有非聚集索引.

I have a single table in the DB with 1M records and the DB size is 230MB, this without the non-clustered index.

我通过创建具有相同模式的新表并只是将数据从一个表复制到另一个表来进行了简单的测试，如下所示:

I did a simple test by creating a new table with the same schema and just copied the data from one table to another like this:

INSERT INTO [dbo].[lete_new]
           ([str_en]
           ,[str_en_fp])
SELECT [str_en] ,[str_en_fp] FROM [dbo].[lete]

首先，在我的本地工作站(SELECT [str_en]，[str_en_fp]从[dbo].[lete])上需要8秒钟，因此最重要的是插入时间. 当我在本地WS上运行插入操作时，花了10秒钟将数据复制到新表中.最大的惊喜是，当我在Azure上运行相同的查询时，花了30秒.

First of all, on me local workstation (SELECT [str_en] ,[str_en_fp] FROM [dbo].[lete]) takes 8 seconds, so on-top of that is the time that it takes to do the insert. When I ran the insert on my local WS, it took 10 seconds to copy the data to the new table. The big surprise was when I ran the same query on Azure, it took 30 seconds.

我知道这不完全是一个编程问题，但这仍然会影响程序性能，我想知道为什么性能会下降?

I know that this is not exactly a programming question, but still this will impact the program performance an I want to know why is the performance drop?

推荐答案

这里有很多事情在起作用.使用这种类型的操作，对您影响最大的是本地驱动器上的存储系统与SQL Azure之上运行的存储系统不同.请记住，所有写操作都是复制的，因此可以快速处理故障.这么多的写作，我怀疑这是大多数差异的体现.

There quite a few things coming into play here. With this type of operation, the one the is affecting you the most is that the storage system on you local drive is not the same as what SQL Azure runs on top of. Keep in mind that all writes are replicated so that failures can be handled quickly. With this many writes, I suspect that this is where the majority of the difference is seen.

此外，请记住，SQL Azure实例与其他用户共享，并且位于商用硬件上.具有专用于单个用户(台式机)的4个核心的系统的性能将不同于与其他用户一起运行的任何服务器(SQL Azure).

Also, keep in mind that a SQL Azure instance is shared with other users and it sits on commodity hardware. The performance of a system with 4 cores dedicated to a single user (your desktop) is going to differ from any server that is running with other users (SQL Azure).

通常，您将无法将单个专用资源的性能等同于云提供商的性能.好处来自于成本降低，然后扩大规模而不是扩大规模.

In general you will not be able to equate performance of a single dedicated resource to that of a cloud provider. The benefits come from lower cost ramp up and scale out instead of scaling up.

这篇关于SQL Azure性能降低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！