提取数据的有效方法 [英] efficient way to fetch data

问题描述

要求:

我有大约180000条记录，我想一次获取所需的20条记录，这将在数据网格中使用.

现在，我正在使用Row_Number()并为每行分配Row_Num并将它们存储在临时表中，这样我就可以基于Row_Num获得所需的行，但是第一次将Row_Num分配给180000需要很长时间.请如果有任何有效的方法可以建议我.

req:

i have about 180000 records i want to fetch desired 20 records at a time which i am going to use in the data grid.

right now i am using Row_Number() and assigning Row_Num for each row and storing them in a temp table than i can get the desired rows based on Row_Num, but it takes long time for the very first time to assign Row_Num to the 180000. Please suggest me if any efficient way to do this.

推荐答案

您不需要分配行号

You don''t need to assign a row number

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;
WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)
SELECT *
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;

http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx [ ^ ]

http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx[^]

这篇关于提取数据的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！