DataGrid和存储过程结合的分页,只读取当前页数据
select top @pagesize from table where id not in ( select top @PageSize*@PageIndex from table order by id desc )order by id desc
在网上找了半天,还是这个写得比较好
(注:后来自己运行了下,发现所写的存储过程运行效率并不高)
下面是存储过程:
CREATE PROCEDURE OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
OrderID int
)
-- Insert into the temp table
INSERT INTO #PageIndex (OrderID)
SELECT
OrderID
FROM
Orders
ORDER BY
OrderID DESC
-- Return total count
--SELECT COUNT(OrderID) FROM Orders
-- Return paged results
SELECT
O.*
FROM
Orders O,
#PageIndex PageIndex
WHERE
O.OrderID = PageIndex.OrderID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
GO
因为建了领时表,当查询大量记录中最后部分的记录时,也比较消耗时间
又找到了一个更好的方法
select top @pagesize from table where id not in ( select top @PageSize*@PageIndex from table order by id desc )order by id desc
这个查询语句执行效率很高,不关查询的数据量有大,只影响了@pagesize 行。具体存储过程怎么写,还得各位自己去发挥。具体思路就是这些。