更新:下面添加了 3 个更新
以下sql语句需要5分钟才能完成。我只是。不。得到。它 :(
第一个表有 6861534 行。第二个表少了一点..第三个表(包含 4 个地理字段)与第一个表相同。
Those GEOGRAPHY
第三个表中的字段..它们不应该与sql语句混淆...应该吗?难道是因为桌子太大了(由于GEOGRAPHY
字段)它有巨大的页面大小或其他东西..从而破坏了 COUNT 所做的表扫描?
SELECT COUNT(*)
FROM [dbo].[Locations] a
inner join [dbo].[MyUSALocations] b on a.LocationId = b.LocationId
inner join [dbo].[GeographyBoundaries] c on a.locationid = c.LocationId
Update
As requested, here's some more info about the GeographyBoundaries table...
/****** Object: Index [PK_GeographyBoundaries] Script Date: 11/16/2010 12:42:36 ******/
ALTER TABLE [dbo].[GeographyBoundaries] ADD CONSTRAINT [PK_GeographyBoundaries] PRIMARY KEY CLUSTERED
(
[LocationId] 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) ON [PRIMARY]
GO
更新 #2 - 添加非聚集索引后
添加非聚集索引后,现在已降至 4 秒!这太棒了。但why ?
什么泽弗拉克?
更新 3 - 更有趣和令人困惑的信息!
现在,当我只做一个连接并强制索引时..它会回到 5 分钟。我这样做是为了
- 确保 MyUSALocations 表没有用连接搞乱事情。
- 确保 PK 正在做奇怪的事情。
.
SELECT COUNT(*)
FROM [dbo].[Locations] a
INNER JOIN [dbo].[GeographyBoundaries] c
WITH (INDEX(PK_GeographyBoundaries)) ON a.locationid = c.LocationId