title: Azure SQL slow performance
body: <p>Quick summary of my problem: In my Azure SQL S0 instance it takes 8:57 minutes to perform a SELECT  WHERE ColumnXYZ = '%anything%' on a table with 8,211,037 rows (result set = 929 rows). On a table with 500,000 rows, it takes 38 seconds. The same table on my laptop (fast with SSD) with 8m lines takes 0 seconds to complete. </p>
  
  <p>I understand that there can be a difference due to specs, but I don't understand the massive difference - these performance levels won't allow me to use Azure SQL (my db is going to be used by a single concurrent user running occassional large queries). Also, I'm wary to upgrade to a higher tier, because I don't need the DB to be twice or four times as fast - it needs to be 500 times faster. Any ideas if I'm doing something wrong? Or are faster results simply not possible in the Azure SQL Standard Tiers? Premium Tiers wouldn't be cost-effective for me as the DB would be idling most of the time. I'm not a DB expert but I'll try to provide some relevant details below - please advise if I should add more details. </p>
  
  <p>Table schema:</p>
  
  <pre><code>CREATE TABLE [dbo].[TestTable](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [PartNumber] [nvarchar](50) NULL,
      [Name] [nvarchar](450) NULL,
      [ProgramName] [nvarchar](450) NULL,
      [URL] [nvarchar](450) NULL,
      [ProgramNumber] [nvarchar](450) NULL,
      [Date] [datetime] NULL,
  PRIMARY KEY CLUSTERED 
  (
      [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  )
  </code></pre>
  
  <p>Non-clustered indexes on PartNumber, Name, ProgramName. ProgramNumber. Clustered index on ID.</p>
  
  <p>Query:</p>
  
  <pre><code>SELECT [PartNumber]
        ,[Name]
        ,[ProgramName]
        ,[URL]
        ,[ProgramNumber]
        ,[Date]
    FROM [dbo].[TestTable]
    where ProgramName like '%test%'
  </code></pre>
  
  <p>Execution Plan (Set SHOWPLAN_ALL ON) first column:</p>
  
  <pre><code>[removed original query as it takes up too much space
    |--Nested Loops(Inner Join, OUTER REFERENCES:([db1].[dbo].[TestTable].[ID], [Expr1002]) OPTIMIZED WITH UNORDERED PREFETCH)
         |--Index Scan(OBJECT:([db1].[dbo].[TestTable].[IX_TestTable_ProgramName]),  WHERE:([db1].[dbo].[TestTable].[ProgramName] like N'%test%'))
         |--Clustered Index Seek(OBJECT:([db1].[dbo].[TestTable].[PK__TableVie__3214EC277B422279]), SEEK:([db1].[dbo].[TestTable].[ID]=[db1].[dbo].[TestTable].[ID]) LOOKUP ORDERED FORWARD)
  </code></pre>
  
  <p>Execution Plan (Set SHOWPLAN_ALL ON) other columns:</p>
  
  <pre><code>EstimateRows    EstimateIO  EstimateCPU AvgRowSize  TotalSubtreeCost
  28671.36    NULL    NULL    NULL    181.9502
  28671.36    0   0.1198463   3281    181.9502
  28671.36    73.67498    9.032298    2015    82.70728
  1   0.003125    0.0001581   1275    91.89737
  </code></pre>
  
  <p>The DB is in development, so there are no other users/queries running. In the Azure Portal Dashboard I see that the DTU peak today (when I was testing) was 68.01%, so DTU capacity doesn't seem to be the issue. Region: East US</p>
  
  <p>I'm really stuck on this - any help is very much welcome! Is there something I can do to improve my query? Or should I consider another cloud provider (with MySQL)?</p>