sql-server - SQL Azure 查询性能 - 即使经过调整的查询也非常慢

Question

这是一个依赖两个非聚集索引的基本查询:

SELECT cc.categoryid, count(*) from company c
INNER JOIN companycategory cc on cc.companyid = c.id
WHERE c.placeid like 'ca_%'
GROUP BY cc.categoryid order by count(*) desc

当完全相同的数据库托管在 SQL Server 2008 上时，几乎在任何硬件上，返回的时间均小于 500 毫秒。即使缓存缓冲区被清除:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

...在传统 SQL 上，这仍然会在大约 1 秒内返回。

在 Azure 上，每次返回大约需要 3.5 秒。

一些articles那里的情况似乎表明人们普遍对 SQL Azure 中的查询性能感到满意。然而，这是一个基本场景，其中“明显”的调整已经用尽，并且没有网络延迟问题可言。只是在处理大型表时真的很慢(companycategroy 有 120 万条记录，places 有 7500 条记录)。

数据库总大小不超过4GB。选择“Web”版本与“企业”版本似乎也没有太大区别。

我错过了什么？

这只是一个基本示例，随着更复杂的查询，情况只会变得更糟，所有这些都是 reviewed 、经过调整并在本地表现良好。

执行计划如下:

  |--Sort(ORDER BY:([Expr1004] DESC))
       |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
            |--Hash Match(Aggregate, HASH:([cc].[CategoryId]), RESIDUAL:([XX].[dbo].[CompanyCategory].[CategoryId] as [cc].[CategoryId] = [XX].[dbo].[CompanyCategory].[CategoryId] as [cc].[CategoryId]) DEFINE:([Expr1007]=COUNT(*)))
                 |--Hash Match(Inner Join, HASH:([c].[Id])=([cc].[CompanyId]))
                      |--Index Scan(OBJECT:([XX].[dbo].[Company].[IX_Company_PlaceId] AS [c]),  WHERE:([XX].[dbo].[Company].[PlaceId] as [c].[PlaceId] like N'ca_%'))
                      |--Index Scan(OBJECT:([XX].[dbo].[CompanyCategory].[IX_CompanyCategory_CompanyId] AS [cc]))

以下是统计数据:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 14 ms, elapsed time = 14 ms.

(789 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CompanyCategory'. Scan count 1, logical reads 5183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Company'. Scan count 1, logical reads 8710, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 3328 ms,  elapsed time = 3299 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

索引定义如下:

CREATE NONCLUSTERED INDEX [IX_Company_PlaceId] ON [dbo].[Company] 
(
    [PlaceId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

CREATE NONCLUSTERED INDEX [IX_CompanyCategory_CompanyId] ON [dbo].[CompanyCategory] 
(
    [CompanyId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

ALTER TABLE [dbo].[Company] ADD  CONSTRAINT [PK_Company_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

Answer 1

他们似乎为您的查询使用一个 CPU 核心，而在您的计算机上查询可能会并行化(查询使用的所有操作都会并行化)。

但是，由于某种原因，索引扫描用于 LIKE 谓词，而索引查找就足够了。

请尝试使用此显式条件而不是 LIKE:

c.placeid >= 'ca'
AND c.placeid < 'cb'

并查看它是否将计划更改为 IX_CompanyPlaceId 上的Index Seek。