sql - 为什么在SQL Azure上运行查询要慢得多？

我在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秒

LocalServer-经过的时间= 1.716秒

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

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

我的问题是:

我应该怎么做才能优化此查询(sp)？

是否建议将Azure用于小型数据库(100mb-1Gb)？我的意思是性能与成本!

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

我对查询做了建议的更改，并且性能提高了50％以上-谢谢Remus

我在Azure S2上测试了我的查询，更新查询所用的时间为11秒。

我再次测试了我对P1的查询，经过时间为0.5秒:)

在SmarterASP上的同一更新查询的经过时间为0.8秒。

现在对我来说很清楚，Azure中的层是什么，进行良好的查询有多重要(我什至理解了什么是索引及其优势/劣势)

谢谢你们，
露西安

最佳答案

这首先是性能问题。您正在处理性能不佳的代码，因此必须找出瓶颈并加以解决。我现在说的是 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支持可更新的集群列存储索引，您可以在预期数据量很大的情况下进行试验。他们确实需要在本地框中输入“Enterprise / Development”。