How to make pagination more efficient

[Sl1ver]

here is my sql

PROCEDURE [dbo].[spMobileSearch]  
(
    @pg            int, 
    @pgSize        int,
    @text        varchar(250)
)
AS 
BEGIN
Create table #temp 
( 
id int identity primary key,
intTitleId int, 
vcTitleName varchar(250), 
intYear int, 
vcCertificate varchar(250), 
vcImageName varchar(250),
vcTitleDescription varchar(4000), 
actor varchar(4000)
) 
 
 
SET NOCOUNT OFF 
 
Insert into #temp(intTitleId, vcTitleName, intYear, vcCertificate, vcImageName, vcTitleDescription, actor) 
 
select * 
from Titles  
 
 
declare @from int 
declare @to int 
set @to=@pg * @pgSize 
Set @from= @pg * @pgSize-@pgsize 
 
Select * from #temp where id>@from and id<=@to 
Select count(*) from #temp
drop table #temp 
SET NOCOUNT ON 
END
 

dont mind the select statment, just took it out to make it look less clunky

Problem: This pager does exactly what i want it to do but struggles when it comes to a big resultset. E.g everytime you click next in the asp page, it feels like it is searching the db again and this slows the pager down a lot.

[Sl1ver]

I have found a solution to this problem and speeds up the paging supper fast

PROCEDURE [dbo].[spMobileSearch]  
(
    @pg INT,
    @pgSize INT,
    @text varchar(250)
)
AS BEGIN
 
WITH Search AS
(select distinct ROW_NUMBER() OVER (Order by T.intTitleId) AS RowID, T.intTitleId, from tites.....)
 
SELECT rowid, intTitleId, vcTitleName, intYear, vcCertificate, vcImageName, vcTitleDescription, actor
FROM Search WHERE RowID BETWEEN 
((@pg - 1) * @pgSize) + 1 AND @pg * @pgSize
ORDER BY RowID
 
 

[Frinavale]

It is searching the DataBase again.
You could use an ASP.NET control like the DataView which has paging functionality. This way you make 1 call to the database to populate the DataView and then cache the DataView into ASP.NET cache or Session (or something) so that you don't need to make more calls the database.