sql - 如何在SQL Server Compact Edition 4上解决此查询性能

Question

我有以下在SQL Server CE 4上运行的SQL查询

SELECT [Join_ReleaseMinDatePost].[FK_MovieID]
  FROM ( 
          SELECT [FK_MovieID], MIN([DatePost]) AS [ReleaseMinDatePost] 
          FROM [Release] 
          GROUP BY [FK_MovieID] 
        ) [Join_ReleaseMinDatePost]
  INNER JOIN  
        ( 
          SELECT COUNT([ID]) AS [FolderCount], [FK_MovieID] 
          FROM [MovieFolder] 
          GROUP BY [FK_MovieID] 
        )  [Join_MovieFolder]
    ON [Join_MovieFolder].[FK_MovieID] = [Join_ReleaseMinDatePost].[FK_MovieID]

此查询需要很长时间才能执行，但是如果我更改零件

SELECT COUNT([ID]) AS [FolderCount], [FK_MovieID] FROM [MovieFolder] GROUP BY [FK_MovieID]

至

SELECT 1 AS [FolderCount], [FK_MovieID] FROM [MovieFolder]

因此完整的查询变为

SELECT [Join_ReleaseMinDatePost].[FK_MovieID]
FROM ( SELECT [FK_MovieID], MIN([DatePost]) AS [ReleaseMinDatePost] FROM [Release] GROUP BY [FK_MovieID] ) [Join_ReleaseMinDatePost]
INNER  JOIN  (SELECT 1 AS [FolderCount], [FK_MovieID] FROM [MovieFolder] ) [Join_MovieFolder]
ON [Join_MovieFolder].[FK_MovieID] = [Join_ReleaseMinDatePost].[FK_MovieID]

然后性能变得非常快。
问题在于，如果单独使用，则更改的部分很快。但是由于某种原因，第一个查询的执行计划显示索引扫描中的“实际行数”为160,016，而表MovieFolder中的总行数为2,192。
并且“估计的行数”为2,192。
所以我认为问题出在行数上，但我无法弄清为什么它们都弄乱了。
任何帮助将不胜感激 ：）
谢谢

表的架构如下

CREATE TABLE [Release] (
  [ID] int NOT NULL
, [FD_ForumID] int NOT NULL
, [FK_MovieID] int NULL
, [DatePost] datetime NULL
);
GO
ALTER TABLE [Release] ADD CONSTRAINT [PK__Release__0000000000000052] PRIMARY KEY ([ID]);
GO
CREATE INDEX [IX_Release_DatePost] ON [Release] ([DatePost] ASC);
GO
CREATE INDEX [IX_Release_FD_ForumID] ON [Release] ([FD_ForumID] ASC);
GO
CREATE INDEX [IX_Release_FK_MovieID] ON [Release] ([FK_MovieID] ASC);
GO
CREATE UNIQUE INDEX [UQ__Release__0000000000000057] ON [Release] ([ID] ASC);
GO

CREATE TABLE [MovieFolder] (
  [ID] int NOT NULL  IDENTITY (1,1)
, [Path] nvarchar(500) NOT NULL
, [FK_MovieID] int NULL
, [Seen] bit NULL
);
GO
ALTER TABLE [MovieFolder] ADD CONSTRAINT [PK_MovieFolder] PRIMARY KEY ([ID]);
GO
CREATE INDEX [IX_MovieFolder_FK_MovieID] ON [MovieFolder] ([FK_MovieID] ASC);
GO
CREATE INDEX [IX_MovieFolder_Seen] ON [MovieFolder] ([Seen] ASC);
GO
CREATE UNIQUE INDEX [UQ__MovieFolder__0000000000000019] ON [MovieFolder] ([ID] ASC);
GO
CREATE UNIQUE INDEX [UQ__MovieFolder__0000000000000020] ON [MovieFolder] ([Path] ASC);
GO

Answer 1

我认为您遇到了correlated subquery问题。您正在试验的查询部分是JOIN条件的一部分，因此将对每个可能匹配的行进行完全评估。您正在使SQL引擎对FROM子句产生的每一行执行第二个“ GROUP BY”。因此，它将读取2192行以对FROM子句产生的每一行进行分组依据。

这表明您在FROM子句分组中获得了73行（2192 * 73 = 160016）

当您将其更改为SELECT 1时，将消除对表扫描的读取以进行分组。