为什么在 SQL Azure 上运行查询速度如此之慢？

Why is running a query on SQL Azure so much slower?

提问by Lucian Bumb

I created a trial account on Azure, and I deployed my database from SmarterAsp.

我在Azure上创建了一个试用帐户，并从SmarterAsp.

When I run a pivot query on SmarterAsp\MyDatabase, the results appeared in 2 seconds.

当我在 上运行数据透视查询时SmarterAsp\MyDatabase，结果在2 秒内出现。

However, running the same query on Azure\MyDatabasetook 94 seconds.

但是，运行相同的查询Azure\MyDatabase需要94 秒。

I use the SQL Server 2014 Management Studio (trial) to connect to the servers and run query.

我使用 SQL Server 2014 Management Studio（试用版）连接到服务器并运行查询。

Is this difference of speed because my account is a trial account?

这种速度差异是因为我的帐户是试用帐户吗？

Some related info to my question

一些与我的问题相关的信息

the query is:

查询是：

ALTER procedure [dbo].[Pivot_Per_Day]
@iyear int,
@imonth int,
@iddepartment int

as

declare @columnName Nvarchar(max) = ''
declare @sql Nvarchar(max) =''

select @columnName += quotename(iDay) + ','
from (
        Select day(idate) as iDay
        from kpivalues where year(idate)=@iyear and month(idate)=@imonth
        group by idate
        )x

set @columnName=left(@columnName,len(@columnName)-1)

set @sql ='


Select * from (
select kpiname, target, ivalues, convert(decimal(18,2),day(idate)) as iDay   

from kpi

inner join kpivalues on kpivalues.idkpi=kpi.idkpi

inner join kpitarget on kpitarget.idkpi=kpi.idkpi

inner join departmentbscs on departmentbscs.idkpi=kpi.idkpi

where iddepartment='+convert(nvarchar(max),@iddepartment)+'

group by kpiname,target, ivalues,idate)x

pivot
(
     avg(ivalues)
    for iDay in (' + @columnName + ')
) p'

execute sp_executesql @sql

Running this query on 3 different servers gave me different results in terms of Elapsed time till my pivot table appear on the screen:

在 3 个不同的服务器上运行此查询在我的数据透视表出现在屏幕上之前的经过时间方面给了我不同的结果：

Azure - Elapsed time = 100.165 sec

Azure - 已用时间 = 100.165 秒

Smarterasp.net - Elapsed time = 2.449 sec

Smarterasp.net - 经过时间 = 2.449 秒

LocalServer - Elapsed time = 1.716 sec

LocalServer - 已用时间 = 1.716 秒

Regarding my trial account on Azure, I made it with the main goal to check if I will have a better speed than Smarter when running stored procedure like the above one. I choose for my database Service Tier - Basic, Performance level -Basic(5DTUs) and Max. Size 2GB.

关于我在 Azure 上的试用帐户，我的主要目标是检查在运行上述存储过程时是否会比 Smarter 具有更好的速度。我为我的数据库选择服务层 - 基本、性能级别 - 基本（5DTU）和最大。大小 2GB。

My database has 16 tables, 1 table has 145284 rows, and the database size is 11mb. Its a test database for my app.

我的数据库有16张表，1张表有145284行，数据库大小为11mb。它是我的应用程序的测试数据库。

My questions are:

我的问题是：

1. What can I do, to optimize this query (sp)?
2. Is Azure recommended for small databases (100mb-1Gb)? I mean performance vs. cost!

1. 我该怎么做才能优化此查询 (sp)？
2. Azure 是否推荐用于小型数据库 (100mb-1Gb)？我的意思是性能与成本！

Conclusions based on your inputs:

根据您的输入得出的结论：

• I made suggested changes to the query and the performance was improved with more than 50% - Thank you Remus
• I tested my query on Azure S2 and the Elapsed time for updated query was 11 seconds.

• I tested again my query on P1 and the Elapsed time was 0.5 seconds :)

• the same updated query on SmarterASP had Elapsed time 0.8 seconds.

• 我对查询进行了建议更改，性能提高了 50% 以上 - 谢谢 Remus
• 我在 Azure S2 上测试了我的查询，更新查询的经过时间为 11 秒。

• 我在 P1 上再次测试了我的查询，经过的时间是 0.5 秒 :)

• SmarterASP 上相同的更新查询的 Elapsed time 为 0.8 秒。

Now its clear for me what are the tiers in Azure and how important is to have a very good query (I even understood what is an Index and his advantage/disadvantage)

现在我很清楚 Azure 中的层是什么以及拥有一个非常好的查询有多重要（我什至了解什么是索引及其优点/缺点）

Thank you all, Lucian

谢谢大家，卢西安

采纳答案by Remus Rusanu

This is first and foremost a question of performance. You are dealing with a poorly performing code on your part and you must identify the bottleneck and address it. I'm talking about the bad 2 secondsperformance now. Follow the guidelines at How to analyse SQL Server performance. Once you get this query to execute locally acceptable for a web app (less than 5 ms) then you can ask the question of porting it to Azure SQL DB. Right now your trial account is only highlighting the existing inefficiencies.

这首先是一个性能问题。您正在处理性能不佳的代码，您必须确定瓶颈并解决它。我现在说的是糟糕的2 秒性能。遵循如何分析 SQL Server 性能中的指南。一旦你让这个查询在本地执行，对于 Web 应用来说是可接受的（少于 5 毫秒），那么你可以询问将它移植到 Azure SQL DB 的问题。现在，您的试用帐户只是突出了现有的低效率。

