为什么在 SQL Azure 上运行查询的速度如此之慢? [英] Why is running a query on SQL Azure so much slower?

问题描述

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

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

当我在 SmarterAspMyDatabase 上运行数据透视查询时，结果出现在 2 秒.

When I run a pivot query on SmarterAspMyDatabase, the results appeared in 2 seconds.

但是，在 AzureMyDatabase 上运行相同的查询需要 94 秒.

However, running the same query on AzureMyDatabase took 94 seconds.

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

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

这是不是因为我的账户是试用账户造成的速度差异?

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

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

查询是:

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 个不同的服务器上运行此查询在我的数据透视表出现在屏幕上之前的经过时间方面给了我不同的结果:

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

Azure - 经过的时间 = 100.165 秒

Azure - Elapsed time = 100.165 sec

Smarterasp.net - 经过的时间 = 2.449 秒

Smarterasp.net - Elapsed time = 2.449 sec

LocalServer - 已用时间 = 1.716 秒

LocalServer - Elapsed time = 1.716 sec

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

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.

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

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

我的问题是:

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

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

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

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

• 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 :)

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

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

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

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)

谢谢大家，卢锡安

推荐答案

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

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 seconds performance 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.

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

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

so what is it? is the iddepartment column an int or an nvarchar? And why use (max)?

这是你应该做的:

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

• parameterize @iddepartment in the inner dynamic SQL
• stop doing nvarchar(max) conversion. Make the iddepartment and @iddertment types match
• ensure indexes on iddepartment and all idkpis

这里是如何参数化内部 SQL:

Here is how to parameterize the inner 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 Indexes including all sub-chapters.

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

As a more general comment: this sort of queries befit columnstores more 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.

这篇关于为什么在 SQL Azure 上运行查询的速度如此之慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！