title: Why is running a query on SQL Azure so much slower?
body: <p>I created a trial account on <a href="http://azure.microsoft.com/en-us/" rel="noreferrer">Azure</a>, and I deployed my database from <code>SmarterAsp</code>. </p>
  
  <p>When I run a pivot query on <code>SmarterAsp\MyDatabase</code>, the results appeared in <strong>2 seconds</strong>. </p>
  
  <p>However, running the same query on <code>Azure\MyDatabase</code> took <strong>94 seconds</strong>. </p>
  
  <p>I use the SQL Server 2014 Management Studio (trial) to connect to the servers and run query. </p>
  
  <p>Is this difference of speed because my account is a trial account?</p>
  
  <p><strong>Some related info to my question</strong> </p>
  
  <p>the query is:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Running this query on 3 different servers gave me different results in terms of Elapsed time till my pivot table appear on the screen:</p>
  
  <p>Azure - Elapsed time = 100.165 sec</p>
  
  <p>Smarterasp.net - Elapsed time = 2.449 sec</p>
  
  <p>LocalServer - Elapsed time = 1.716 sec</p>
  
  <p>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.</p>
  
  <p>My database has 16 tables, 1 table has 145284 rows, and the database size is 11mb. Its a test database for my app. </p>
  
  <p>My questions are:</p>
  
  <ol>
  <li>What can I do, to optimize this query (sp)? </li>
  <li>Is Azure recommended for small databases (100mb-1Gb)? I mean performance vs. cost! </li>
  </ol>
  
  <p><strong>Conclusions based on your inputs:</strong></p>
  
  <ul>
  <li>I made suggested changes to the query and the performance was improved with more than 50% - Thank you Remus</li>
  <li>I tested my query on Azure S2 and the Elapsed time for updated query was 11 seconds. </li>
  <li><p>I tested again my query on P1 and the Elapsed time was 0.5 seconds :)</p></li>
  <li><p>the same updated query on SmarterASP had Elapsed time 0.8 seconds. </p></li>
  </ul>
  
  <p>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) </p>
  
  <p>Thank you all,
  Lucian</p>