SQL + ASP.Net Efficient paging
My method of paging is inefficient as it calls the same query twice therefore doubling the query time. I currently call the 1 query that joins about 5 tables together with XML search querys to allow for passing List from ASP.net.. then I need to call exactly the same query except with a Count(row) to get the amount of records
For Example (I have removed bits to make it easier to read)
Main Query:
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
Count Query:
select count(rowID)
from quote
join geolookup as Pickup on pickup.geoid = quote.pickupA开发者_运维知识库ddress
where
quote.Active=1
and //More
)
You could select the results of your big query into a temp table, then you could query this table for the row number and pull out the rows you need.
To do this, add (after your select statement and before the from)
INTO #tmpTable
Then reference your table as #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
You can set an output parameter which will hold the number of rows from the first query.
You could do something like
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
Hope this helps
精彩评论