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

Question

我在 Azure 创建了一个试用帐户，我从 SmarterAsp 部署了我的数据库。

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

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

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

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

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

查询是:

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

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

Azure - 已用时间 = 100.165 秒

Smarterasp.net - 已用时间 = 2.449 秒

本地服务器 - 已用时间 = 1.716 秒

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

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

我的问题是:

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

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

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

• 我再次测试了 P1 上的查询，运行时间为 0.5 秒:)

• SmarterASP 上的相同更新查询的运行时间为 0.8 秒。

现在我清楚了 Azure 中的层级以及良好查询的重要性(我什至了解什么是索引及其优点/缺点)

谢谢大家，卢锡安

Answer 1

这首先是一个性能问题。您正在处理性能不佳的代码，您必须找出瓶颈并解决它。我现在谈论的是糟糕的2 秒性能。请遵循 How to analyse SQL Server performance 上的指南。一旦您让此查询在本地执行 Web 应用程序可接受的速度(少于 5 毫秒)，您就可以提出将其移植到 Azure SQL DB 的问题。目前，您的试用帐户仅突出了现有的低效率问题。

更新后

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

那它是什么？ iddepartment 列是 int 还是 nvarchar？为什么要使用(max)？

这是你应该做的:

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

以下是如何参数化内部 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;

覆盖索引是迄今为止最重要的修复。这显然需要比这里提供的更多信息。阅读 Designing Indexes包括所有子章节。

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