Azure SQL slow performance

Question

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.

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.

Table schema:

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

Non-clustered indexes on PartNumber, Name, ProgramName. ProgramNumber. Clustered index on ID.

Query:

SELECT [PartNumber]
      ,[Name]
      ,[ProgramName]
      ,[URL]
      ,[ProgramNumber]
      ,[Date]
  FROM [dbo].[TestTable]
  where ProgramName like '%test%'

Execution Plan (Set SHOWPLAN_ALL ON) first column:

[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)

Execution Plan (Set SHOWPLAN_ALL ON) other columns:

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

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

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)?

The SQL Azure Product Team has been alerted and someone will be responding soon — BrianAtkins

Jan Engelsberg Jan Engelsberg · Accepted Answer · 2015-04-21T21:56:54

The query is expensive to execute due to the LIKE operator you are using in the where clause. Basically the DB must look at all entries in the table to figure out which are part of the result set. If this is a typical query for your application, you will probably have to look into upgrading to a higher performance level.

If you can predict when the queries run, you can upgrade to a higher performance level for these specific points in time and then downgrade the DB afterwards. This way you can make use of the hourly billing that SQL database has.

Azure SQL slow performance

2 Answers