After update

更新后

...
@iddepartment int
...
iddepartment='+convert(nvarchar(max),@iddepartment)+'
...

so what is it? is the iddepartmentcolumn an intor an nvarchar? And why use (max)?

那是什么？是iddepartment列一个int或一个nvarchar？为什么使用(max)？

Here is what you should do:

这是你应该做的：

• parameterize @iddepartmentin the inner dynamic SQL
• stop doing nvarchar(max)conversion. Make the iddepartmentand @iddertmenttypes match
• ensure indexes on iddepartmentand all idkpis

• @iddepartment在内部动态 SQL 中参数化
• 停止做nvarchar(max)转换。使iddepartment和@iddertment类型匹配
• 确保索引iddepartment和所有idkpis

Here is how to parameterize the inner SQL:

以下是参数化内部 SQL 的方法：

set @sql =N'
Select * from (
select kpiname, target, ivalues, convert(decimal(18,2),day(idate)) as iDay   
from kpi
inner join kpivalues on kpivalues.idkpi=kpi.idkpi
inner join kpitarget on kpitarget.idkpi=kpi.idkpi
inner join departmentbscs on departmentbscs.idkpi=kpi.idkpi
where iddepartment=@iddepartment
group by kpiname,target, ivalues,idate)x
pivot
(
     avg(ivalues)
    for iDay in (' +@columnName + N')
) p'

execute sp_executesql @sql, N'@iddepartment INT', @iddepartment;

The covering indexes is, by far, the most important fix. That obviously requires more info than is here present. Read Designing Indexesincluding all sub-chapters.

到目前为止，覆盖索引是最重要的修复。这显然需要比这里提供的更多信息。阅读设计索引，包括所有子章节。

As a more general comment: this sort of queries befit columnstoresmore than rowstore, although I reckon the data size is, basically, tiny. Azure SQL DB supports updateable clustered columnstore indexes, you can experiment with it in anticipation of serious data size. They do require Enterprise/Development on the local box, true.

作为更一般的评论：这种查询比行存储更适合列存储，尽管我认为数据大小基本上很小。Azure SQL DB 支持可更新的聚集列存储索引，您可以在预计数据量很大时对其进行试验。他们确实需要本地机器上的企业/开发，真的。

回答by Frans

(Update: the original question has been changed to also ask how to optimise the query - which is a good question as well. The original question was why the differencewhich is what this answer is about).

（更新：原始问题已更改为还询问如何优化查询 - 这也是一个很好的问题。原始问题是为什么差异，这就是这个答案的内容）。

The performance of individual queries is heavily affected by the performance tiers. I know the documentation implies the tiers are about load, that is not strictly true.

单个查询的性能受性能层的影响很大。我知道文档暗示层是关于负载的，这并不完全正确。

I would re-run your test with an S2 database as a starting point and go from there.

我会以 S2 数据库为起点重新运行您的测试，然后从那里开始。

Being on a trial subscription does not in itself affect performance, but with the free account you are probably using a B level which isn't really useable by anything real - certainly not for a query that takes 2 seconds to run locally.

试用订阅本身不会影响性能，但使用免费帐户，您可能使用的是 B 级，它实际上不能被任何真实的东西使用 - 当然不是对于需要 2 秒在本地运行的查询。

Even moving between, say, S1 and S2 will show a noticeable difference in performance of an individual query. If you want to experiment, do remember you are charged a day for "any part of a day", which is probably okay for S level but be careful when testing P level.

即使在 S1 和 S2 之间移动，单个查询的性能也会出现明显差异。如果您想进行实验，请记住“一天中的任何部分”都需要支付一天的费用，这对于 S 级别来说可能没问题，但在测试 P 级别时要小心。

For background; when Azure introduced the new tiers last year, they changed the hosting model for SQL. It used to be that many databases would run on a shared sqlserver.exe. In the new model, each database effectively gets its own sqlserver.exe that runs in a resource constrained sandbox. That is how they control the "DTU usage" but also affects general performance.

为背景；当 Azure 去年推出新层时，他们更改了 SQL 的托管模型。过去，许多数据库会在共享的 sqlserver.exe 上运行。在新模型中，每个数据库都有效地获得了在资源受限沙箱中运行的自己的 sqlserver.exe。这就是他们控制“DTU 使用”的方式，但也会影响总体性能。

回答by Low Flying Pelican

It's nothing to do with the fact that your account is trial, it's due to the lower performance level you have selected.

这与您的帐户处于试用状态无关，这是由于您选择的性能级别较低。

In other service (SmarterAsp) and running local instance you probably do not have performance restrictions rather size restrictions.

在其他服务 (SmarterAsp) 和运行本地实例中，您可能没有性能限制而不是大小限制。

At this point it's impossible to put together what actually DTU means / what sort of DTU number is associated with a Sql server installed in your local machine or in any other hosting provider.

在这一点上，不可能将 DTU 的实际含义/什么样的 DTU 编号与安装在本地计算机或任何其他托管服务提供商中的 Sql 服务器相关联。

However, there are some good analysis (https://cbailiss.wordpress.com/2014/09/16/performance-in-new-azure-sql-database-performance-tiers/) done regarding this but nothing official.

但是，对此有一些很好的分析（https://cbailiss.wordpress.com/2014/09/16/performance-in-new-azure-sql-database-performance-tiers/），但没有官方的。