从给定的条件中提取记录的有效方法

Question

我正在尝试实现一个逻辑，用户可以说，从给定的id#开始，给我n条记录。例如。

SELECT TOP (100) col1, col2, ... colN 
FROM Table1
WHERE ID > @id
ORDER BY ID

性能是这里最大的问题，特别是当您进入复杂联接的嵌套循环时。我看过Server 2012中的新OFFSET-FETCH特性，但它们要求您指定一个数字而不是WHERE子句。除非我事先数数字行，否则我不会知道偏移量。

在Server (2008 R2和更高版本)中是否有其他有效的方法来做到这一点？

更新:由EF

生成的完整SQL

-- Region Parameters
DECLARE @p__linq__0 BigInt = 60375518904121
DECLARE @p__linq__1 BigInt = 60375518904121
-- EndRegion
SELECT 
[Project3].[Id] AS [Id], 
[Project3].[C2] AS [C1], 
[Project3].[C1] AS [C2], 
[Project3].[C3] AS [C3] 
FROM ( SELECT 
    [Limit1].[Id] AS [Id], 
    [Limit1].[C1] AS [C1], 
    [Limit1].[C2] AS [C2],
    [Limit1].[TmId] AS [TmId], 
    CASE WHEN ([Extent4].[TmId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]
    FROM   (SELECT TOP (100) [Project2].[Id] AS [Id], [Project2].[TmId] AS [TmId], [Project2].[C1] AS [C1], [Project2].[C2] AS [C2]
        FROM ( SELECT 
            [Extent1].[Id] AS [Id], 
            [Extent2].[TmId] AS [TmId], 
            CASE WHEN ([Extent2].[TmId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
            1 AS [C2]
            FROM  [dbo].[TextMessages] AS [Extent1]
            LEFT OUTER JOIN [dbo].[Tms] AS [Extent2] ON [Extent1].[Id] = [Extent2].[TmId]
            WHERE ( EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[Tmr] AS [Extent3]
                WHERE ([Extent1].[Id] = [Extent3].[TmId]) AND ([Extent3].[IsDel] = 1) AND ([Extent3].[UserId] = @p__linq__0)
            )) OR ([Extent1].[CreatedBy_Id] = @p__linq__1)
        )  AS [Project2]
        ORDER BY [Project2].[Id] DESC ) AS [Limit1]
    LEFT OUTER JOIN [dbo].[Tmr] AS [Extent4] ON [Limit1].[Id] = [Extent4].[TmId]
)  AS [Project3]
ORDER BY [Project3].[Id] DESC, [Project3].[TmId] ASC, [Project3].[C3] ASC

更新:真正的瓶颈是由EC2服务器造成的网络延迟。

Answer 1

为了给@SQLFox说的话添加一点，如果ID恰好是聚集索引，那么它应该是快速运行的。即使它不是聚集索引，如果它甚至包括在一个索引中，那么你就是诽谤，至少得到一个索引扫描和相当快。

如果我不得不猜的话，我会说是你的命令让你慢下来了。随着订单到位，它将不得不检查每一行，并检查它是否应该是前100名的一部分。没有它，SQL只需要检查足够多的行，直到获得符合条件的100行为止。