为什么运行的SQL Azure的查询这么慢得多? [英] Why is running a query on SQL Azure so much slower?
问题描述
我创建了天青一个试用帐号,我从<$ C部署我的数据库$ C> SmarterAsp 。
I created a trial account on Azure, and I deployed my database from SmarterAsp
.
当我运行 SmarterAsp \\ MyDatabase的
枢轴查询,结果出现了 2的秒。。
When I run a pivot query on SmarterAsp\MyDatabase
, the results appeared in 2 seconds.
然而, Azure上运行相同的查询\\ MyDatabase的
把94秒。
我使用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:
天青 - 经过时间= 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上我的试用帐户,我的主要目标使它检查,如果运行存储过程像上面之一,当我将有一个更好的速度比智慧。
我选择我的数据库服务层 - 基本,性能等级 - 基本(5DTUs)和马克斯。大小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.
我的问题是:
- 我能做些什么,来优化这个查询(SP)?
- 是天青推荐用于小型数据库(100MB-1GB)?我的意思是性能与成本!
根据您输入的结论:
- 。我在建议修改查询和性能与50%以上的提升 - 谢谢你莱姆斯
- 我测试了在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相同更新的查询已经过去了时间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)
谢谢大家,
卢西恩
Thank you all, Lucian
推荐答案
这是第一和性能的最重要的问题。你对你的一部分,处理一个业绩不佳的code和必须标识瓶颈,解决这一问题。现在我讲的不好的 2的秒。性能。按照在如何分析SQL Server性能准则一>。一旦你得到这个查询来执行本地接受的一个web应用程序(少于5毫秒),那么你可以问它移植到Azure的SQL数据库的问题。现在您的试用帐户仅突出了现有的低效率。
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
?以及为什么使用(最大)
?
so what is it? is the iddepartment
column an int
or an nvarchar
? And why use (max)
?
下面是你应该做的:
- 参数
@iddepartment
在内部动态SQL - 停止做
为nvarchar(最大)
的转换。让iddepartment
和@iddertment
类型匹配 - 确保
iddepartment
索引和所有的idkpi
取值
- parameterize
@iddepartment
in the inner dynamic SQL - stop doing
nvarchar(max)
conversion. Make theiddepartment
and@iddertment
types match - ensure indexes on
iddepartment
and allidkpi
s
下面是如何参数内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;
的覆盖索引是,到目前为止,最重要的修正。这显然需要更多的信息比在这里present。阅读设计指标的包括所有子章节
作为一个更普遍的评论:这种查询合意 columnstores 的超过rowstore,虽然我估计数据大小,基本上微不足道。 SQL Azure的支持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屋!