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

Question

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

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

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

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?

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:

Azure - Elapsed time = 100.165 sec

Smarterasp.net - Elapsed time = 2.449 sec

LocalServer - Elapsed time = 1.716 sec

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.

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

My questions are:

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

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.

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

Isn't that a question you should ask the Azure support? How should we know the details of your account and their equipment? — arkascha
Are you talking about SQL Azure? The performance is very dependent on the tiers. Anything less than S2 is unlikely to give you acceptable performance. And no, it's not dependent on load, individual query performance is heavily affected by the tiers. — Frans
@Lucian - as your question stands, it's completely unclear. You haven't shown any SQL query in your question, and you haven't mentioned a single thing about either your local server or your selected SQL Database tier. That said: If it's about server performance (or tier performance), that's a question for ServerFault, not StackOverflow. If it's a query issue, then it's appropriate for StackOverflow, but not as a performance difference between different service tiers / hosting companies. — David Makogon
In defence of Lucian's question, there are very clear differences between performance at the different SQL Azure database tiers. The price/performance ratio grows faster than linearly so understanding this is very important for application design, as decisions such as sharding and caching can often be driven by the way price and performance correlates. I have had this happen more than once recently. To be clear, I am a big fan of SQL Azure - I have more than 100 databases in production in Azure. I also think the price is very competitive - but understanding this is essential for design. — Frans
@lucian; following your edit to clarify; its because of the Basic perf level. As per my answer below, try with an S2 as a more realistic starting level. It's worth trying all the levels one at a time, the perf diff is huge. — Frans

Remus Rusanu Remus Rusanu · Accepted Answer · 2015-06-27T10:37:06

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.

After update

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

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

Here is what you should do:

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

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.

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.

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

3 Answers

After update