SQL + asp.高效分页

Question

我的分页方法效率很低，因为它将同一个查询调用两次，因此查询时间增加了一倍。我目前调用1个查询，将大约5个表与XML搜索查询连接在一起，以允许从ASP.net传递List。然后我需要调用完全相同的查询，除了一个计数(行)，以获得记录的数量

例如(我已经删除了位，使其更容易阅读)

主要查询:

WITH Entries AS (
  select row_number() over (order by DateReady desc)
  as rownumber, Columns...,
 from  quote
 join geolookup as Pickup on pickup.geoid = quote.pickupAddress
 where 
     quote.Active=1
     and //More 
 )
 select * from entries 
 where Rownumber between (@pageindex - 1) * @pagesize + 1 and @pageIndex * @pageSize
 end

统计查询:

 select count(rowID)        
 from  quote
 join geolookup as Pickup on pickup.geoid = quote.pickupAddress
 where 
     quote.Active=1
     and //More 
 )

Answer 1

您可以将大查询的结果选择到一个临时表中，然后您可以查询该表的行号并取出您需要的行。

为此，在select语句之后和from语句之前添加

INTO #tmpTable

然后引用你的表为#tmpTable

---

select row_number() over (order by DateReady desc)  
  as rownumber, Columns...,  
into #tmpTable
 from  quote  
 join geolookup as Pickup on pickup.geoid = quote.pickupAddress  
 where   
     quote.Active=1  
     and //More   
 )  
 SELECT @Count = COUNT(*) FROM #tmpTable
 select * from #tmpTable  
 where Rownumber between (@pageindex - 1) * @pagesize + 1 and @pageIndex * @pageSize  

Answer 2

您可以设置一个输出参数，该参数将保存第一次查询的行数。

你可以这样写

WITH Entries AS (
  select row_number() over (order by DateReady desc)
  as rownumber, Columns...,
 from  quote
 join geolookup as Pickup on pickup.geoid = quote.pickupAddress
 where 
     quote.Active=1
    and //More 
 )
select @rowcount = max(rownumber) from entries
 select * from entries 
 where Rownumber between (@pageindex - 1) * @pagesize + 1 and @pageIndex * @pageSize

希望能有所帮助